I've been using the below VBScript code snippet to move an hourly .csv drop to an Excel file. It works like clockwork except recently the .csv crossed the 65536 row limitation. The machine is flooded with popups from Compatibility Checker and then I realized it was using Excel 2003 even through Excel 2007 is installed. Not sure why this happens even when Excel.Application.12 object is explicitly invoked:
Dim oExcel
Set oExcel = CreateObject("Excel.Application.12")
With oExcel
.DefaultSaveFormat=51
.Workbooks.Open sourceFile
.Sheets(1).Columns("A").TextToColumns .Range("A1"), xlDelimited, , , , True
.ActiveWorkbook.SaveAs outputFile, xlNormal
.Quit
End With
How do I make this script pull a row count greater than 65536 to be imported into Excel 2007 without the Compatibility Checker prompt, while preserving all source rows?
Some additional information:
Try adding fileformat:=51
as advised by Ron de Bruin.
Below extracted from an edit by OP to the Question:
Below change worked!:
Dim oExcel Set oExcel = CreateObject("Excel.Application.12") With oExcel .DefaultSaveFormat=51 .Workbooks.Open sourceFile .Sheets(1).Columns("A").TextToColumns .Range("A1"), xlDelimited, , , , True .ActiveWorkbook.SaveAs outputFile, 51 .Quit End With
Also, make sure the file extension is .xlsx.