This code has worked for over a year. But today it's changed its mind:
Code:
Private Sub Command18_Click()
Dim dbs As DAO.Database
Dim rstSAGE As DAO.Recordset
Dim myQueryName As String '<<--missing declaration
Dim myExportFileName As String '<<--missing declaration
DoCmd.SetWarnings False '<<--Not needed
Set dbs = CurrentDb
Set rstSAGE = dbs.OpenRecordset("sageimport4")
myQueryName = "sageimport4"
' Either of these lines create the Excel file in my Documents directory
'
' myExportFileName = "SERVER LOCATION " & "PO.xlsx"
myExportFileName = "\\-len-3\accounts\2010\Company.000\Import Maps\Audit Trail transactions\ " & Trim(Format(Date, "yyyy_mm_dd")) & "_PO.xlsx" '<<-- I added a "_" before the PO for readability
If Not rstSAGE.EOF Then
' DoCmd.TransferSpreadsheet acExport, 10, myQueryName, myExportFileName, True '<-- don't like using 10.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, myQueryName, myExportFileName, True '<< I prefer acSpreadsheetTypeExcel12Xml.
MsgBox "Your Sage report has been created."
Else
MsgBox "There are no records to import to sage."
End If
DoCmd.OpenQuery "sage import update"
DoCmd.SetWarnings True '<<--Not needed
'Clean up
rstSAGE.Close
Set rstSAGE = Nothing
Set dbs = Nothing
End Sub
This is the highlighted line in debug:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, myQueryName, myExportFileName, True
Ill do some reading and see what I can do.. (maybe recover an older version),
Does anyone have any ideas why this would happen or how I can fix this?
Thanks, Andy.