Link to home
Create AccountLog in
Avatar of DanielT
DanielTFlag for Canada

asked on

Export to XLS using VBA

Hey,

Have a strange problem that I thought I could resolve easily.

I need to use the code...
DoCmd.OutputTo acOutputQuery, "qryName", acFormatXLS, , True

to push the results of a Query out to an Excel file. It works OK but chooses an old version of Excel when using the acFormatXLS constant. I checked into the  constants and did not see another that would represent 97-2003.  Easy, right?

Checked the acFormatXLS constant actually expecting to see a numeric result but it was a text result instead returning "Microsoft Excel (*.xls)". Without the parameter, a prompt is shown allowing the selection of "Microsoft Excel 97-2003 (*.xls)" as a format so I created a public constant as acFormatXLS2003 to hold value "Microsoft Excel 97-2003 (*.xls)" and used the 'new' parameter...

DoCmd.OutputTo acOutputQuery, "qryName", acFormatXLS2003 , , True

Problem is - it still outputs as a Excel 5-7 file!

Ideas or experience with this? I would like to stick with this output method as it retains formatting an offers a prompt (without extra work) to save the file. If this is done in a Macro, the option is available for 97-2003 format and it works.
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

If you convert the macro to vba then you get the file type..
"MicrosoftExcelBiff8(*.xls)"
Avatar of DanielT

ASKER

Who would have guessed that!! Didn't look for converting. How do I convert the Macro to VBA? (Not at PC)
Select the macro in the database window and then Tools>Macro>Convert macro to Visual Basic
Avatar of DanielT

ASKER

This made enough sense that I almost closed this before testing. I assigned the new string value to a variable and it did not work! Still presented Excel 5-7 version! So, instead, I used the string value directly but it still remained as Excel 5-7!!??

This is in Access 2003 BTW - but do you get the same "bad" results?
DoCmd.OutputTo acOutputQuery, "qryName", "MicrosoftExcelBiff8(*.xls)", , True
Avatar of DanielT

ASKER

ps; Running the converted Macro also fails to use the correct format.
That appears to be the case.
OutputTo appears to ignore the file format.
I don't see a code solution using OutputTo then.
Using outputto in a macro or transferspreadsheet in code would appear to be the only simple options.
Avatar of DanielT

ASKER

Hmmmm. Could be the case.

I'm wondering if the macro conversion is just flawed but that may be hard to nail down. If the filetype is left blank it does allow selection of the type and works that way so this is also an alternative if the user knows what to choose. May try to look further.

Seems like it must just be the parameter name. May try to find out more - or see if anyone else picks up on this that may have been able to resolve. :)
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of DanielT

ASKER

Ha! Waddayaknow!

8 works. So does "8".
I assigned "8" to my original variable (as below) and the following now works great!

DoCmd.OutputTo acOutputQuery, "qryName", acFormatXLS2003 , , True

Thanks.
Avatar of DanielT

ASKER

Great help - thanks for sticking with it.