Exported excel file save as csv question

[reillj]

Hello,
I am exporting individual xls files out of my access database, and during the process i would like to also save a copy as a csv file.

every time i run the code shown below it incorrectly exports the csv.

any help would be appreciated, thanks.

Private Sub Export_Report_Test_Click()
Dim sSourceFile As String
Dim sSourceQuery As String
Dim sSourceVal As String

sSourceFile = "H:\25982.00\Transmission\Conversion_Data\NY\Working\Exported_Files\"
STempFile = "H:\25982.00\Transmission\Conversion_Data\NY\Working\"
sSourceQuery = "Export_Data-qry"
sSourceVal = Forms!Export_Data_frm!Circuit_combo

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sSourceQuery, STempFile & "Temp.xls"
Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.workbooks.Open STempFile & "Circuit_Loader_Data_file.xls"
xlObj.activeworkbook.SaveAs sSourceFile & Me.Circuit_combo.Value & ".xls"
xlObj.workbooks.Open STempFile & "Temp.xls"
xlObj.activesheet.range("A2:Q1000").select
xlObj.selection.copy
xlObj.workbooks(Me.Circuit_combo.Value & ".xls").Activate
xlObj.activeworkbook.sheets(1).range("A3").select
xlObj.activesheet.paste
xlObj.activeworkbook.Save
xlObj.activeworkbook.SaveAs sSourceFile & Me.Circuit_combo.Value & ".csv", FileFormat = xlCSV
xlObj.activeworkbook.Close
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing
MsgBox "Your Data is exported!"
End Sub

the code that is not working is bolded.

 

[DJkarl]

If Access is the source of your data as indicated by the line

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sSourceQuery, STempFile & "Temp.xls"

Why not use the DoCmd.TransferText method to export a CSV file directly rather than trying to use Excel automation?

 

[boblarson]

If Access is the source of your data as indicated by the line

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sSourceQuery, STempFile & "Temp.xls"

Why not use the DoCmd.TransferText method to export a CSV file directly rather than trying to use Excel automation?

Amen to that. That would be the better way.

 

[reillj]

Thanks for replying; I am exporting the data to a temp file and copying it to a template file via the excel automation. The header on the template is kind of weird, and unfortunately it has to be in there.

I thought the easier thing to do was to get the exported data into the correct format in excel and then do a save as on the completed file.

the export and copy to the template file works great; i just need to get that completed file into a csv format.

 

[boblarson]

So what is it doing that isn't working? (just saying it isn't working or that it incorrectly creates the csv isn't all that helpful. Be more specific, if you would please)

 

[DJkarl]

If the header on your template truly is "weird" that could be an issue. CSV files are plain text, if you have graphics or some funky formatting it won't be saved and Excel may be giving an error letting you know that this isn't going to work.

Can you create the temp document and manually do a SaveAs CSV?

 

[reillj]

Sorry for the lack of specifics, didn't want to bore anyone

The export to the xls file works fine; I am making a copy of the template, saving it as the correct name, and copying the data from the temp file into the new xls file. the CSV says that it is working, but when I try to open it as a text file it is a binary file. it looks like it is saving as an xls file with a different extention.

I think the problem is that when the saveas happens, it is not saving it in csv format, but in xls format with a csv extention.

 

[boblarson]

Do you have a reference set to Excel? I don't see where you are declaring your excel application object but that you are instantiating it using CreateObject. That is fine but if you don't have a reference set, you will need to pass the value of xlCSV and not xlCSV itself. If you use late binding you have to either provide the value (I usually create a constant for it but you can refer to it in code but I can't remember the exact syntax).

So, if you don't have an Excel Reference set and you aren't using Option Explicit at the top of your modules, you could be trying to pass a 0 as the value when you would want to pass a 6 (which is the value of xlCSV).

 

[reillj]

Thanks Bob.

So should I explicitly set the value to 6?

xlObj.activeworkbook.SaveAs sSourceFile & Me.Circuit_combo.Value & ".csv", FileFormat = 6

or do i need to declare this earlier?

 

[boblarson]

Does changing it to 6 actually work?

 

[reillj]

No, just tried it; "SaveAs method of workbook class failed"

 

[reillj]

Figured it out. real easy.

xlObj.activeworkbook.SaveAs sSourceFile & Me.Circuit_combo.Value & ".csv", FileFormat:=6

just added a ':' and it works.

Thanks for all the help; i never would have used the 6 if not for you guys.