无法将大的.csv行计数传输到Excel 2007 [英] Unable to transfer large .csv row count to Excel 2007

问题描述

我一直在使用下面的VBScript代码片段将小时.csv文件移动到Excel文件。它的工作原理像发条，但最近.csv越过了65536行限制。该机器充满了兼容性检查器的弹出窗口，然后我意识到，它使用Excel 2003甚至通过Excel 2007安装。不清楚为什么即使在 Excel.Application.12 对象被显式调用时也会发生这种情况：

  Dim oExcel 
设置oExcel = CreateObject（Excel.Application.12）
使用oExcel 
 .DefaultSaveFormat = 51 
 .Workbooks.Open sourceFile 
 .Sheets（1）。 Columns（A）。TextToColumns .Range（A1），xlDelimited，，，True 
 .ActiveWorkbook.SaveAs outputFile，xlNormal 
 .Quit 
 End With 
  

如何使此脚本提取大于65536的行计数导入到Excel 2007中没有兼容性检查器提示，保留所有源行？

一些其他信息：

1. 全新机器


2. 运行Windows 7 x64位操作系统


3. 只有一个版本的Office安装：Office 2007

解决方案

尝试按 fileformat：= 51 .nl / saveas.htmrel =nofollow> Ron de Bruin 。

以下摘录自OP对问题的编辑：

以下更改工作！：

  
设置oExcel = CreateObject（Excel.Application.12）
使用oExcel 
 .DefaultSaveFormat = 51 
 .Workbooks.Open sourceFile 
 .Sheets（1）。 Columns（A）。TextToColumns .Range（A1），xlDelimited，，，True 
 .ActiveWorkbook.SaveAs outputFile，51 
。Quit 
 End With 
  
 
 
 
此外，请确保文件扩展名为.xlsx。
 
 

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

解决方案

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.

这篇关于无法将大的.csv行计数传输到Excel 2007的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！