Automatically export from a query 1

[wintergreen]

Hello,

I have a form w/ a macro on a command button that should run a query and then automatically export to excel. I haven't been able to get the macro to automatically export - what should I do?

If this requires VB code, please be very specific because I am no EXPERT.

THANK YOU!
Summer

 

[smedvid]

Here is an Excerpt from some code I've used in the past. DoCmd.OutputTo is the easiest way to send a query to Excel imho.

htwh,

...
ElseIf Displaymode = "Excel" Then
'Export to Excel
lcErr_Message = "" & vbCr
lcErr_Message = lcErr_Message + "Would You Like to Launch Excel After the Excel File is Created?" & vbCr
Response = MsgBox(lcErr_Message, vbYesNo + vbQuestion + vbDefaultButton2, "Launch Excel")
If Response = vbYes Then ' User chose Yes.
' Yes - Start Excel after file created!
ViewExcelFlag = True
Else ' User chose No.
' No Don't Launch Excel
ViewExcelFlag = False
End If
'User must specify file name and storage location.

DoCmd.OutputTo acOutputQuery, lcQueryName, acFormatXLS, , ViewExcelFlag

If Response = vbYes Then ' User chose Yes.
' Yes - No Message Box Needed!
Else ' User chose No.
' No
MsgBox ("Excel File Created")
End If
...


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[wintergreen]

Thanks for the help, but I am a little confused. Where do I put this code? Do I attach it to the macro command that opens the query? - See, I told you I was not an EXPERT.

 

[smedvid]

Well... short of writing a Function in a Module, your options are limited.

I stay away from Macros since they offer little or no error handling capabilities.

You can create a macro using Action - OutputTO



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[kmkland]

Hiya Steve,
I have tried to implement your code excerpt into one of my codes, but I am having serious problems....
Here is the code in its (possible) entirety. Please, review and advise as to what I need to change/add, and also, I am not VB literate.

Private Sub Export_Claims_Click()
DoCmd.OutputTo acOutputQuery, [Account Claims], [.xls], [True], [True]

ElseIf Displaymode = "Excel" Then
  'Export to Excel
    lcErr_Message = "" & vbCr
    lcErr_Message = lcErr_Message + "Would You Like to Launch Excel After the Excel File is Created?" & vbCr
    Response = MsgBox(lcErr_Message, vbYesNo + vbQuestion + vbDefaultButton2, "Launch Excel")
    If Response = vbYes Then    ' User chose Yes.
       ' Yes - Start Excel after file created!
       ViewExcelFlag = True
    Else    ' User chose No.
       ' No Don't Launch Excel
       ViewExcelFlag = False
    End If
    'User must specify file name and storage location.

    DoCmd.OutputTo acOutputQuery, [Account Claims], acFormatXLS, , ViewExcelFlag

    If Response = vbYes Then    ' User chose Yes.
       ' Yes - No Message Box Needed!
    Else    ' User chose No.
       ' No
       MsgBox ("Excel File Created")
    End If

End Sub

Thanks so much!!!
Kim

 

[PHV]

Get rid of the the 1st DoCmd and the ElseIf lines.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Hi PH,

This for instance:

Private Sub Export_Claims_Click()
DoCmd.OutputTo acOutputQuery, [Account Claims], acFormatXLS, , ViewExcelFlag

    If Response = vbYes Then    ' User chose Yes.
       ' Yes - No Message Box Needed!
    Else    ' User chose No.
       ' No
       MsgBox ("Excel File Created")
    End If

End Sub

I get the following error when I run this script:
MS Access can't find the field "|" referred to in your expression.

Thanks...
Kim

 

[smedvid]

DoCmd.OutputTo acOutputQuery, [Account Claims],
acFormatXLS, , True

Will generate a file and open in excel.

ViewExcelFlag is a field that will contain TRue or False based on if you want to display the file in Excel or not. I created a variable to accept the response from the user as to if they want to open in Excel or not. see below..

Dim ViewExcelFlag as Boolean
ViewExcelFlag = False
Response = MsgBox("Open File in Excel?", vbYesNo + vbQuestion + vbDefaultButton2, "Launch Excel")
If Response = vbYes Then ' User chose Yes.
' Yes - Start Excel after file created!
ViewExcelFlag = True
Else ' User chose No.
' No Don't Launch Excel
ViewExcelFlag = False
End If
DoCmd.OutputTo acOutputQuery, [Account Claims],
acFormatXLS, , ViewExcelFlag

htwh


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[kmkland]

Hi Steve,
I entered this script, and it gives me the same error as last time ("MS Access can't find the field "|" referred to in your expression")and then highlights the following command when I select Debug:

DoCmd.OutputTo acOutputQuery, [Account Claims], acFormatXLS, , True

Suggestions??
Kim

 

[kmkland]

Anyone have any suggestions as to why I am getting an error message when I run this script????

Private Sub Export_Claims_Click()

DoCmd.OutputTo acOutputQuery, [Account Claims], acFormatXLS, , True

End Sub

Thanks so much.
Kim

 

[PHV]

I am getting an error message
Please, please, when you post this sort of sentence, at least post the whole error message too...
My guess;
DoCmd.OutputTo acOutputQuery, "[Account Claims]", acFormatXLS, , True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Hi PH,
I had already posted my error message twice. I just felt a bit redundant posting it a 3rd time, that's all.
Thanks for the tips - PH and Steve. I discovered that the query name should've been "Account Claims" instead of [Account Claims].
I appreciate all of your help!!!
Kim

 

[kmkland]

I noticed that this script is not prompting for my parameter set in the query.
Any suggestions on that???
You have been a tremendous help!
Kim

 

[PHV]

I had already posted my error message twice
OK, so at least say : I am getting same error message.

 

[kmkland]

Ok.
Please, disregard the last post.......
This is actually the same step as using a macro (OutputTo)to export the query, which is my problem. What I was hoping to accomplish was the same result as running the query then going under "File" and Exporting, but I do not want my user to resort to taking those steps...
Any thoughts, ideas????
Kim