Export: Custom file name without folder path

[jack555]

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]

Hi. Using a macro, you could try ExportWithFormatting. Using VBA, you could try DoCmd.OutputTo.

 

[jack555]

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]

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]

Don't use a macro. Use VBA to export the file and you have all the control you need.

 

[Gasman]

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?

 

[jack555]

Use a default path?

How can we use the default path without specifying it? each user name has a different default path for downloads. sorry for the late reply.

 

[NoLongerSet]

How can we use the default path without specifying it? each user name has a different default path for downloads. sorry for the late reply.

The Current Directory (CurDir) function might give you what you need.

The Change Drive (ChDrive) and Change Directory (ChDir) functions can be used to change the Current Directory.

 

[arnelgp]

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]

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.