Error 13 Type Mismatch (1 Viewer)

pjg34711 · Sep 20, 2012

Hi,

I'm still on my quest to enter a file name for a file I will later output to my computer. This is my current test code (whithout the inputbox segment) -

Function CopyQuearytoSdriveFolder()
Dim MyValue As String
MyValue = "mm_dd_yyyyQTMsQuery.xls"
DoCmd.OutputTo "acOutputQuery", "Qualified TMs", "Microsoft Excel 97-2003 (*.xls)", "S:\Compensation\Urban Tax Credit\Access DB Data\From Access\XXX.xls", True
End Function

When I step through this though I get the Runtime Error '13': Type mismatch after running the DoCmd step.

I've made sure that the data in my query are all formatted as text, so I'm not sure where the mismatch is occurring. any help would be appreciated! thanks,

Paul G

boblarson · Sep 20, 2012

Your code should be like this I believe:

Code:

Function CopyQuearytoSdriveFolder()
Dim MyValue As String
MyValue = "mm_dd_yyyyQTMsQuery.xls"
DoCmd.OutputTo [B][COLOR=red]acOutputQuery[/COLOR][/B], "Qualified TMs", [B][COLOR=red]acFormatXLS[/COLOR][/B], "S:\Compensation\Urban Tax Credit\Access DB Data\From Access\[B][COLOR=red]" & MyValue[/COLOR][/B], True
End Function

The constants don't get put into quotes and you didn't have acFormatXLS in there.

pjg34711 · Sep 20, 2012

Thanks, Bob. It works.

Paul

pbaldy · Sep 20, 2012

As an FYI, the format may have been okay like it was. I was helping someone recently who was trying to use acFormatPDF in a 2003 db that was to be run in 2007, and it didn't work (prompted for file type). The solution was to put "PDF Format (*.pdf)" there instead of the constant. Presumably when 2003 created an mde, it left the string alone, but dropped the constant that was unknown to it. A trick in the Immediate window:

?acFormatXLS
Microsoft Excel (*.xls)
?acFormatPDF
PDF Format (*.pdf)

Or if you look up the constants in help, all of them are numeric except the format constants, which are text. That surprised me for some reason.

pbaldy · Sep 21, 2012

boblarson said:
This:
Microsoft Excel (*.xls)

is quite different from

Microsoft Excel 97-2003 (*.xls)

So yes, Microsoft Excel (*.xls) could be used successfully, but the one originally posted - no.

That's something you should have tested before stating so emphatically. I tested; this worked fine in a client's 2007 db:

DoCmd.OutputTo acOutputTable, "tbl_journeys", "Microsoft Excel 97-2003 (*.xls)", "C:\BlueCrew\XXX.xls", True

boblarson · Sep 21, 2012

Oh well

pjg34711 · Sep 21, 2012

Bob and Paul,

Thanks for your guidance. I think I've solved the problem.

Paul G.

Error 13 Type Mismatch (1 Viewer)

pjg34711

Registered User.

boblarson

Smeghead

pjg34711

Registered User.

pbaldy

Wino Moderator

pbaldy

Wino Moderator

boblarson

Smeghead

pjg34711

Registered User.

Similar threads

Users who are viewing this thread