0

Please consider code below. There open topic here -> "Open Sharepoint Excel Files With VBA". With some advises. My technical task is 1) Open file, 2) Update all links. 3) Save & Close file 4) Next file Code renters error message enter image description here Can some one correct me in my code, please?

Sub update_files()
    Dim FolderPath As String
    Dim wb_master As Workbook
    Dim ws_master As Worksheet
    Dim StrFile As String
    Dim TempPath As String
    Dim source_wb As Workbook

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False

    If wb_master Is Nothing Then Set wb_master = ThisWorkbook
    If ws_master Is Nothing Then Set ws_master = ThisWorkbook.Sheets("setup")

    FolderPath = ws_master.Range("A5") 'cell "A5" = https://customer_name.sharepoint.com/teams/BG003C9/FY20/08/Actuals/01/Preliminary/Actuals/

    'customer_name
    TempPath = Replace(Replace(FolderPath, "https://customer_name.sharepoint.com", "\\customer_name.sharepoint.com@SSL\DavWWWRoot\"), "/", "\")

    If ws_master.AutoFilterMode = True Then ws_master.AutoFilterMode = False
        SourceRow = 5
                    Do While Cells(SourceRow, "B").Value <> "" 'cell B5 ... Bn+1 until cell = "", stored file name on customer SharePoint
                        StrFile = ws_master.Range("B" & SourceRow).Value

                            source_file = TempPath & StrFile & ".xlsx"
                            Set source_wb = Workbooks.Open(source_file)
                                source_wb.LockServerFile 'locke file on server for next changes
                                source_wb.Activate 'active opend file
                                source_wb.UpdateLink Name:=ActiveWorkbook.LinkSources 'update all links
                                source_wb.Save 'save workbook
                                source_wb.Close 'close workbook
                            SourceRow = SourceRow + 1 ' Move down 1 row for source sheet
                        Loop
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.AskToUpdateLinks = True
End Sub
Alex_D.
  • 1
  • 5
  • What line is the error occurring? May also be worth having a look at this post: https://stackoverflow.com/questions/1344910/get-the-content-of-a-sharepoint-folder-with-excel-vba – newuser2967 Feb 11 '20 at 11:35
  • error appears in this line code -> source_file = TempPath & StrFile & ".xlsx" – Alex_D. Feb 11 '20 at 12:45

0 Answers0