Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Excel export to a CSV file
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? -- Chris Burke |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Excel export to a CSV file
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!" -- Hope it helps, Andy "Chris B" wrote: 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? -- Chris Burke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to automate excel file sending by outlook from within the file | Excel Discussion (Misc queries) | |||
Automate export to a new workbook | Excel Discussion (Misc queries) | |||
Automate Import/Export | Excel Discussion (Misc queries) | |||
Automate Export from Form Template to Seperate Table | Excel Discussion (Misc queries) | |||
How do I automate upload of data from one excel file to another? | Excel Worksheet Functions |