open query/report in excel (1 Viewer)

[spinkung]

hi all,

i have a form withj a button that when clicked should open preferably a query in an instance of excel, alternatively a report.

Is there a quick, single line of code in vba that will do this??

i have..
DoCmd.OutputTo acOutputQuery, "my query", acFormatXLS

but that tries to save the file to disk. i just want it to open in excel??

I don't really want to have to go down the route of creating an instance of excel first etc.

Thanks. Spin.

 

[DCrake]

Why do you want to open it in Excel if you don't want Excel to be present?

Can't see the logic?

David

 

[spinkung]

sorry must'nt have explained myself properly.

I want excel to open with the results of my query so i can analyze the data but when using OutputTo the user is prompted to save the file on disk. I only want to open excel and then close without saving if i want.

does that help??

thanks, spin

 

[spinkung]

sorry to be a pain, am i right in thinking this isn;t going to possible??

thanks.

 

[wiklendt]

you could get access to save the output to the same file everytime. so, it would overwrite the file and you could put it in some out-of-the-way location, like C:\temp. or you could save it automatically then delete it (use the 'kill' function) later.

 

[boblarson]

hi all,

i have a form withj a button that when clicked should open preferably a query in an instance of excel, alternatively a report.

Is there a quick, single line of code in vba that will do this??

i have..
DoCmd.OutputTo acOutputQuery, "my query", acFormatXLS

but that tries to save the file to disk. i just want it to open in excel??

I don't really want to have to go down the route of creating an instance of excel first etc.

Thanks. Spin.

You can use my code that I have on my site here to send a query to Excel (without saving).

 

[wiklendt]

You can use my code that I have on my site here to send a query to Excel (without saving).

Hi bob,

how do i call this function? i've tried:

SendTQ2Excel(strDoc, strSheetName)

, where it just highlights the line in red, indicating there's something wrong with it

Call SendTQ2Excel(strDoc, strSheetName)

, which gives me a compile error: byref argument type mismatch. however, my query name is provided and declared as a string...

and i can't think of what else to use?

 

[boblarson]

Normally without the CALL keyword you call without the parens:

SendTQ2Excel strDoc, strSheetName

But I'm not sure why it would throw an error if you have assigned the sheet name as a string, unless it exceeds 34 characters or has special characters in it. But that should get you a different error later on, I would think.

What is the full code you are using and did you copy the code I gave into a standard module and then save it (as a different name than the function)? What is the code that comes before this which sets the values of the strDoc, etc.

 

[boblarson]

I just retested with a sample in my test database and it works fine for me.

Are you sure you are declaring your variables like

Dim strDoc As String
Dim strSheetName As String

or

Dim strDoc As String, strSheetName As String

and not

Dim strSheetName, strDoc As String

which would end up with strSheetName as a VARIANT and not a string.

 

[wiklendt]

I just retested with a sample in my test database and it works fine for me.

Are you sure you are declaring your variables like

Dim strDoc As String
Dim strSheetName As String

or

Dim strDoc As String, strSheetName As String

and not

Dim strSheetName, strDoc As String

which would end up with strSheetName as a VARIANT and not a string.

aha! that was it. i was using Dim strSheetName, strDoc As String, and i had always (mistakenly, as it would seem) assumed that the "list" inbetween would ALL be declared as strings.

now that i've changed it, it all works a treat niiiiiice thank you!

edit: oh, yeah, and i had to change calling the procedure to:

"SendTQ2Excel strDoc, strSheetName" (without the parentheses i had originally)

 

[Zaeed]

DoCmd.OutputTo acOutputQuery, "my query", acFormatXLS, "C:\querysave.xls", 1

that saves it to your c drive.. but opens it in excell for you, so you don't have to do anything with the file..

 

[wiklendt]

DoCmd.OutputTo acOutputQuery, "my query", acFormatXLS, "C:\querysave.xls", 1

that saves it to your c drive.. but opens it in excell for you, so you don't have to do anything with the file..

yup, i was originally using the outputto command BUT i wanted extra formatting that outputto didn't allow (like freeze panes, landscape, header and footer info....)

 

[Zaeed]

Yeah, the OP didn't though

 

[wiklendt]

Yeah, the OP didn't though

oops, my bad, you're right. i did kinda hijack the thread, didn't i... *sheepish*