View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Perico[_2_] Perico[_2_] is offline
external usenet poster
 
Posts: 57
Default 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