How to use a macro to open files from sharepoint?

Hi all,

Hope all are well.

I am using below macro to open excel file from an online sharepoint link and its working successfully.

Sub OpenAllExcelFilesInFolder()
    Dim folderPath As String
    Dim fileName As String

   folderPath = "https://myteams.gsk.com/sites/Serialisation Governance/Business Process Support/EU Data Errors in ATTP EMVS/Alerts/Test/"
   Workbooks.Open fileName:="https://myteams.gsk.com/sites/Serialisation Governance/Business Process Support/EU Data Errors in ATTP EMVS/Alerts/Alerts logs 2024/EMVS Alerts Log - Benelux 2024.xlsm"
End Sub

However, there are 9 more files to be opened and therefore i modified the line as below to open 2 files at once.

Workbooks.Open fileName:="https://myteams.gsk.com/sites/Serialisation Governance/Business Process Support/EU Data Errors in ATTP EMVS/Alerts/Alerts logs 2024/EMVS Alerts Log - Benelux 2024.xlsm","https://myteams.gsk.com/sites/Serialisation Governance/Business Process Support/EU Data Errors in ATTP EMVS/Alerts/Alerts logs 2024/EMVS Alerts Log - Scanda 2024.xlsm"

But now the code doesn't work. Can someone please help how to add code for multiple files? Thanks in advance.

Perfect. Repeating the Workbooks.Open code.

Thanks a bunch

Instead of writing a long path in each file, consider using the following macro:

Sub OpenAllExcelFilesInFolder()
    Dim folderPath As String
    Dim vFiles As Variant
    Dim i As Long

    folderPath = "https://myteams.gsk.com/sites/Serialisation Governance/Business Process Support/EU Data Errors in ATTP EMVS/Alerts/Alerts logs 2024/"

    vFiles = Array("EMVS Alerts Log - Benelux 2024.xlsm", _
                   "EMVS Alerts Log - Scanda 2024.xlsm", _
                   "File AAA.xlsm", _
                   "File BBB.xlsm", _
                   "File CCC.xlsm")

    For i = 0 To UBound(vFiles)
        Workbooks.Open fileName:=folderPath & vFiles(i)
    Next i
End Sub

Artik

@Artik.

That's great. Thanks a lot

Hi Artik,

Hope you are well.

Sorry but can you just help with 1 additional step.

After all files are opened, I want the code to take me back to sheet "Main Page" in the original file from where macro was run.

Could you please add this line at the end? Many thanks in advance.

Is it enough to add

ThisWorkbook.Activate

?

Artik

Its working perfectly. Thanks a bunch for your lightning response