VBA Macro, file path to reference a cell in the workbook

  • Hello

    I need a little help with some VBA code I have inherited. I want to change it so that the user enters in the folder location in a cell on the first page of the workbook (Cell B3, work sheet name: Extract) as opposed to editing the macro each time they want to change folder location.

    Please find the code below, any help would be very much appreciated as I am very new to this!

  • Hello and Welcome to the Forum :)

    Let us say, for example, in your worksheet named Extract, the Cell B3 holds C:\Users\bsisko\Desktop\locations\

    Then, you should replace Line #18 in your macro which is currently:

    Const strPath As String = "C:\Users\bsisko\Desktop\locations\"

    by the following new instruction:

    strPath = Sheets("Extract").Range("B3").Value

    Hope the explanation is clear enough


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • You also need to declare the variable, a Constant cannot be changed within the code.

    Dim strPath As String 

    If you use Option Explicit at the top of the code then such errors will be flagged by the VB Editor's Intellisense. Read this to understand

    Option Explicit

  • Hi All

    Thank you for your help with this, I've made the changes as suggested above (in rows 9 [royUK], and 20 [Carim] of the code). I have added my file location to cell B3 (C:\Users\bsisko\Desktop\locations\ ) in a worksheet called "Extract".

    However I get the debug error and row 31 is high lighted in yellow when I access the code, also it opens the first workbook located in the "locations" folder to the first sheet which in this case is "DS9".

    Any ideas???


  • Hi

    As I've inherited the code the only parts I edit is the folder location (row 12 of code above) and sometimes the number of columns and range of cells (row 37) if I'm working with different workbook types. It's been working perfectly and I don't set the col value, so I'm not sure if that helps or not! :)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!