1

I have Excel VBA code that sends data to a word template (saves a docx and pdf file). The code then sends the pdf as an email via outlook.

It all works perfectly on my local drive, but opening, checking and saving folders and files to sharepoint has beaten me. I've spent hours searching and experimenting....there seems to be some conflicting ports (mainly on other forums).

Surely I'm not alone here....

Code that works on my local drive is:

thefilepath = Application.ActiveWorkbook.Path
Set wrdDoc = wrdApp.Documents.Open(thefilepath & "\Letter Template.docx", ReadOnly:=True)
strFolderPath = thefilepath & "\Results"
CheckDir (strFolderPath)


<Run code>

wrdDoc.SaveAs thefilepath & "\Results\" & thefilename & ".doc"
wrdDoc.ExportAsFixedFormat OutputFileName:=thefilepath & "\Results\" & thefilename & ".pdf", ExportFormat:=wdExportFormatPDF

Function CheckDir(Path As String)
If Dir(Path, vbDirectory) = "" Then
MkDir (Path)
End If
End Function

Problems…

  • Thefilepath is not accurate…..returns “C:\Users\sf\PP\WIP\a Macros”

  • I’m struggling to create the “results” subfolder

  • Docx and PDF naming is giving me errors.

My excel file is in

thisdir = https://pplanners.sharepoint.com/sites/PP/Shared Documents/PP WIP/a Macros

I want to save my docx and pdf files to thisdir & "/results" Ie.

resultsdir = https://pplanners.sharepoint.com/sites/PP/Shared Documents/PP WIP/a Macros/results

So to test if the results subdirectory exists I've tried....

If Dir("/pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/results", vbDirectory) = "" Then

If Dir("//pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/results", vbDirectory) = "" Then

If Dir("https://pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/results", vbDirectory) = "" Then

But nothing seems to work

Similarly, for MkDir, I’ve tried

MkDir "https://pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/Results"

MkDir "//pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/Results"

MkDir "/pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/Results"

MkDir "https://pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/Results"

MkDir "//pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/Results"

MkDir "/pplanners.sharepoint.com/sites/PP/Shared%20Documents/PP%20WIP/a%20Macros/Results"

Handy code that I’ve used to tweak name so far is:

tr = ThisWorkbook.Path
tr2 = Replace(Replace(tr, "http:", ""), "/", "\")
tr3 = Replace(tr2, " ", "%20")

Hoping to get some discussion going on file and folder management with sharepoint.

Simon Forer
  • 11
  • 2
  • `\\pplanners.sharepoint.com\sites\PP\Shared Documents\PP WIP\a Macros` might work – Tim Williams Dec 04 '19 at 04:54
  • you need to verify whether WEBDEV is enabled or not(server). and web client services in client Machine to access the SharePoint as network path. as mention @TimWilliams you use path dir (\\). if didn't work slight chances it will not work. bcz its need authentication i will recommended to use SharePoint code and upload. https://stackoverflow.com/questions/468469/how-do-you-upload-a-file-to-a-document-library-in-sharepoint – Jaynesh Sharma Dec 04 '19 at 07:52

0 Answers0