How to open SharePoint File from one Excel Workbook and then edit opened file using VBA

[Lexcon07]

I am trying to use VBA to open a SharePoint File and write data to it and then save and close the file.

The code below works when it is a traditional file path. By defining the term Destination, it allows me to differentiate between the original workbook that I am copying data from and the "Destination" workbook that I am pasting data to.

        Dim Destination As Workbook, dPath, Source As Range
        
        dPath = [filepath]
        
        Set Destination = Workbooks.Open(dPath)

I can open the SharePoint file using the following code:

        Dim Destination As Workbook, dPath, Source As Range
        
            dPath = [sharepointfilepath]
            
            ActiveWorkbook.FollowHyperlink Address:=(dPath)

However, I cannot figure out how to set the term "Destination" to be the opened SharePoint file, so that the rest of my copying and pasting code works. The following code results in a "Compile Error: Expected End of Statement"...

Set Destination = ActiveWorkbook.FollowHyperlink Address:=(dPath)

I've tried making the newly opened SharePoint File the "Active" Workbook, but it doesn't work with all of the copying and pasting between files.

Any advice or guidance would be greatly appreciated.

 

[severynm]

Is there a particular reason you are using FollowHyperlink instead of Workbook.Open?

Dim wb As Workbook
Set wb = Application.Workbooks.Open("https://sharepointcompanyname.sharepoint.com/pathtofile/filename.xlsx")

The documentation is not super clear about this, but it appears that FollowHyperlink does not actually return an object - it just follows a hyperlink. This seems to be confirmed with testing with VarType. VarType(Application.Workbooks.Open(...)) returns 9 (object), while VarType(ThisWorkbook.FollowHyperlink(...)) results in a compile error.

 

[Lexcon07]

Is there a particular reason you are using FollowHyperlink instead of Workbook.Open?

Dim wb As Workbook
Set wb = Application.Workbooks.Open("https://sharepointcompanyname.sharepoint.com/pathtofile/filename.xlsx")

The documentation is not super clear about this, but it appears that FollowHyperlink does not actually return an object - it just follows a hyperlink. This seems to be confirmed with testing with VarType. VarType(Application.Workbooks.Open(...)) returns 9 (object), while VarType(ThisWorkbook.FollowHyperlink(...)) results in a compile error.

Hi Severynm,

Thanks for the comment.

Unless I am doing something wrong, the Workbooks.Open function doesn't appear work with a SharePoint file path. It works fine with a traditional file path, but I've tried using a SharePoint (i.e. URL) file path (that works when I use it as a hyperlink), but I get a "Method 'Open' of object 'Workbooks' failed" error. Other than my SharePoint file path being much longer than in your example, I don't see any other differences between the two lines of code.

When I use FollowHyperlink, I can get the file to open, but I can't seem to figure out how to then make it the "Destination" workbook.

 

[severynm]

How are you finding the SharePoint URL? If you open the file with the desktop app, go to File → Info → Copy Path, and remove the ?web=1 from the end. This is the URL to use with Workbooks.Open.

If you are syncing the SharePoint location to your computer, you can also refer to the C: based path of the file, but there's a couple of things to set up to get that to work correctly; I've learned about the URL method recently and find its a much better solution (for me at least).

 

[Lexcon07]

Hi Severynm,

Thanks you again for the reply. It turns out that there is a character limit (255 characters) on hyperlink formulas in Excel/VBA that was creating the error. My SharePoint File Path was longer than the limit. Once I renamed a few folders, it worked fine.