Export Excel, rename file (1 Viewer)

[Gismo]

Hi All,

please could you assist.

I am exporting a query in excel.
I would like to add a control name from the active form also, i would like to rename the object name if possible, depending on selections made in my form

example:
I want to add a Registration as a pre fix to the exported file name

I tried : = [RegCBO] &" - "& CS Orders - Outstanding Spares QRY

in the Object Name but does not work

How would I accomplished this? please could you help?

 

[arnelgp]

you need to use VBA to prefix the Output name.
use DoCmd.OutputTo

 

[Gismo]

you need to use VBA to prefix the Output name.
use DoCmd.OutputTo

I tried that, in my own mind it would work , but it doesnt

DoCmd.OutputTo acOutputQuery, "Registration - " & " " & [Form]![CS Orders Detail - Main], [RegCBO] & " " & "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityPrint

 

[arnelgp]

see the proper Syntax of OutputTo.

 

[Gismo]

see the proper Syntax of OutputTo.

I have looked at the syntax

I am not sure how to interpreted the changes I need to add as there is no direct indication of adding additional information to the
outputTo syntax

 

[arnelgp]

DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", acFormatXLS, "d:\theoutputExcelName.xls",,,,acExportQualityPrint

 

[Gismo]

DoCmd.OutputTo acOutputQuery, "QueryNameHere", acFormatXLS, "d:\theoutputExcelName.xls",,,,acExportQualityPrint

I think we are missing each other

i need to add a control from my current form as a prefix to my excel name
if the name of the excel spreadsheet is "MyExcel' I need to replace it with "Registration - XXX - My Excel"
Registration will be hard coded
XXX is a value in my current form
My Excel is the query name

 

[Gismo]

Sorry, my bad, did not read properly

DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "Registration - " & " " & Forms![CS Orders Detail - Main]![RegCBO] & " " & ", True, ", , acExportQualityPrint

 

[arnelgp]

Add the .xls extension to your ouput name.

 

[Gismo]

Add the .xls extension to your ouput name.

I have an .xls extention
Now i get a compile error

DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityPrint

DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO]", True, "", , acExportQualityPrint

 

[arnelgp]

can you change this:

Excel97-Excel2003Workbook(*.xls)"

to: acFormatXLS

 

[Gismo]

can you change this:

Excel97-Excel2003Workbook(*.xls)"

to: acFormatXLS

I made the change it does not recognize the format
but there is an error with

", True, "", , acExportQualityPrint

i also removed the control from the form to test the code
i dont get the Text "Registration" in front of the excel file name

I do get the file extention select window

 

[arnelgp]

DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint

if it does not work, comment out that line and add a msgbox:

'DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint

msgbox "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls"

''''''''''
see if there is something unusual on the xls you want to create.

 

[Gismo]

DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint

if it does not work, comment out that line and add a msgbox:

'DoCmd.OutputTo acOutputQuery, "CS Orders - Outstanding Spares QRY", "Excel97-Excel2003Workbook(*.xls)", "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint

msgbox "Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls"

''''''''''
see if there is something unusual on the xls you want to create.

it works perfect
thank you very much,
if only i had the time to study these cases to know what to do as you do
much appreciated

 

[arnelgp]

in time, mr.gismo, all of us here starts from nothing.
there are many ebooks out there (for ms access).
not a good advise, but i always visit some torrent sites.

 

[Gasman]

I *think* you could use a TempVar

=[TempVars]![MyXLFile]

for the output filename, but I would use VBA as arnel advised.?