VBA - Create Folder in Sharepoint if doesn't already exist

JC66

New Member
Joined
Sep 16, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Hey all, I'm hoping you can help.
I've searched many versions of this, none of which I can get a resolution from with other peoples resolutions, so any help is massively appreciated. I'm completely stuck! :(

I've an excel workbook which completes a statement reconciliation, matching a supplier statement to my company ledger. All of that works fine, however when trying to save the file in Sharepoint it needs to be saved into two folders which may or may not be already present: (Sharepoint site > Statement Recs > Complete > 2020-2021 > Supplier Name) where "2020-2021" and "Supplier Name" are the folders I'm looking to check already exist, and if not, create them. IE: if dir1

So far I've the below:
Code:
Dim dir1 as String
Dim dir2 as String
Dim f as String

dir1 = worksheets("A").Range("B14") 'This will be the folder for financial year
dir2 = worksheets("A").Range("B15") 'This will be the folder for supplier name
filename = worksheets("A").Range("B16") 'This will be the filename

'******    If Dir("\\company.sharepoint.com/teams/companydepartment/Shared Documents/Statement Reconciliation/Completed Recs/" & dir1, vbDirectory) = "" Then
        MkDir "\\company.sharepoint.com/teams/companydepartment/Shared Documents/Statement Reconciliation/Completed Recs/ & dir1)
    Else
    End If

    If Dir("\\company.sharepoint.com/teams/companydepartment/Shared Documents/Statement Reconciliation/Completed Recs/" & dir1 & "/" & dir2, vbDirectory) = "" Then
        MkDir "\\company.sharepoint.com/teams/companydepartment/Shared Documents/Statement Reconciliation/Completed Recs/ & dir1 & "/" & dir2)
    Else
    End If

filepath = "http://company.sharepoint.com/teams/companydepartment/Shared Documents/Statement Reconciliation/Completed Recs/" & dir1 & "/" & dir2 &"/"

ActiveWorkbook.SaveAs filename:=filepath & fname & ".xlsm", _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled

When running the code, it hangs for about 30 seconds before I get Run-time error 52 "Bad File name or number" on line i've marked with '****** and I've tried many combinations of "/" and "\" as seen in various other posts.
I'm just completely stumped so any help is much appreciated :)
Thank you in advance!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,127,251
Messages
5,623,612
Members
415,981
Latest member
Baltwin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top