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/sites/OPACIBELMSProcess/Shared%20Documents/Contract%20Support/Walga%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!
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.comsitesOPACIBELMSProcessShared DocumentsContract SupportWalga 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.
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.comsitesOPACIBELMSProcessShared DocumentsContract SupportWalga Invoices" & PDFName
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.comsitesOPACIBELMSProcessShared DocumentsContract SupportWalga Invoices
You might need to login, then try again the code.
Also, make sure the path is correct
