0

I currently am using a VBA macro that I found here on stack over flow. The problem is when I run it it saves all the data into a separate excel sheet but when i open it it appears as "jargon" in other words unreadable type. This is the "Save code"

'Save the new workbook, and close it
wb.SaveAs ThisWorkbook.Path & "\test" & WorkbookCounter
wb.Close

The way I am currently running the code is that it separates my excel sheets into different spread sheets by rows of 250. Everything works but when I open the saved documents it says that this file format is unacceptable to Excel. Then I try importing it and I get an error. Here is a snap shot of the way it appears in my screen. Also here is the file name: built.list\test.xls39

Fabricio G
  • 13
  • 5

1 Answers1

2

Your workbook counter always ends in a number, Windows and Excel use the file extension to determine file-type, so an '.xls39' file is unrecognisable. Try:

    wb.SaveAs _
    Filename:=ThisWorkbook.Path & "\test" & WorkbookCounter & ".xls" _
    FileFormat:=XlFileFormat.xlExcel8

'Use xlOpenXMLWorkbook for the .xlsx format

(Use space followed by underscore to separate lines in VBA)

Or make sure WorkbookCounter ends in .xls and not a number.

(Edit: For other formats, please look in the References dialog in Excel VBA Editor)

mjsqu
  • 5,151
  • 1
  • 17
  • 21
  • 1
    I actually would go a step further and specify file format instead of an extension like `wb.SaveAs Filename:="your file name", FileFormat:=XlFileFormat.xlOpenXMLWorkbook`. Excel will add the extension that is correct for the file format. There is no harm in adding your own extension to the name, but if it is not the right one then Excel will add the correct one anyway. If `FileFormat` is not specified then you do need it. – Cor_Blimey Apr 30 '14 at 18:57
  • Thanks @Cor_Blimey, updated - although, the updated code may add ".xlsx", I don't have Excel so I can't test! – mjsqu Apr 30 '14 at 18:59
  • 1
    ah - i see your edited comment. Yeah, the enum is for the more modern .xlsx :) - the enum for the archaic .xls is xlExcel8 (I think) - I must have put the xlOpenXMLWorkbook as a gentle nudge to the OP to use .xlsx :D – Cor_Blimey Apr 30 '14 at 19:01
  • ok this is like a http://en.wikipedia.org/wiki/Race_condition of comment editing :D - I think the xlExcel12 should be xlExcel8 (per http://stackoverflow.com/questions/12159513/what-is-the-correct-xlfileformat-enumeration-for-excel-97-2003). Apologies for any havoc I wrought. – Cor_Blimey Apr 30 '14 at 19:05
  • 1
    That's put a stop to it...hopefully – mjsqu Apr 30 '14 at 19:08