Specifying the file name from a form when using Outputto method

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

When I use the "Outputto" method to output a query to an
Excel file, I am using a form to set a part of the path as
the filename. I get a run time error '2302'. Can anybody
tell me the best method to output a query to a filename
which is determined by the form?

DoCmd.OutputTo acOutputQuery, "qryShiftsRoster",
acFormatXLS, "C:\" & Forms![Shifts Roster Form]![Date 1]
& ".xls", False

I am trying to output the qryShiftsRoster query in Excel
to C:\230803.xls using the Date 1 field in the form to set
the 230803 in the filename.

Any suggestions would be greatly appreciated.

Regards,
Jason
 
you might try this:

DoCmd.OutputTo acOutputQuery, "qryShiftsRoster",
acFormatXLS, "C:\" & CStr(Forms![Shifts Roster Form]![Date
1])
& ".xls", False

if that doesn't work, you could try removing the final
comma and False from the code; the default value for
Autostart is False, anyway. that probably won't make a
difference but it never hurts to test it, Access gets a
little weird about simple things like that once in awhile.

if it works, but doesn't format the date the way you want,
you could try this:

DoCmd.OutputTo acOutputQuery, "qryShiftsRoster",
acFormatXLS, "C:\" & CStr(Format(Forms![Shifts Roster
Form]![Date 1],"ddmmyy"))
& ".xls", False

hth
 
Tina, thanx for your help with this. The problem was that
the filename i was saving to ended up as "2003 / 32".
When I did as you suggested and dropped the / it worked
perfectly. Thankyou enormously for this.
-----Original Message-----
you might try this:

DoCmd.OutputTo acOutputQuery, "qryShiftsRoster",
acFormatXLS, "C:\" & CStr(Forms![Shifts Roster Form]! [Date
1])
& ".xls", False

if that doesn't work, you could try removing the final
comma and False from the code; the default value for
Autostart is False, anyway. that probably won't make a
difference but it never hurts to test it, Access gets a
little weird about simple things like that once in awhile.

if it works, but doesn't format the date the way you want,
you could try this:

DoCmd.OutputTo acOutputQuery, "qryShiftsRoster",
acFormatXLS, "C:\" & CStr(Format(Forms![Shifts Roster
Form]![Date 1],"ddmmyy"))
& ".xls", False

hth

-----Original Message-----
When I use the "Outputto" method to output a query to an
Excel file, I am using a form to set a part of the path as
the filename. I get a run time error '2302'. Can anybody
tell me the best method to output a query to a filename
which is determined by the form?

DoCmd.OutputTo acOutputQuery, "qryShiftsRoster",
acFormatXLS, "C:\" & Forms![Shifts Roster Form]![Date 1]
& ".xls", False

I am trying to output the qryShiftsRoster query in Excel
to C:\230803.xls using the Date 1 field in the form to set
the 230803 in the filename.

Any suggestions would be greatly appreciated.

Regards,
Jason

.
Click to expand...
.
Click to expand...
 
Back
Top