Is there a way to get a Excel VBA macro to prompt for a file?

Question

My company's clinical software has a built in reporting function which can then export the result to an Excel spreadsheet.

One of our clients is pulling a turnover report and filtering it by the referring doctor. The problem arises in the fact that each referring doctor has a unique practice number which, due to the limitation of the reporting module, is placed as text next to the doctor's name, eg. Dr A Smith (Pr No: 1234567)

Unfortunately the client is one of those difficult clients that wants the practice number in a separate column. I have done a bit of research and found that I can run a VBA macro from a macro-enabled Excel spreadsheet that can apply the changes in another Excel spreadsheet. I can use a macro to extract the practice number and save it to a new column. Now I have found there is the ability to affect a specific file name or ALL xlsx files within a specific file location.

What I have not been able to find is VBA code where the user can run the macro and the user is asked which file they would like to run the macro against.

Is this even possible?

https://learn.microsoft.com/en-us/office/vba/api/excel.application.getopenfilename — BigBen, Aug 21 '20 at 15:49
https://stackoverflow.com/questions/32990696/prompt-user-to-open-another-workbook — BigBen, Aug 21 '20 at 16:00
Welcome to SO! Please take the [tour] and read [ask]! Before posting a question, **always** have a web-search on your questions title! — ComputerVersteher, Aug 21 '20 at 17:28
Does this answer your question? [Show "Open File" Dialog](https://stackoverflow.com/questions/1091484/show-open-file-dialog) — HackSlash, Aug 21 '20 at 17:35

score 1 · Accepted Answer · answered Aug 21 '20 at 17:14

The kind of visual component you are looking for is a so-called "openfile dialog" (or "open file dialog"). A simple Google search brought me to the following piece of code:

Dim strFile As String 
strFile = Application.GetOpenFilename(FileFilter:="Excel files (*.xlsx*), *.xlsx*", Title:="Choose an Excel file to open", MultiSelect:=True)

As mentioned in the first comments, there are quite some posts about this subject.

Is there a way to get a Excel VBA macro to prompt for a file?

1 Answers1