I’m a newbie in VBA coding.
I would like to convert an excel sheet into PDF and then to publish it into a SharePoint site.
I succeeded doing it when we were on SharePoint 2013. My code convert the sheet into PDF, save it into local disk, copy it and then paste it into the SharePoint site.
Now, we migrated to SharePoint Online. I simply replaced the former URL by the new one but it does not work. I get an error message
Path not found (Error 76)
Sub PDFSP() Dim wsA As Worksheet Dim wbA As Workbook Dim wrA As Range Dim strTime As String Dim strName As String Dim strPath As String Dim strFile As String Dim strPathFile As String Dim myFile As Variant Dim SharepointAddress As String Dim LocalAddress As String Dim objNet As Object Dim FS As Object Dim team As String 'On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsA = Sheets("Dashboard One Pager") Set wrA = Range("ONEPAGER") Set TeamReport = Range("C3") strTime = Format(Now(), "yyyymmdd\_hhmm") 'get active workbook folder, if saved strPath = wbA.Path If strPath = "" Then strPath = Application.DefaultFilePath End If strPath = strPath & "\" 'replace spaces and periods in sheet name strName = Replace(wsA.Name, " ", "") strName = Replace(strName, ".", "_") fName = Range("c3").Value 'create default name for savng file strFile = fName & "_" & strTime & ".pdf" strPathFile = strPath & strFile 'use can enter name and ' select folder for file myFile = Application.GetSaveAsFilename _ (InitialFileName:=strPathFile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and FileName to save") 'export to PDF if a folder was selected If myFile <> "False" Then wsA.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=myFile, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False End If SharepointAddress = "//xxxx365.sharepoint.com/sites/xxxx/xxx/xxx/xxxx/xxx\" Set objNet = CreateObject("WScript.Network") Set FS = CreateObject("Scripting.FileSystemObject") If FS.FileExists(strPathFile) Then FS.CopyFile strPathFile, SharepointAddress End If Set objNet = Nothing Set FS = Nothing MsgBox "Report has been successfully uploaded in the relevant location of Sharepoint" End Sub