Search for a folder and get folder path using VBA

Hi there,

I have an excel program running over multiple computers and in it I designate a save folder path when certain items are created from the excel spreadsheet, i.e. PDF documents generated form the worksheets. Now the program is running on different computers I need to find the folder that the documents will will be saved in and then designate its file path to a variable. The folder will generally be on the desktop and is a one drive folder so all documents saved within it will upload to the cloud. Could anyone point me in the right direction on how to search for the folder without knowing its whole path?

Cheers,
Marcus

Variable to be shared with the other computers or just itself to be recalled later in code?

Once the folder is found I will then store its Path to a variable while the saving takes place. This process will repeat each time something is saved and the relevant code runs.

Does the user get prompt where to save the file? If so you can get the folder path after the user clicks saves and assign it to a public variable.

no it will automatically save to the folder the code finds (without knowing its whole path) and then gets the path, and saves the document within that folder.

The only difference between the file paths on the different computers is the computers username so I have come up with this:

Please Login or Register  to view this content.

will Application.UserName always return what I need on different computers or is there a more robust method to use?

Cheers,
Marcus