作者:xc15212 | 来源:互联网 | 2023-05-18 23:18
IhavealargemacroprogramrunthroughExcel2010that,afterformattinglargeamountsofdataint
I have a large macro program run through Excel 2010 that, after formatting large amounts of data into another table and exporting the workbook as a CSV file (by large amounts of data I mean thousands of rows, up to over 59,000 rows). Recently, my files have started ending up with an extra row of commas at the end like so:
我有一个运行在excel2010中的大型宏程序,在将大量数据格式化到另一个表并将工作簿导出为CSV文件之后(大量数据指的是数千行,最多超过59000行)。最近,我的文件以多一行逗号结尾:
data,data,data,data,number,date
data,data,data,data,number,date
,,,,,
I am exporting these files to an SQL database using a stored procedure, so ensuring that there are no extra commas to screw with the program is essential. So, with that said, what is happening and how can I prevent it? I can provide any code or information that you believe is missing.
我正在使用存储过程将这些文件导出到SQL数据库中,因此确保没有额外的逗号来连接程序是必要的。那么,说到这里,发生了什么,我该如何预防呢?我可以提供您认为丢失的任何代码或信息。
NOTE: It only appears to be happening on files with a couple thousand lines at least of data. One file exported often has 2,000+ and another must have 59,000+ for the table to be exported.
注意:它只出现在文件中,至少有几千行数据。导出的一个文件通常有2,000+,而要导出的表必须有59,000+。
EDIT1: Here's the macro I'm using, just in case it would be helpful (requested by Ditto)
EDIT1:这是我正在使用的宏,以防它会有帮助(Ditto的请求)
Sub exportTable()
Dim varIsOpen As Boolean
Dim varSaveLocation1 As String, varSaveLocation2 As String
varIsOpen = False
If ThisWorkbook.Sheets("ControlSheet").Range("D2").value = "" Then
varSaveLocation1 = ThisWorkbook.Path & "\CSVREVIEW\"
varSaveLocation2 = varSaveLocation1 & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
Else
varSaveLocation1 = ThisWorkbook.Sheets("ControlSheet").Range("D2").value
If Right(varSaveLocation1, 1) <> "\" Then varSaveLocation1 = varSaveLocation1 & "\"
varSaveLocation2 = varSaveLocation1 & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
End If
For counter = 1 To Workbooks.Count
If Workbooks(counter).Name = "TableBook.xls" Then varIsOpen = True
If varIsOpen = True Then Exit For
Next
If varIsOpen = False Then GoTo isClosed
Workbooks("TableBook").Activate
Application.DisplayAlerts = False
'Check if TableBook is empty and don't export if so
If Workbooks("TableBook").Sheets("logFile").Range("A1").value = "" Then
Workbooks("TableBook").Close
GoTo isClosed
End If
'On Error Resume Next
If Len(Dir(varSaveLocation1, vbDirectory)) = 0 Then
MkDir varSaveLocation1
End If
If Len(Dir(varSaveLocation2, vbDirectory)) = 0 Then
MkDir varSaveLocation2
End If
'On Error GoTo 0
ActiveWorkbook.Sheets("test").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\test", xlCSV
ActiveWorkbook.Sheets("part").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\part", xlCSV
ActiveWorkbook.Sheets("logFile").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\logFile", xlCSV
ActiveWorkbook.Sheets("deltaLimits").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\deltaLimits", xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
isClosed:
End Sub
1 个解决方案