Cannot Find Correct O365 SharePoint Path For Excel VBA

I've been trying to create files from Excel to our company SharePoint site on Office 365, so far without success. Here is the code:

Dim dYear As Double

Dim sYear As String

Dim sDC01 As String

Dim sDC02 As String

Dim sDC04 As String

Dim sDC05 As String

Dim sDC06 As String

Dim sDC11 As String

Dim sDC21 As String

Dim sGenTotals As String

Dim sDC01FileTemplate As String

Dim sDC02FileTemplate As String

Dim sDC04FileTemplate As String

Dim sDC05FileTemplate As String

Dim sDC06FileTemplate As String

Dim sDC11FileTemplate As String

Dim sDC21FileTemplate As String

Dim sGenTotalsFileTemplate As String

Dim sDC01FileNew As String

Dim sDC02FileNew As String

Dim sDC04FileNew As String

Dim sDC05FileNew As String

Dim sDC06FileNew As String

Dim sDC11FileNew As String

Dim sDC21FileNew As String

Dim sGenTotalsFileNew As String



Sub cmdCreateNewFiles_Click()

   

    'Verify that the year entered is appropiate

   

    dYear = CInt(Worksheets("Home").txtYear.Value)

   

    sYear = Worksheets("Home").txtYear.Value

   

        If Len(sYear) <> 4 Then

       

            MsgBox "Enter a Four-Digit Year.", vbCritical, "Improper Year Value Entered"

           

            GoTo Release

       

        Else

       

            If dYear < 2000 Or dYear > 2100 Then

           

                MsgBox "Enter an appropriate Four-Digit Year.", vbCritical, "Improper Year Value Entered"

           

                GoTo Release

           

            End If

       

        End If

   

    dYear = 0

   

    'Create the new directories

   

    sDC01 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC1\"

    sDC02 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC2\"

    sDC04 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC4\"

    sDC05 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC5\"

    sDC06 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC6\"

    sDC11 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC11\"

    sDC21 = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC21\"

    sGenTotals = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\DC Generator Totals\"



    If Dir(sDC01 & sYear, vbDirectory) = "" Then



        MkDir sDC01 & sYear



    End If



    If Dir(sDC02 & sYear, vbDirectory) = "" Then



        MkDir sDC02 & sYear



    End If



    If Dir(sDC04 & sYear, vbDirectory) = "" Then



        MkDir sDC04 & sYear



    End If



    If Dir(sDC05 & sYear, vbDirectory) = "" Then



        MkDir sDC05 & sYear



    End If



    If Dir(sDC06 & sYear, vbDirectory) = "" Then



        MkDir sDC06 & sYear



    End If



    If Dir(sDC11 & sYear, vbDirectory) = "" Then



        MkDir sDC11 & sYear



    End If



    If Dir(sDC21 & sYear, vbDirectory) = "" Then



        MkDir sDC21 & sYear



    End If

   

    'Create the new files

   

    sDC01FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC01 Template.xlsm"

    sDC02FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC02 Template.xlsm"

    sDC04FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC04 Template.xlsm"

    sDC05FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC05 Template.xlsm"

    sDC06FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC06 Template.xlsm"

    sDC11FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC11 Template.xlsm"

    sDC21FileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\DC21 Template.xlsm"

    sGenTotalsFileTemplate = "\\CompanyName.sharepoint.com\sites\Amer-DC-SouthCampusEng\Shared Documents\Campus Compliance\Generator Run and Training Logs\New Building Templates\Generator Totals Template - On Campus.xlsx"

    sDC01FileNew = sDC01 & sYear & "\DC01.xlsm"

    sDC02FileNew = sDC02 & sYear & "\DC02.xlsm"

    sDC04FileNew = sDC04 & sYear & "\DC04.xlsm"

    sDC05FileNew = sDC05 & sYear & "\DC05.xlsm"

    sDC06FileNew = sDC06 & sYear & "\DC06.xlsm"

    sDC11FileNew = sDC11 & sYear & "\DC11.xlsm"

    sDC21FileNew = sDC21 & sYear & "\DC21.xlsm"

    sGenTotalsFileNew = sGenTotals & "\Generator Totals - On Campus " & sYear & ".xlsx"

   

    FileCopy sDC01FileTemplate, sDC01FileNew

    FileCopy sDC02FileTemplate, sDC02FileNew

    FileCopy sDC04FileTemplate, sDC04FileNew

    FileCopy sDC05FileTemplate, sDC05FileNew

    FileCopy sDC06FileTemplate, sDC06FileNew

    FileCopy sDC11FileTemplate, sDC11FileNew

    FileCopy sDC21FileTemplate, sDC21FileNew

    FileCopy sGenTotalsFileTemplate, sGenTotalsFileNew

   

    'Modify the new file

   

    Workbooks.Open (sGenTotals & "\Generator Totals - On Campus " & sYear & ".xlsx")



    Dim sht As Worksheet

    Dim fnd As Variant

    Dim rplc As Variant



    fnd = "\TBD\"

    rplc = "\" & sYear & "\"



    For Each sht In ActiveWorkbook.Worksheets

      sht.Cells.Replace what:=fnd, Replacement:=rplc, _

        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

        SearchFormat:=False, ReplaceFormat:=False

    Next sht



    ActiveWorkbook.Close SaveChanges:=True

   

    ThisWorkbook.Activate

   

Release:



    Worksheets("Home").txtYear.Value = ""



End Sub

I get a runtime error at this line:

If Dir(sDC01 & sYear, vbDirectory) = "" Then



I've tried this as well:

https://CompanyName.sharepoint.com/s...mpusEng/Shared Documents/Campus Compliance/Generator Run and Training Logs/



The code has to be able to run for whomever opens the file in the company, so I cannot use a local directory to save.


You can also find the post here.