title: Change from absolute to relative workbook reference in Excel VBA
body: <p>I have written an Excel VBA macro that compiles all the information from various spreadsheets that are located in a specific folder and compiles them into one 'Master' Excel workbook. </p>
  
  <p>This currently works fine when using it on my computer, but I would like to adjust the code so that I can place the 'Master' spreadsheet and the folder containing the individual spreadsheet (the ones to be compiled) on a network drive, so that anyone can use it.</p>
  
  <p>I am fairly new to VBA and coding in general so I have a strong feeling there is probably an easy solution to fix my issue.</p>
  
  <p>I have attached my current macro that runs the absolute reference.</p>
  
  <pre><code>'Summary:    Open all Excel files in a specific folder and merge data
  '            into one master sheet (stacked)
  
  Dim fName As String, fPath As String, fPathDone As String, OldDir As String
  Dim LR As Long, NR As Long
  Dim wbData As Workbook, wbkNew As Workbook
  
  'Setup
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayAlerts = False
  
  Set wbkNew = ThisWorkbook
  wbkNew.Activate
  Sheets("Master").Activate
  
  If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
  
  If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
      Cells.Clear
      NR = 1
  Else
      NR = Range("A" &amp; Rows.Count).End(xlUp).Row + 1
  End If
  
  
  fPath = "C:\Folder-that-Excel-workbooks-are-located-in"
  On Error Resume Next
      MkDir fPathDone
  On Error GoTo 0
  OldDir = CurDir
  ChDir fPath
  fName = Dir("*.xlsx")
  
  
  Do While Len(fName) &gt; 0
      If fName &lt;&gt; wbkNew.Name Then
  
          Set wbData = Workbooks.Open(fName)
  
  
  
          LR = Range("C" &amp; Rows.Count).End(xlUp).Row
          If NR = 1 Then
              Range("C5:F" &amp; LR).EntireRow.Copy _
                  wbkNew.Sheets("Master").Range("A" &amp; NR)
          Else
              Range("C5:F" &amp; LR).EntireRow.Copy _
                  wbkNew.Sheets("Master").Range("A" &amp; NR)
          End If
  
          wbData.Close False
  
          NR = Range("C" &amp; Rows.Count).End(xlUp).Row + 1
  
          fName = Dir
      End If
  Loop
  
  ErrorExit:
  ActiveSheet.Columns.AutoFit
  Application.DisplayAlerts = True
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ChDir OldDir  
  </code></pre>