Search code examples
csvvbscriptexcel-2007export-to-excelfile-format

Unable to transfer large .csv row count to Excel 2007


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:

  1. Brand new machine
  2. Running Windows 7 x64 bit OS
  3. There is only one version of Office installed: Office 2007

Solution

  • 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.