(Solved) – How do I use Dir and Mkdir and reference Sharepoint?

(solved)-–-how-do-i-use-dir-and-mkdir-and-reference-sharepoint?

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)




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:UserssfPPWIPa 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 & “https://stackoverflow.com/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("https://stackoverflow.com/pplanners.sharepoint.com/sites/PP/Shared Documents/PP WIP/a Macros/results", vbDirectory) = "" Then

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

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

But nothing seems to work

Similarly, for MkDir, I’ve tried

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

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

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

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

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

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

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

tr = ThisWorkbook.Path
tr2 = Replace(Replace(tr, "http:", ""), "https://stackoverflow.com/", "")
tr3 = Replace(tr2, " ", " ")

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

Leave a Reply

Your email address will not be published. Required fields are marked *