ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   CSV file has ,,,,,below last data row (https://www.excelbanter.com/excel-programming/381544-csv-file-has-below-last-data-row.html)

Perico[_2_]

CSV file has ,,,,,below last data row
 
I generate a csv file from Access , creating an Excel object. The csv is for
upload into an Oracle database. Using Notepad, I see rows and rows of
,,,,,,,,,,,,,,,, after the last data row. I thought adding a row count
parameter to CopyFromRecordset would help...

CopyFromRecordset rsCSVmst, rowCount, iCount

.... would cure the problem. It didn't. I then tweaked the line:

xlWbMst.SaveAs Filename:=fNameMst, FileFormat:=6

trying constants 20, 23. Still, didn't work. How do I eliminate those
empty rows of commas in my resulting CSV file?

The query from which the dao object is built has no blank rows.
Here's the code:
Dim xlWbMst As Object
Dim IStartedXL As Boolean
Dim i As Integer, iCount As Integer, rowCount As Long

IStartedXL = False
'PUT IF OBJECT NOT EXIST CREATE IT HERE?
On Error Resume Next
Set xlAppMst = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppMst Is Nothing Then
Set xlAppMst = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbMst = xlAppMst.Workbooks.Add

Dim rsCSVmst As DAO.Recordset
Set rsCSVmst = db.OpenRecordset("qtmpMasterCSV")
iCount = rsCSVmst.Fields.Count
rowCount = rsCSVmst.RecordCount

xlAppMst.Range("A1").Select
For i = 0 To iCount - 1
xlAppMst.Cells(1, i + 1).Value = rsCSVmst.Fields(i).Name
Next i
i = 0

xlAppMst.Range("A2").CopyFromRecordset rsCSVmst, rowCount, iCount
xlWbMst.SaveAs Filename:=fNameMst, FileFormat:=6


All times are GMT +1. The time now is 12:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com