0

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
  • Didn't really look into your VB code, but from personal experience I've noticed that uploading stuff to SP Online with VB snippets, it has been the most effortless when I've mapped the SharePoint's destination path to a Windows network drive path. – moe Dec 28 '17 at 10:07
0

The SharePoint online is different from SharePoint on-premise.

Then you could refer to the articles below to upload files to document library in SharePoint online by VB.

How to: Upload a File to a SharePoint Site from a Local Folder.

https://msdn.microsoft.com/en-us/library/office/ms454491(v=office.14).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-8

Uploading files to the SharePoint Document Library and updating any metadata columns.

https://www.codeproject.com/Articles/19717/Uploading-files-to-the-SharePoint-Document-Library

Upload a Document to the SharePoint Document Library using a Webservice.

http://www.dotnetcurry.com/ShowArticle.aspx?ID=297

Using SharePoint RPC to Upload Documents.

https://thechriskent.com/2015/04/15/using-sharepoint-rpc-to-upload-documents/

And you also could consider using REST API and jQuery to upload files to document library in sharepoint online. For more detailed information, refer to the article below.

Upload a file by using the REST API and jQuery.

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/upload-a-file-by-using-the-rest-api-and-jquery

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.