Output To Excel File 1

[jadams0173]

I have a macro that runs a series of queries. At the end I send the result of a query to excel and save it on a network drive. Is it possible to have a "dynamic" name for the file so they don't overwrite each day? Maybe like a combo box value or the date using Now()?? Any other ideas?

Something like:
X:\folder\folder\TEST123 & now()&.xls
X:\folder\folder\TEST123 & forms!form!cbobox&.xls

 

[Presterman]

You could use a randomly generated number..but the date/datetime solution is a standard one...

 

[jadams0173]

Thanks for the response Presterman. I have experimented with date(). However excel doesn't like it because of the / in the file name. I'm trying to figure out how to format the / out of the date. I haven't tried now().

 

[jadams0173]

Ah...Date$ did the trick. Thanks for the advice Presterman!

 

[PHV]

An usual way:
"X:\folder\folder\TEST123_" & Format(Now, "yyyy-mm-dd") & ".xls"

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

 

[jadams0173]

Thanks PHV I'll give that a try. format(now,"yyy-mm-dd"), will that also include the time? This will be ran automatically every day, but the users will have the option to run it manually. The time would keep it from over writing on the same days. Thanks again PHV

 

[PHV]

With time included:
"X:\folder\folder\TEST123_" & Format(Now, "yyyy-mm-dd_hh_nn_ss") & ".xls"

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

 

[EVE734]

Hi - I'm looking to do something similar to jadams0173, but I don't want to use a date, but a value from a combo box as jadams0173 used as an example in the original thread. Can this be done?

Thanks,
Evelyn

 

[jadams0173]

Yes it can. I actually add a cbox value to mine. Goes something like.

X:\folder\folder\TEST123_" & Me!cboName

or if the cbox is on a different form. As long as the form is open..
This is my code that is in my application.

filename = "YourFileName" & Forms!FormName!cboName & "_" & Format(Now, "yyyy-mm-dd")

DoCmd.OutputTo acQuery, "QueryName", "MicrosoftExcel(*.xls)", "X:\Documents\" & filename & ".xls", True, ""

You can leave off everything after cboName if you don't want a date.

HTH

 

[EVE734]

Perfect - thanks so much!