Error message with Macro

[Bob]

I have a macro that I need to output a pivot table to excel however I am
getting the following error message when I run it: The command or action
"output to" isn't available now. Here is the VBA code of the macro:

Option Compare Database

'------------------------------------------------------------
' BDI_Macro1
'
'------------------------------------------------------------
Function BDI_Macro1()
On Error GoTo BDI_Macro1_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "BDI_Complete_By_FSE", acViewNormal, acEdit
Call Shell("", 1)
DoCmd.OutputTo acQuery, "BDI_Complete_By_FSE",
"MicrosoftExcelBiff8(*.xls)", "C:\Files\BDI_Complete By FSE", False, "", 0
DoCmd.Close acQuery, "BDI_Complete_By_FSE"
DoCmd.OpenQuery "BDI_Complete_By_FSE_Crosstab", acViewNormal, acEdit
Call Shell("", 1)
DoCmd.Close , ""
DoCmd.OpenQuery "BDI_by_FSE_Month", acViewPivotTable, acEdit
Call Shell("", 1)
DoCmd.OutputTo acQuery, "BDI_by_FSE_Month",
"MicrosoftExcelBiff8(*.xls)", "C:\Files\BDI FSE Month", False, "", 0
DoCmd.Close , ""
DoCmd.OpenQuery "BDI FSE YTD", acViewNormal, acEdit
Call Shell("", 1)
DoCmd.OutputTo acQuery, "BDI FSE YTD", "MicrosoftExcelBiff8(*.xls)",
"C:\Files\BDI FSE YTD", False, "", 0
DoCmd.Close , ""
DoCmd.OpenQuery "BDI by FSE Region Summary Month", acViewNormal, acEdit
Call Shell("", 1)
DoCmd.OutputTo acQuery, "BDI by FSE Region Summary Month",
"MicrosoftExcelBiff8(*.xls)", "C:\Files\BDI Region Summary Month", False, "",
0
DoCmd.Close acQuery, "BDI by FSE Region Summary Month"
DoCmd.OpenQuery "BDI by Region YTD", acViewNormal, acEdit
Call Shell("", 1)
DoCmd.OutputTo acQuery, "BDI by Region YTD",
"MicrosoftExcelBiff8(*.xls)", "C:\Files\BDI by Region YTD", False, "", 0
DoCmd.Close acQuery, "BDI by Region YTD"


BDI_Macro1_Exit:
Exit Function

BDI_Macro1_Err:
MsgBox Error$
Resume BDI_Macro1_Exit

End Function

 

[Steve Schapel]

Bob,

First of all , this is a VBA procedure, not a macro.

I am not familiar with the Call Shell("", 1) that you have in your code.
I suspect it is either meaningless or unnecessary.

Also, I don't understand why you have the DoCmd.OpenQuery methods in the
code. I sort of expect that this is the reason for the error message...
you can't output the query because it is open at the time.

Also I am not familiar with the meaning of the "" in the DoCmd.Close ,
"" line of code. What are you trying to close... the query? What sort
of queries are they anyway?

Finally, I think the Output File argument of the OutputTo method needs
to specify the file name. What is "C:\Files\BDI_Complete By FSE"? Is
this supposed to be the name of a folder or a file? If a file, you will
need to add the ".xls" at the end.