file format or extension not valid.

[Homegrownandy]

Below is some code I'm working on. Feel free to criticise, its welcome.



Im getting an error message when I try to open the file it creates (It does create the file, the file looks fine untill opened.). The path is correct, I have changed the path in the code just because of it being on here.

I've tried:

*compact repair
*changing the spredsheet "type" to different values.
*removing variables from the file name created (you can see its commented out).

This code worked once, but not properly. It created two sheets inside the document. Both with the correct headings but only one with the data. Since this once I keep getting this error.

I have this working fine through the macros, but I have converted to VBA so I can do a check if there is any data to export.

Any Ideas what i can try?

Andy

Private Sub Command18_Click()
DoCmd.SetWarnings False
Dim dbs As DAO.Database
Dim rstSAGE As DAO.Recordset


Set dbs = CurrentDb
Set rstSAGE = dbs.OpenRecordset("sageimport3")

myQueryName = "sageimport3"
myExportFileName = "SERVER LOCATION " & "PO" & ".xlsx"  '& Trim(Format(Date, "yyyy_mm_dd")) & "PO" & ".xlsx"


If Not rstSAGE.EOF Then

 DoCmd.TransferSpreadsheet acExport, 10, myQueryName, myExportFileName, True
  MsgBox "Winner Winner Chicken Dinner. Your Sage report has been created."
   
Else

   MsgBox "There are no records to import to sage."
End If


DoCmd.SetWarnings True
End Sub

[aytee111]

MS has been messing with this since 2007 and never has got it right. Some chap on 2016 said that his worked with "acSpreadsheetTypeExcel12Xml" instead of the 10. See if that helps (if you haven't tried it already).

And guaranteed to work is Excel9 with xls!!

[Homegrownandy]

acSpreadsheetTypeExcel12Xml doesnt work for me. If i change xlsx to xls then I get a warning but it does work if the warning is ignored. I'll have a play with importing with this file and changing the values of file type. see if i can get this working.

Appreciate the help.

[aytee111]

You could also try OutputTo instead: DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLSX, "File.xlsx", True

[isladogs]

Just to confirm that acSpreadsheetTypeExcel12Xml IS correct for xlsx files. The short version is 10

So either of these should work

DoCmd.TransferSpreadsheet acExport, 10, myQueryName, myExportFileName, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, myQueryName, myExportFileName, True

The line I would check is:

myExportFileName = "SERVER LOCATION " & "PO" & ".xlsx"  '& Trim(Format(Date, "yyyy_mm_dd")) & "PO" & ".xlsx"

Try this:
Debug.Print "SERVER LOCATION " & "PO" & ".xlsx"
Does your server location have a trailing backslash?

[ssanfu]

This worked for me:

Option Compare Database
Option Explicit   '<<--should be at the top of EVERY module

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("sageimport3")

    myQueryName = "sageimport3"
    
    ' Either of these lines create the Excel file in my Documents directory
    '
    '    myExportFileName = "SERVER LOCATION " & "PO.xlsx"
    myExportFileName = "SERVER LOCATION " & 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 "Winner Winner, Chicken Dinner. Your Sage report has been created."
    Else
        MsgBox "There are no records to import to sage."
    End If

    '    DoCmd.SetWarnings True   '<<--Not needed

   'Clean up
    rstSAGE.Close
    Set rstSAGE = Nothing
    Set dbs = Nothing

End Sub

The rule is
"If you create it, destroy it."
"If you open it, close it"

You created a reference to "dbs' ("Set dbs ="), so you need to destroy it (Set dbs = Nothing)
You opened a recordset, "rstsage", so you need to close it (rstSAGE.Close)
You created a reference to "rstsage" ("Set rstSAGE ="), so you need to destroy it (Set rstSAGE = Nothing)


BTW, & "PO" & ".xlsx" can be written & "PO.xlsx" because you are concatenating two text strings

[Homegrownandy]

ajax: thanks, noted.
Ridders: File was being created in the right place. wasnt a path issue.

ssanfu: thanks for explaining ill take it on board. Code works perfect.