I have this code to export to excel to create a backup for data.

Private Sub Command32_Click()
Dim strFile As String

strFile = "P:\Logistics Development\Quality Control\Backups\" & Format (date (),"yyyymmdd") & " Test History.xls"

stDocName = "900_Tbl_Test_Report_History"
DoCmd.TransferSpreadsheet [acExport], , stDocName, _
strFile, 0

End Sub

Only thing is when I open spreadsheet in XL after I get an unrecognizable format and it is just gobbledygook.
Is there a way to fix this?

Also can I specify a particular sheet to in the same way with a todays date as the sheet name?

thanks

Are you exporting to the correct Excel type file? I believe the default is spreadsheet type 8. You have omitted that argument, so it will be using that type. That's the only thing I can think of...but it was just an initial thought.

No idea why it's not displaying your data properly.

Anyway if you want to rename worksheets etc. then you are much better of using the CopyFromRecordset method. Something like this:


Sub bleh()

dim xlapp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

dim i as long
dim strSQL as string

dim rs as recordset


set xlapp = new excel.application
set wb = xlapp.workbooks.add
set ws = wb.worksheets("Sheet1")

strFile = "P:\Logistics Development\Quality Control\Backups\" & Format (date (),"yyyymmdd") & " Test History.xls"


set rs = currentdb.openrecordset("900_Tbl_Test_Report_History")

ws.range("A1").copyfromrecordset rs

ws.name = Format (date (),"yyyymmdd")

wb.saveas strfile

set rs = nothing
set ws = nothing
set wb = nothing
set xlapp = nothing

end sub