• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

MS Access VBA - User sets output directory for Excel Exports


I have many Queries that Excel Exports to an .xls, at a determined directory, like so,

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "ASP", "C:\Databases\Access to Excel.xls", True

This executes when a user pushes a button on a form.

Now, i need to substitute my  Path,  "C:\Databases\Access to Excel.xls", with a Variable

let say "Dim OutPutPath", And let the user select the output directory, via windows explorer,

so it copies the path information the user selected to my "OutPutPath" variable, since I need to export over 30 queries to Excel this way,and their is no way we can select the same directory in real time, 30 times!

1 Solution
Rey Obrero (Capricorn1)Commented:
see this sample db
Option 1: User uses Windows Explorer and navigates the path, copies the path, and pastes the path into an unbound textbox on the same form as the export button. You can then do something like this near the top of your sub
OutPutPath = txtCopiedPath <-- the name of the unbound textbox
Option 2: Use the CommonDialog Class under ActiveX Controls
Option 3: Use a tedious combination of comboboxes and listboxes and events - not recommended
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now