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:
I get a runtime error at this line:
If Dir(sDC01 & sYear, vbDirectory) = "" Then
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.
VBA 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 SubI get a runtime error at this line:
If Dir(sDC01 & sYear, vbDirectory) = "" Then
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.
