Form Click Button Issue

[PD1978US1]

I have a database when I click on a button it transfers data from a table to an excel sheet which has two tabs.

The original database and button works.
Database is in the same folder as the excel file.

Now I created a new database and copied the same vba string command and now it is telling me it can't find the file.

here the string:
Private Sub Command95_Click()
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Performance_report_HW_1_DE", CurrentProject.Path & "\Performance report HW-DE.xls", False, A3
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Performance_report_HW_History_DE", CurrentProject.Path & "\Performance report HW-DE.xls", False, A3

Any Ideas?

 

[arnelgp]

You nee to create the export specification again.

 

[PD1978US1]

This is the complete script:

Private Sub Command95_Click()
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Performance_report_HW_1_DE", CurrentProject.Path & "\Performance report HW-DE.xls", False, A3
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Performance_report_HW_History_DE", CurrentProject.Path & "\Performance report HW-DE.xls", False, A3

'Change format in Excel cells
'Dim exApp As Excel.Application
' Dim exBook As Excel.Workbook
' Dim exSheet As Excel.Worksheet
' Dim BookName As String
'Set exApp = New Excel.Application
'BookName = CurrentProject.Path & "\Performance report HW-DE.xls"
'Set exBook = exApp.Workbooks.Open(BookName)
'Set exSheet = exBook.Worksheets(1)

'exSheet.Activate
'exSheet.Range("A3", "AO10000").Font.Size = 8
'exSheet.Range("I3", "J10000").NumberFormat = "dd/mm/yyyy"
'exSheet.Range("k3", "K10000").NumberFormat = "hh:mm"
'exSheet.Range("N3", "N10000").NumberFormat = "hh:mm"
'exSheet.Range("M3", "M10000").NumberFormat = "dd/mm/yyyy"
'exSheet.Range("V3", "W10000").NumberFormat = "dd/mm/yyyy"
'Set exSheet1 = exBook.Worksheets(2)

'exSheet.Activate
'exSheet1.Range("A3", "AO10000").Font.Size = 8
'exSheet1.Range("I3", "J10000").NumberFormat = "dd/mm/yyyy"
'exSheet1.Range("k3", "K10000").NumberFormat = "hh:mm"
'exSheet1.Range("N3", "N10000").NumberFormat = "hh:mm"
'exSheet1.Range("M3", "M10000").NumberFormat = "dd/mm/yyyy"
'exSheet1.Range("V3", "W10000").NumberFormat = "dd/mm/yyyy"

'exBook.Save
'exApp.Interactive = True
' exBook.Close
' Set exSheet = Nothing
' Set exBook = Nothing
' Set exApp = Nothing
MsgBox "File stored in " & CurrentProject.Path & "\Performance report HW-DE.xls", vbDefaultButton1
DoCmd.SetWarnings True
End Sub

Export specification as in how and what exactly?

 

[Ranman256]

verify :CurrentProject.Path is correct
and
if the file is open, it can produce an error.

I always erase the file before the transfer, because just overwriting can corrupt data.
If old file has 100 recs, and the new transfer has 50, the result will be 100 records,
50 old, and 50 new.
So erase the file first.

 

[arnelgp]

You manually export it first at the end access will ask if you want to save what you have done press yes and name your export spec Performance_report_hw_1_de.

 

[Minty]

Remove the range reference at the end of your statement (A3) . You can't specify a range for an export.

Bizarrely, I have had it work if the range is defined in the original spreadsheet, but isn't actually a valid switch for an export, and will cause the error you are getting.

Read the detail here https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-transferspreadsheet-method-access