UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error "external Table Is Not In The Expected Format", Access 2016    
 
   
LagoDavid
post Aug 7 2018, 08:58 PM
Post#1



Posts: 433
Joined: 12-October 03
From: Texas


I am building a query and attempting to export it to Excel in the event code behind a command button. The query gets properly created, but I get an error when attempting to export it to Excel. I get an error that says "External table is not in the expected format".

My code is:
CODE
'create the new xTab query based on the above SQL; first close and delete the query if it already exists
         On Error Resume Next
         DoCmd.Close acQuery, "qrytmptblReportSampleResultsXTab"
         DoCmd.DeleteObject acQuery, "qrytmptblReportSampleResultsXTab"
         On Error GoTo ErrHandler
         Set qdf = dbs.CreateQueryDef("qrytmptblReportSampleResultsXTab", strSQL)

'output the cross tab query to Excel and send to desktop
        
         Debug.Print "now about to export the query to Excel"
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
         "qrytmptblReportSampleResultsXTab", strFilePath & strOutputFileName
         MsgBox "Excel file has been exported to your Desktop as " & strOutputFileName
        
         strSQL = ""
         Set qdf = Nothing
Go to the top of the page
 
ranman256
post Aug 8 2018, 03:07 AM
Post#2



Posts: 874
Joined: 25-April 14



the TRANSFERSPREADSHEET command export type: XML,
and the file name: strOutputFileName. (Not determined here)
MAY have conflicting types so you get the message.....

If you use the new type: Version12. (For .xlsx)
And you name it with the old extension: .xls
If will cause the conflict message. Tho you can still open it.
XL wants the 2 parameters to match.

Try using a different TYPE param, like the old version9 and .xls extension.
(Unless you need >65000 rows then use new format params)


....and why do you keep deleting the Querydef?
Why not just use it over and over, or just edit and save?
This post has been edited by ranman256: Aug 8 2018, 03:08 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 8 2018, 11:57 AM
Post#3


UA Admin
Posts: 33,259
Joined: 20-June 02
From: Newcastle, WA


Step one is to REMOVE the line: On Error Resume Next

On Error Resume Next hides errors and is generally not a first tier practice, shall we say.

You don't show the actual SQL, so it's impossible to know if it is well formed.

You have to other variables for which we don't see the actual values:

strFilePath

and

strOutputFileName

We'd need to know that those values are both valid and can be resolved correctly on your computer.

The export format constant "acSpreadsheetTypeExcel12Xml" should produce as an Microsoft Excel 2010/2013/2016 XML format document with one of these extensions: .xlsx, .xlsm, .xlsb
Can you confirm that that is exactly what your variable produces?

Thanks for providing sufficient detail to suggest an answer.


--------------------
Go to the top of the page
 
LagoDavid
post Aug 9 2018, 10:45 PM
Post#4



Posts: 433
Joined: 12-October 03
From: Texas


I am obviously not clear about what to do about clearing and not clearing the query defs.

I have an input form that gathers user input and builds the same query name in differing row and column arrangements depending upon user choices. So not being certain how to handle this, I am deleting the query def and starting over with each new user input.

GPG, I thought I needed to have the ON ERROR RESUME NEXT statement because if I attempted to close or delete the query and it was not present, I would create an error. And GPG, the file, when created, outputs with an .xlsx extension.

The error is not consistent, and now I have done some more investigation and found when the error occurs. The form collects user input and based on the input creates some queries. Depending upon which command button the user clicks, those queries are used to either create an Excel file for export, or they are used to create a report which is exported to a PDF file. As long as I have not previously created a PDF file, there is no error thrown when I create the Excel file. If I create a PDF file and then attempt to create the Excel file, the error is thrown. If I delete the previously saved PDF file before attempting to create the Excel file, there is no error. I am guessing that there is something in the code that exports to the PDF file that needs to be cleared each time so as not to conflict with creating an Excel file. But if that were the case, why would merely deleting the PDF file from my computer allow the Excel file to export without error.
CODE
'now open the report which is based on the above xTab query, and output to a PDF file
'First, check to see if the report is currently open and if so, close it and then re-open it to refresh the data
         strReportSelected = Me.lstReportTemplates.Column(1)
         Set accobj = Application.CurrentProject.AllReports.Item(strReportSelected)
         If accobj.IsLoaded Then
            If accobj.CurrentView = acCurViewPreview Then
               DoCmd.Close acReport, strReportSelected
               DoCmd.OpenReport strReportSelected, acPreview
            End If
         Else
            DoCmd.OpenReport strReportSelected, acPreview
               'see Sub for how File Name and File Path were created
            DoCmd.OutputTo acOutputReport, strReportSelected, acFormatPDF, strFilePath & strOutputFileName, True
            'last parameter means the file is opened after completion
            MsgBox "PDF File has been exported to your Desktop as " & strOutputFileName
            DoCmd.Close acReport, strReportSelected, acSaveNo
         End If

Go to the top of the page
 
GroverParkGeorge
post Aug 10 2018, 07:14 AM
Post#5


UA Admin
Posts: 33,259
Joined: 20-June 02
From: Newcastle, WA


Don't worry, creating a new querydef or replacing the SQL in a saved querydef both work. In my view, it's sort of a 50-50 choice as to which way to go.

On Error Resume Next is akin to using a sledge hammer to drive nails. You CAN do it, but it's not the safest thing to do. Use a valid error handler instead. On Error Resume Next HIDES problems, it doesn't solve them.

As I suggested before, you probably want to be sure what values are being returned in your variables each time you export a file, either pdf or xlsx: strFilePath and strOutputFileName

My guess is that they are not what you think they should be at any given point.

Also, you've only shown the part where you create a pdf, but it sounds like the problem is actually in the part where you try to export the xlsx file.

--------------------
Go to the top of the page
 
LagoDavid
post Aug 10 2018, 07:46 AM
Post#6



Posts: 433
Joined: 12-October 03
From: Texas


I believe I have figured out the problem, but so far not the solution.

My export to PDF VBA is: DoCmd.OutputTo acOutputReport, strReportSelected, acFormatPDF, strFilePath & strOutputFileName, True
My export to Excel is : DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrytmptblReportSampleResultsXTab", strFilePath & strOutputFileName

Both commands use the same output file name, which is what I want. However, the output to Excel creates an Excel file name that includes an extension. The output to PDF creates a file with NO EXTENSION. If I either delete the PDF file or if I go in and rename it with an extension, the error does not occur. So I need to make my export to PDF statement create a file name that includes the .PDF extension. I expected that the acFormatPDF qualifier would do that, but it does not.

Should I change my export to PDF statement to: DoCmd.OutputTo acOutputReport, strReportSelected, acFormatPDF, strFilePath & strOutputFileName & ".PDF", True

Go to the top of the page
 
LagoDavid
post Aug 10 2018, 07:52 AM
Post#7



Posts: 433
Joined: 12-October 03
From: Texas


not sure why I asked instead of just trying it.

I changed the PDF file output statement as above to include the .PDF extension and problem solved.

Thank you for your responses.
Go to the top of the page
 
GroverParkGeorge
post Aug 10 2018, 12:34 PM
Post#8


UA Admin
Posts: 33,259
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem yourself.

Continued success with the project.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th August 2018 - 12:47 PM