Share excel file on Sharepoint using VBA

[mariyarashkova]

I am working on a VBA file that has a button, which should upload some data from an Excel file to a Sharepoint file. The problem is when the Sharepoint file is open it takes 2/3 times to run it to upload the information. But I want the data to be uploaded whether Sharepoint is open or not. This is because SharePoint is used by more than 20 people and the upload will happen a couple of times a day, so I need to be able to run this the first time, no matter if someone has opened Sharepoint.

Sub PopulateDataToSharePoint()
    Dim SharePointFile As String
    Dim LocalFile As String
    Dim ExcelApp As Object
    Dim SharePointWorkbook As Object
    Dim LocalWorkbook As Object
    Dim i As Integer
    
    ' File paths
    SharePointFile = "https://company-my.sharepoint.com/personal/person/Documents/test/test.xlsx"
    LocalFile = ThisWorkbook.Path & "\report.xlsx"
    
    Set ExcelApp = CreateObject("Excel.Application")
    
    ' Open SharePoint
    Set SharePointWorkbook = ExcelApp.Workbooks.Open(SharePointFile)
    
    ' Open local file
    Set LocalWorkbook = ExcelApp.Workbooks.Open(LocalFile)
    
    ' Map the sheets between the local and SharePoint files
    Dim sheetMappings(1 To 3, 1 To 2) As String
    sheetMappings(1, 1) = "local1" ' Source sheet name
    sheetMappings(1, 2) = "sharepoint1" ' Destination sheet name in SharePoint
    sheetMappings(2, 1) = "local2" ' Source sheet name
    sheetMappings(2, 2) = "sharepoint2" ' Destination sheet name in SharePoint
    sheetMappings(3, 1) = "local3" ' Source sheet name
    sheetMappings(3, 2) = "sharepoint3" ' Destination sheet name in SharePoint
    
    ' Loop through the sheet mappings and copy data starting from the second row
    For i = 1 To 3
        Dim sourceSheetName As String
        Dim destinationSheetName As String
        sourceSheetName = sheetMappings(i, 1)
        destinationSheetName = sheetMappings(i, 2)
        
        ' Copy data
        LocalWorkbook.Sheets(sourceSheetName).Range("A2").CurrentRegion.Copy _
            Destination:=SharePointWorkbook.Sheets(destinationSheetName).Range("A2")
    Next i
    
    ' Close workbooks
    SharePointWorkbook.Close True
    LocalWorkbook.Close False
    
    ExcelApp.Quit
    
    Set ExcelApp = Nothing
    Set SharePointWorkbook = Nothing
    Set LocalWorkbook = Nothing
    
    MsgBox "Data has been populated successfully to SharePoint.", vbInformation
End Sub

 

[mariyarashkova]

Can anyone help with this? Do you think VBA is not the best option to do it?