Programmatically Moving PDFs to SharePoint 365

[RMcCreavy]

Hi all,

I have been stumped on this problem for far too long and wanted to reach out for help. I want to programmatically move PDFs from a local shared drive to a SharePoint 365 document library. I am open to just about any solution.

I already use VBA to move excel workbooks to a SP 365 doc library, and I have attempted to use similar code to move the PDFs using a FileSystemObject variable and the Microsoft Scripting Runtime/Microsoft Scriptlet Library references checked off.

VBA has no trouble moving the PDFs from one shared drive location to another, E:/Test1/TestPDF.pdf to E:/Test2/TestPDF.pdf for example, but when replacing the local drive address with the SharePoint URL in the file path string variable as I would for an excel workbook, I receive a Run-time error '52' Bad file name or number message.

From previous internet research, it appears that utilizing a power automate flow would require OneDrive as a middle ground, which I want to avoid if possible. I have also tried to explore the Microsoft Graph API functionality, but I admit this is currently outside of my comfort zone and would require a large time investment to gain that skillset.

[HansV]

How do you specify the Sharepoint path?

[RMcCreavy]

How do you specify the Sharepoint path?

Within VBA? As a string variable with the doc library URL and the full file name at the end, as I would for an Excel workbook.

Excel Workbook example

Code: Select all

Dim SaveFileName as String, Directory as String

Sub SharePoint_Save_Excel 

SaveFileName = ActiveWorkbook.Name
Directory = "https://example.sharepoint.com/sites/ExampleSubSite/ExampleDocLibrary/"
ActiveWorkbook.SaveAs FileName:=Directory & SaveFileName, FileFormat:=51, CreateBackup:=False

End Sub

PDF example (does not work)

Code: Select all

Dim InputPath as String, OutputPath as String, Example_PDF as FileSystemObject

Sub SharePoint_Save_PDF

Set Example_PDF = CreateObject("Scripting.FileSystemObject")

InputPath = "E:\Test\Example.pdf"
OutputPath = "https://example.sharepoint.com/sites/ExampleSubSite/ExampleDocLibrary/Example.pdf"

Example_PDF.CopyFile InputPath, OutputPath

End Sub

The above code for the PDF will work if the OutputPath string variable is set to = another local address like "E:\Test2\Example.pdf".

[HansV]

Does this work?

Code: Select all

OutputPath = "\\example.sharepoint.comsites\ExampleSubSite\ExampleDocLibrary\Example.pdf"

[RMcCreavy]

Does this work?

Code: Select all

OutputPath = "\\example.sharepoint.comsites\ExampleSubSite\ExampleDocLibrary\Example.pdf"

It spins for awhile, which is slightly encouraging, but then I receive Run-time error '76' Path not found.

[HansV]

Sorry about that. I'll have to leave this to the experts...

[RMcCreavy]

Sorry about that. I'll have to leave this to the experts...

Don't apologize, I always appreciate your suggestions, Hans!