How to write a code that reads a folder path from a cell?

Hello,

I have a macro that needs access to files so I have to specify the folder path. So I usually have to go to the VBA code and update the folder path, e.g.:

Folder = "C:\Documents and Settings\Jane.Doe\Desktop\TESTING\"

However, I am trying to make this Excel workbook more user friendly so other uses can use it too. So I am planning to create an New Tab where the user inputs important data that the macro needs before pressing a 'Run Macro' button. For example:

The user is asked to input the folder path in cell B1, and the user simply enters: C:\Documents and Settings\Jane.Doe\Desktop\TESTING\

The question is, how do I make the VBA code get the folder path from cell B1? That is, how do I make Folder = "B1" of New Tab?

---

Similarly, the VBA code has a line that says:

Application.Workbooks.Open ("C:\Documents and Settings\Jane.Doe\Desktop\TESTING\Engineering_worksheet.xls")

The file name will not change, i.e.: Engineering_worksheet.xls, but the folder path will change, i.e.: C:\Documents and Settings\Jane.Doe\Desktop\TESTING\

So how would I write the above VBA code so that it works properly by obtaining the folder path from cell B1 of New Tab, but keeping the file name the same?

Thanks a lot and I am looking forward to your feedback

Hi
Should be :

Fname = Range("B1") & "" & "Engineering_worksheet.xls"
Workbooks.Open Filename:=Fname

if you are running the macro from other sheets then instead of Range("B1") should be Sheets("Sheet1").Range("B1")

Thank you, with your help I figured out how to do it

Hi

This topic help me a lot in developing my project. I will contribute more when I finished it.

If you want to get more materials that related to this topic, you can visit: How to write a KSA?


Best regards.