title: VBA - Use Directory Path on Server without Drive Letter
body: <p>I have many macros that have the following path definition:</p>
  
  <pre><code>"X:\Test\3rd Party\Other Files\"
  </code></pre>
  
  <p>But what I need to, which is what I did with the vbscripts, is make it like this:</p>
  
  <pre><code>"\\ServerName\Folder\Test\3rd Party\Other Files\"
  </code></pre>
  
  <p>This is because the files that have the macros in them are on the server and they need to be able to be executed by anyone who has access to the server - and since each person might map the drive with a different letter and/or have different levels of access, the first option wont work.</p>
  
  <p>When I use this:</p>
  
  <pre><code>"\\ServerName\Folder\Test\3rd Party\Other Files\"
  </code></pre>
  
  <p>I get the error: </p>
  
  <blockquote>
    <p>Sorry, we couldn't find \ServerName\Folder\Test\3rd Party\Other
    Files. Is it possible it was moved, renamed or deleted?</p>
  </blockquote>
  
  <p>When I use this:</p>
  
  <pre><code>"\\ServerName\Folder\Test\3rd Party\Other Files"
  </code></pre>
  
  <ul>
  <li>Note the backslash missing at the end of the string</li>
  </ul>
  
  <p>I get the error:</p>
  
  <blockquote>
    <p>Excel cannot access "Other Files". The document may be read-only or
    encrypted.</p>
  </blockquote>
  
  <pre><code>Sub RenameOriginalFilesSheets()
  
      Const TestMode = True
      Dim WB As Workbook
  
      Application.ScreenUpdating = False
      rootpath = "\\ServerName\Folder\Test\Terminations\"
  
      aFile = Dir(rootpath &amp; "*.xlsx")
      Do
          Set WB = Application.Workbooks.Open(rootpath &amp; aFile, False, AddToMRU:=False)
  
          WB.Sheets(1).Name = Left$(WB.Name, InStrRev(WB.Name, ".") - 1)
          WB.Close True
  
          aFile = Dir()
          DoEvents
      Loop Until aFile = ""
  
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>