Automate Excel export to a CSV file

[Chris B]

Hi...

I'm wondering if it's possible to export an excel file to a csv file via
script. I have data in excel that I need to import into a SQL Server
database, but importing the excel spreadsheet results in missing data or
values that are assigned to null when they have a value in the spreadsheet.
If I convert the excel spreadsheet to a csv and import the csv, it works fine.

I know the users of the spreadsheet can export/save as a csv file, I'd
rather automate this process rather than relying on them to actually do this
themselves. Is this at all possible?

 

[AndyC812]

Yes you can. I have data in two columns, A has field names and B has values
and use this code which is a modified version of something I got off this
forum:


' Write data to CSV file

Set fswrite = CreateObject("Scripting.FileSystemObject")
'Set path names
CSVPathName = FPath + WriteFileName + ".csv"
DocPathName = FPath + WriteFileName + ".doc"

' Open and export data to CSV File
fswrite.CreateTextFile CSVPathName
Set fwrite = fswrite.GetFile(CSVPathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = z + 1
LastCol = 2
With Sheets("Data Table2")
For ColCount = 1 To LastCol
OutputLine = ""
For RowCount = 2 To LastRow
If OutputLine = "" Then
OutputLine = Cells(RowCount, ColCount).Value
'MsgBox "OutputLine = " & OutputLine
Else
'If ColCount = 2 Then MsgBox RowCount & ": " & Cells(RowCount,
ColCount).Value
OutputLine = OutputLine & Delimiter & Cells(RowCount,
ColCount).Value
End If
Next RowCount
tswrite.writeline OutputLine
Next ColCount
End With
tswrite.Close
MsgBox "CSV has been written!"