title: Excel VBA - Find Local File Location of Files on SharePoint
body: <p>I have a business account that created a SharePoint document folder, of which I've locally synced it to my computers via OneDrive (in D drive, accessible locally).</p>
  
  <p>In VBA of Excel, I know there is a way to call the OneDrive local location by using</p>
  
  <pre><code>environ("OneDrive")
  </code></pre>
  
  <p>However, I don't know how to specify that in my Excel file in order to locate the local file that's stored on SharePoint.</p>
  
  <p>The reason why this is important is that I need to use VBA to execute mail merge and that I discovered that a SharePoint-stored XLSM cannot be connected properly. Here is the code:</p>
  
  <pre><code>Private Sub InvitationLetter_Click()
  Dim WordApp As New Word.Application, ActionFormDocument As Word.Document, WorksheetName As String
  Dim OSPFullPath As String: OSPFullPath = ThisWorkbook.FullName
  
  WorksheetName = ActiveWorkbook.Sheets("Guest Speakers").Name
  
  With WordApp
    .DisplayAlerts = wdAlertsNone
    Set ActionFormDocument = .Documents.Open(ThisWorkbook.Path &amp; "\1.2 - Guest Speaker\02 - Guest Speaker Invitation Letter.docx", _
      ConfirmConversions:=False, ReadOnly:=False, AddToRecentfiles:=False)
  
    With ActionFormDocument
      With .MailMerge
        .MainDocumentType = wdFormLetters
        .SuppressBlankLines = False
        .OpenDataSource Name:=OSPFullPath, ReadOnly:=False, _
          LinkToSource:=True, AddToRecentfiles:=False, _
          Format:=wdOpenFormatAuto, _
          Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
          "User ID=Admin;Data Source=OSPFullPath;" &amp; _
          "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
          SQLStatement:="SELECT * FROM`" &amp; WorksheetName &amp; "$`" &amp; "WHERE `Status` = 'Pending' And `Nomination Details Alert` LIKE '%Urgent%'", _
          SubType:=wdMergeSubTypeAccess
        .ViewMailMergeFieldCodes = 0
        With .DataSource
          .FirstRecord = wdDefaultFirstRecord
          .LastRecord = wdDefaultLastRecord
        End With
      End With
    End With
  
    .DisplayAlerts = wdAlertsAll
  
    .Visible = True
    .Activate
  End With
  
  Unload Me
  End Sub
  </code></pre>
  
  <p>I need to point the OSPFullPath locally (e.g. D:\One Drive\Excel.xlsm) instead. I cannot use the 'fullname' function.</p>
  
  <p>Any environ code I can use, or anyway to detect the file location?</p>