Log In
Register
Home
May 14, 2020
Offline
Hi all - thanks for this wonderful place to learn and gain support.
I developed a Work Request Invoice and used the template code from "VBA to Create PDF from Excel Worksheet Then Email It With Outlook" to make it possible to convert the worksheet into a PDF and populate an Outlook email to be sent.
I would like to replace the prompt to store it somewhere on my local drive (we're all using PC) and have it automatically seek out a folder on Sharepoint to have the file saved to.
The Sharepoint "Path" outlined to the preferred destination (Walga Invoices) is: Region 3 CIB > Documents > Contract Support > Walga Invoices
However, the site URL references the original name for the Sharepoint defined by the original name for the Microsoft Teams team that the Sharepoint exists for. That URL is https://usepa.sharepoint.com/s.....20Invoices
My goal is to have the PDF generated and sent to that Walga Invoices folder - whether the VBA prompts me to save it there or does it automatically doesn't matter to me.
Thanks again, hope there's a solution in sight!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Patrick,
Update the following code in your file:
Dim PDFName As String
PDFName = ActiveSheet.Name & "_" & CurrentMonth & ".pdf"
Dim fs As Object: Set fs = CreateObject("Scripting.FileSystemObject")
PDFFile = DestFolder & Application.PathSeparator & PDFName
fs.CopyFile PDFFile, "\\usepa.sharepoint.com\sites\OPACIBELMSProcess\Shared Documents\Contract Support\Walga Invoices\" & PDFName
'If the PDF already exists...
Let us know if it works, only you can test it as I guess it's not public.
May 14, 2020
Offline
Thanks Catalin -
When I run this, I get the following error message:
Run-time error '-2147024672 (800700eo)':
Method 'CopyFIle' of object 'IFileSystem3' failed
When I click on Debug it highlights the line: fs.CopyFile PDFFile, "\\usepa.sharepoint.com\sites\OPACIBELMSProcess\Shared Documents\Contract Support\Walga Invoices\" & PDFName
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
You need to make sure you can access that folder from your computer.
Try using the folder path in file explorer, see what happens: \\usepa.sharepoint.com\sites\OPACIBELMSProcess\Shared Documents\Contract Support\Walga Invoices\
You might need to login, then try again the code.
Also, make sure the path is correct
1 Guest(s)
