Opening a Document in Excel Format in Access

[Marcus]

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
******

[Alan Webb]

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@SPAMh otmail.com
"It's not IT, it's IS"

"Marcus" <tobhamlet@yaho o.ca> wrote in message
news:1113871496 .908585.215920@ g14g2000cwa.goo glegroups.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]

[Marcus]

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
*******

[Alan Webb]

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.Applicati on.

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@SPAMh otmail.com
"It's not IT, it's IS"

"Marcus" <tobhamlet@yaho o.ca> wrote in message
news:1113910183 .385735.89310@o 13g2000cwo.goog legroups.com...[color=blue]
> Would you happen to have any samples of this, or know where I could
> find samples on this?
>
> Marcus
> *******
>[/color]

[Alan Webb]

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@SPAMh otmail.com
"It's not IT, it's IS"

"Marcus" <tobhamlet@yaho o.ca> wrote in message
news:1113910183 .385735.89310@o 13g2000cwo.goog legroups.com...[color=blue]
> Would you happen to have any samples of this, or know where I could
> find samples on this?
>
> Marcus
> *******
>[/color]