Opening a Document in Excel Format in Access

I am using the following command to open a query in Excel:

DoCmd.OutputTo acOutputQuery, "qryTest", acFormatXLS, , True

The problem is that the code creates an Excel 97 file. Is there a way
using code to create an Excel 2000 or 2002 file?

Marcus
******

re: Opening a Document in Excel Format in Access

Marcus,
Maybe. It's a kludge, but you could run an instance of Excel in your code
that opened the saved result of outputting the query and saved it in the
format you want.
--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS"

"Marcus" <tobhamlet@yahoo.ca> wrote in message
news:1113871496.908585.215920@g14g2000cwa.googlegr oups.com...[color=blue]
>I am using the following command to open a query in Excel:
>
> DoCmd.OutputTo acOutputQuery, "qryTest", acFormatXLS, , True
>
> The problem is that the code creates an Excel 97 file. Is there a way
> using code to create an Excel 2000 or 2002 file?
>
> Marcus
> ******
>[/color]

re: Opening a Document in Excel Format in Access

Would you happen to have any samples of this, or know where I could
find samples on this?

Marcus
*******

re: Opening a Document in Excel Format in Access

Marcus,
It's a fairly straightforward bit of coding. I am pretty sure it's been
illustrated before here. But if you are new to this, record a macro in
Excel which opens a workbook and saves it in the format you want. Excel
will create a bunch of VBA for you that is the recorded macro. You can save
the module as a text file in the VB Editor view of Excel's macros. Then
open your Access project (database, whatever) and in a module start things
off with:

Private appExcel as new Excel.Application.

You will need a reference to the Office type library to make this work.
Click on Tools | References in the VB Editor to get the dialog box where
references are selected. Make sure something like the Office X type library
is selected.

Now you can take the VBA code generated by Excel and paste it into a
procedure in your Access module. There is a bit more work to clean up the
code so it uses your appExcel instance to run everything. But after that
the rest should be pretty easy.

--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS"

"Marcus" <tobhamlet@yahoo.ca> wrote in message
news:1113910183.385735.89310@o13g2000cwo.googlegro ups.com...[color=blue]
> Would you happen to have any samples of this, or know where I could
> find samples on this?
>
> Marcus
> *******
>[/color]

re: Opening a Document in Excel Format in Access

Marcus,
And . . . the reason I didn't send sample code is I draw the line at usable
code that doesn't need additional work. I am willing as a teacher to
provide examples that point someone in the right direction but if you want
me to do all the work then that's consulting and I charge for that.
--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS"

"Marcus" <tobhamlet@yahoo.ca> wrote in message
news:1113910183.385735.89310@o13g2000cwo.googlegro ups.com...[color=blue]
> Would you happen to have any samples of this, or know where I could
> find samples on this?
>
> Marcus
> *******
>[/color]