Object name argument

[Bonnie]

Can anyone help me please. I am opening a form (SelectIn) that has a combo
box that offers me the states from a table. When I select a state, a make
table query is run that pulls all records from that state to a table named
temp. Then I want to copy temp to an XLS named state_sales.xls (state being
the state that I have selected). I am receiving the error "requires an
object name argument". Can anyone tell me what I am doing wrong in this code?

Thanks in advance.

Function ToXLS()
On Error GoTo ToXLS_Err

DoCmd.OpenForm "SelectIn"
DoCmd.SetWarnings False

DoCmd.Close acQuery, GetState
DoCmd.SetWarnings True

DoCmd.OutputTo acTable, "TEMP", "MicrosoftExcelBiff8(*.xls)",
"C:\CARGO\" & [Forms]![SelectIN]![Combo8] & "_SALES.XLS", False, "", 0
Call Copydbfs


ToXLS_Exit:
Exit Function

ToXLS_Err:
MsgBox Error$
Resume ToXLS_Exit

End Function


Public Function Copydbfs()

FileCopy "C:\CARGO\" & [Forms]![SelectIN]![Combo8] & "_Sales.XLS",
"C:\" & [Forms]![SelectIN]![Combo8] & "Sales.XLS"

End Function

 

[Dirk Goldgar]

Can anyone help me please. I am opening a form (SelectIn) that has a
combo box that offers me the states from a table. When I select a
state, a make table query is run that pulls all records from that
state to a table named temp. Then I want to copy temp to an XLS
named state_sales.xls (state being the state that I have selected).
I am receiving the error "requires an object name argument". Can
anyone tell me what I am doing wrong in this code?

Thanks in advance.

Function ToXLS()
On Error GoTo ToXLS_Err

DoCmd.OpenForm "SelectIn"
DoCmd.SetWarnings False

DoCmd.Close acQuery, GetState
DoCmd.SetWarnings True

DoCmd.OutputTo acTable, "TEMP", "MicrosoftExcelBiff8(*.xls)",
"C:\CARGO\" & [Forms]![SelectIN]![Combo8] & "_SALES.XLS", False, "", 0
Call Copydbfs


ToXLS_Exit:
Exit Function

ToXLS_Err:
MsgBox Error$
Resume ToXLS_Exit

End Function


Public Function Copydbfs()

FileCopy "C:\CARGO\" & [Forms]![SelectIN]![Combo8] &
"_Sales.XLS", "C:\" & [Forms]![SelectIN]![Combo8] & "Sales.XLS"

End Function

What's this line supposed to do?

DoCmd.Close acQuery, GetState

If there's an open query datasheet named "GetState" that you want to
close, the line would have to be:

DoCmd.Close acQuery, "GetState"

But nothing in your description suggests that there would be such a
query open.

 

[Van T. Dinh]

I am not sure what you are trying to do with the code for function ToXLS()
and how you call this function or how the statement:

DoCmd.Close acQuery, GetState

is related to what you are trying but if I need to create an Excel file and
the user needs to selection some Parameter value before exporting, I you
follow the following process:

1. Create & save a Select Query which will be used as the source for the
export to Excel. This Query uses the ComboBox on the Form "frmParameter"
(see point 2) as the Parameter.

2. Create a Form "frmParameter" which has the ComboBox referred to in 1 and
a CommandButton to actually export to an Excel file. This CommandButton
should check that a value has been selected in the ComboBox and then run the
TransferSpreadsheet method (see Access VH Help) to export the data selected
by the Query to an Excel file.

If you need to make another copy of the Excel file, you can copy the created
file in this step also.

3. When the user needs to export to Excel, you code actually opens the Form
"frmParameter" first so that the user can select a value and then click the
CommandButton to actually perform the export (and copy).

BTW, I think the correct OutputFormat of the OutputTo statement should be
acFormatXLS. I can see "MicrosoftExcelBiff8(*.xls)" as an OutputFormat
option in Access VB Help. TransferSpreadsheet method is a bit more flexible
in this case.

 

[Bonnie]

That was it Dirk - when I took that line out it worked fine. I had been
monkeying with the code for a while and left that line in from something I
was trying earlier. Thanks so much for the help.

Bonnie

Can anyone help me please. I am opening a form (SelectIn) that has a
combo box that offers me the states from a table. When I select a
state, a make table query is run that pulls all records from that
state to a table named temp. Then I want to copy temp to an XLS
named state_sales.xls (state being the state that I have selected).
I am receiving the error "requires an object name argument". Can
anyone tell me what I am doing wrong in this code?

Thanks in advance.

Function ToXLS()
On Error GoTo ToXLS_Err

DoCmd.OpenForm "SelectIn"
DoCmd.SetWarnings False

DoCmd.Close acQuery, GetState
DoCmd.SetWarnings True

DoCmd.OutputTo acTable, "TEMP", "MicrosoftExcelBiff8(*.xls)",
"C:\CARGO\" & [Forms]![SelectIN]![Combo8] & "_SALES.XLS", False, "", 0
Call Copydbfs


ToXLS_Exit:
Exit Function

ToXLS_Err:
MsgBox Error$
Resume ToXLS_Exit

End Function


Public Function Copydbfs()

FileCopy "C:\CARGO\" & [Forms]![SelectIN]![Combo8] &
"_Sales.XLS", "C:\" & [Forms]![SelectIN]![Combo8] & "Sales.XLS"

End Function

What's this line supposed to do?

DoCmd.Close acQuery, GetState

If there's an open query datasheet named "GetState" that you want to
close, the line would have to be:

DoCmd.Close acQuery, "GetState"

But nothing in your description suggests that there would be such a
query open.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)