Export: Custom file name without folder path (1 Viewer)

jack555

Member
Local time
Tomorrow, 02:45
Joined
Apr 20, 2020
Messages
93
Via macro or VBA we can specify the file name with the location for the query or report export. I am exporting via macro but now the query or report picked up as the default name where the user can change while saving it.

Question: in macro how to specify the export file name without specifying the file path? save location will be chosen by the user each time. however, would like to have a custom filename rather than "qryExport" or "rptExport".
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:45
Joined
Oct 29, 2018
Messages
21,358
Hi. Using a macro, you could try ExportWithFormatting. Using VBA, you could try DoCmd.OutputTo.
 

jack555

Member
Local time
Tomorrow, 02:45
Joined
Apr 20, 2020
Messages
93
Hi. Using a macro, you could try ExportWithFormatting. Using VBA, you could try DoCmd.OutputTo.
Yes, I follow ExportWithFormatting. However in the output file either I have to leave it blank or specify the full file path with name. cannot specify the name without a file path. is there any way to achieve this via macro?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:45
Joined
Feb 19, 2013
Messages
16,553
Not sure about macros, too limiting, but in vba you would use file dialog to enable the user to pick a folder, then concatenate the chosen folder path and the file name before outputting/exporting.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:45
Joined
Feb 19, 2002
Messages
42,976
Don't use a macro. Use VBA to export the file and you have all the control you need.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:45
Joined
Sep 21, 2011
Messages
14,048
Yes, I follow ExportWithFormatting. However in the output file either I have to leave it blank or specify the full file path with name. cannot specify the name without a file path. is there any way to achieve this via macro?
Use a default path?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:45
Joined
May 7, 2009
Messages
19,169
Question: in macro how to specify the export file name without specifying the file path? save location will be chosen by the user each time. however, would like to have a custom filename rather than "qryExport" or "rptExport".
you can use DoCmd.CopyObject to copy the query to a New query name.
later after you have exported it, you can delete it using DoCmd.DeleteObject.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:45
Joined
Feb 19, 2002
Messages
42,976
If the download directory is the same as where the FE is copied to or a child of that directory, you can figure it out. Otherwise, you need an additional column in your users table to hold their download path and they must each set theirs up unless you can prepopulate the table for them.
 

Users who are viewing this thread

Top Bottom