Programmatically Moving PDFs to SharePoint 365

RMcCreavy
Lounger
Posts: 39
Joined: 02 Nov 2018, 16:12

Programmatically Moving PDFs to SharePoint 365

Post by 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.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Programmatically Moving PDFs to SharePoint 365

Post by HansV »

How do you specify the Sharepoint path?
Best wishes,
Hans

RMcCreavy
Lounger
Posts: 39
Joined: 02 Nov 2018, 16:12

Re: Programmatically Moving PDFs to SharePoint 365

Post by RMcCreavy »

HansV wrote:
13 Jan 2023, 19:41
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".

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Programmatically Moving PDFs to SharePoint 365

Post by HansV »

Does this work?

Code: Select all

OutputPath = "\\example.sharepoint.comsites\ExampleSubSite\ExampleDocLibrary\Example.pdf"
Best wishes,
Hans

RMcCreavy
Lounger
Posts: 39
Joined: 02 Nov 2018, 16:12

Re: Programmatically Moving PDFs to SharePoint 365

Post by RMcCreavy »

HansV wrote:
13 Jan 2023, 20:59
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.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Programmatically Moving PDFs to SharePoint 365

Post by HansV »

Sorry about that. I'll have to leave this to the experts...
Best wishes,
Hans

RMcCreavy
Lounger
Posts: 39
Joined: 02 Nov 2018, 16:12

Re: Programmatically Moving PDFs to SharePoint 365

Post by RMcCreavy »

HansV wrote:
13 Jan 2023, 21:19
Sorry about that. I'll have to leave this to the experts...
Don't apologize, I always appreciate your suggestions, Hans!