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)
Please suggest.
Thanks.
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
