Error 13 Type Mismatch

[pjg34711]

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]

Your code should be like this I believe:

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]

Thanks, Bob. It works.

Paul

 

[pbaldy]

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]

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]

Oh well

 

[pjg34711]

Bob and Paul,

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

Paul G.