How to reference a file on SharePoint?

Question

We migrated docs to SharePoint online, but still need to run VBA code for a mail merge.

I updated the ChDir to (ActiveDocument.AttachedTemplate.Path) and that now resolves.

An error occurs on line Open strFile For Input As #1.

Private Sub Document_Close()
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

Private Sub Document_Open()
    Dim strFileName As String
    Dim strDBName As String
    Dim strTabName As String
    
    ChDrive "S"
    'DB is in current folder
    ChDir (ActiveDocument.AttachedTemplate.Path)
    'DB is in parent folder
    'ChDir (ActiveDocument.Path & "\..")
    strCurDir = CurDir
    ''EDIT BELOW 2 LINES ONLY
    strFileName = ImportVariable(strCurDir + "\settings.txt")
   
    strTabName = "NEW DB"
    ''EDIT ABOVE 2 LINES ONLY
    
    strLongName = strCurDir + "\" + strFileName

    strSQL = "SELECT * FROM `'" & strTabName & "$'`"
    
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
        ActiveDocument.MailMerge.OpenDataSource Name:= _
        strLongName, ReadOnly:=False, LinkToSource:=True, _
        Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" + strFileName + ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Databas" _
        , SQLStatement:=strSQL, _
        SubType:=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub


Private Function ImportVariable(strFile As String) As String
    Open strFile For Input As #1
    Line Input #1, ImportVariable
    Close #1
End Function

Your title says *code error*, but your post does not include any information about that error, which should include the **complete, exact error message**. Also, you say *having a problem*, but never describe any problem at all. Please take some time to complete the [tour] and read the [help] pages, especially [ask], and then [edit] your post to clearly describe the problem and include all of the relevant details, as well as asking a **specific question** related to the code you've posted. — Ken White, Nov 19 '22 at 20:49
When I open the template, it debugs and has "Open strFile For Input As #1" highlighted in yellow. It was originally mapped to S drive prior to migration. I'm using a explorer mapped drive (WebDAV) so not resolving S drive correctly and need additional pathing in the script. I'm guessing something is wrong at the CurDir stage? — CraigP, Nov 19 '22 at 20:55

score 0 · Answer 1 · answered Nov 20 '22 at 01:09

Based on how you asked the question, I'm assuming that the S drive was mapped to a local system, location of the data file. After the migration to SharePoint online, the data file is no longer stored in the S drive. If this is the case, then, 1) download the data file from the SharePoint online location, 2) make sure it's save in the S drive. Then run the original VBA script that used the S drive to open the file.

Having said that, the solution provided below doesn't require downloading the file, https://sharepoint.stackexchange.com/questions/174690/open-file-in-a-sharepoint-list-via-vba

How to reference a file on SharePoint?

1 Answers1