I can't update worksheets objects links on powerpoint VBA - OneDrive Folder

Question

I've already read in the forums but noone has my exactly problem, so here we go.

I have my excel and powerpoint files in a OneDrive folder (the Powerpoint is in subfolder), the powerpoint has 100 links. So, in a forum someone suggested that to get the local OneDrive path, you should turn off the process. I did it. I have to have the excel file open, because the processing time is really slow if the excel is closed. So If I have opened the excel file and run the macro (in other folder diferent to OneDrive) it runs ok, but if I try to do the same but in the OneDrive folder, it generated the next error into the code line pptShape.LinkFormat.Update:

Error -2147188160 (80048240) in runtime. LinkFormat (unknown member): Invalid request. The linked file was unavailable and could not be updated

If I have the excel file closed, the macro runs ok, but the process is so slow (almost 30 minuts), because it open and close the excel a hundred times.

does anyone knows why it happened? How can I fix it? I'll appreaciate your help. here is the code to update the links

Sub updatelinks_1()
Call Shell("cmd.exe /S /C" & "%LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /shutdown")
Application.DisplayAlerts = ppAlertsNone

Dim pptPresentation As Presentation
Dim pptSlide As Slide
Dim pptShape As Shape
            
        
'Set the variable to the PowerPoint Presentation
Set pptPresentation = ActivePresentation

'Loop through each slide in the presentation
For Each pptSlide In pptPresentation.Slides

    'Loop through each shape in each slide
    For Each pptShape In pptSlide.Shapes

    'Find out if the shape is a linked object or a linked picture
    If pptShape.Type = msoLinkedOLEObject Then

        Dim name, path1, path2, source, begin, search1, cells As String
        Dim limit1 As Integer
                  
        name = pptShape.LinkFormat.SourceFullName

        limit1 = InStr(1, name, "!")
                                
        cells = Right(name, Len(name) - limit1)
        
         
        search1 = "subfoldername"

        path1 = Application.ActivePresentation.FullName
               
        begin = InStr(1, path1, search1)
        begin = Left(path1, begin - 1)

        file1 = Dir(begin & "*.xlsm")

        source = begin & file1

    End If

    path2 = source & "!" & cells
     
        pptShape.LinkFormat.SourceFullName = path2
        
        'update method. code line where generate error
        pptShape.LinkFormat.Update
           
    End If
    Next
Next

'Update the links (If I use this method on OneDrive folder, it doesn't work and broke all the links because replace the Link name with only the excel file name, not the sheets and cells)
  ' pptPresentation.UpdateLinks

Call Shell("cmd.exe /S /C" & "start %LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /background")
Set pptPresentation = Nothing
Set pptSlide = Nothing
Set pptShape = Nothing

Application.DisplayAlerts = ppAlertsAll
End Sub

Answer 1

Good morning everyone.

As I have not seen the solution, I'd like to add my 2 cents. I have had a similar issue, on a win10 Platform running Office 365. In my case both files are on the same laptop. I have seen that the powerpoint VBA procedure to update the path takes a long time by default. ( around 4 Minutes for me as there are 22 linked Objects). One can speed it up by manually open the target excel file before launching the Powerpoint VBA. It becomes effectively faster but I hit the issue where for each link the ppt vba procedure tries to update, we get a pop up window telling us that Excel can't open 2 files with same name.

I've tried to add in the PowerPoint VBA procedure : Application.DisplayAlerts = False , but is logically inefficient as applies to the PPT application and not to the Excel app !

I finally found one quick (and logic) solution :

at the beginning of the PowerPoint VBA, I ask user to locate the target excel file :

Set XlApp = CreateObject("Excel.Application")
ExcelFile = XlApp.GetOpenFilename(, , "Would you please locate your excel File")

And after, I just Open the target file, and set it with displayLAerts to False.

XlApp.Visible = True
Set xlWorkbook = XlApp.Workbooks.Open(ExcelFile, True, False)

Doing so, I no longer get warnings.

Full source code available .

Wish you a nice day !