Create folder path if does not exist (saving issue)
You need to check if the folder exists. If not, then make it. This function does the job. Place it before saving your workbook.
'requires reference to Microsoft Scripting Runtime
Function MkDir(strDir As String, strPath As String)
Dim fso As New FileSystemObject
Dim path As String
'examples for what are the input arguments
'strDir = "Folder"
'strPath = "C:\"
path = strPath & strDir
If Not fso.FolderExists(path) Then
' doesn't exist, so create the folder
fso.CreateFolder path
End If
End Function
it's better to avoid using Shell
command for this as it is likely to return errors for various reasons. Your code even ignores/bypasses errors which is not wise.
No reference to Microsoft Scripting Runtime required.
Dim path_ As String
path_ = "G:\BUYING\Food Specials\4. Food Promotions\(1) PLANNING\(1) Projects\Promo Announcements\" & .Range("H" & i) & "\KW " & .Range("A" & i)
Dim name_ As String
name_ = file & " - " & file3 & " (" & file2 & ").xlsx"
With CreateObject("Scripting.FileSystemObject")
If Not .FolderExists(path_) Then .CreateFolder path_
End With
wbTemplate.SaveCopyAs Filename:=path_ & "\" & name_
OR
Dim path_ As String
path_ = "G:\BUYING\Food Specials\4. Food Promotions\(1) PLANNING\(1) Projects\Promo Announcements\" & .Range("H" & i) & "\KW " & .Range("A" & i)
Dim name_ As String
name_ = file & " - " & file3 & " (" & file2 & ").xlsx"
If Len(Dir(path_)) = 0 Then MkDir path_
wbTemplate.SaveCopyAs Filename:=path_ & "\" & name_
Run this Macro two times to confirm & test.
First run should create a direcotry "TEST" on desktop and MsgBox "Making Directory!".
Second run should just MsgBox "Dir Exists!"
Sub mkdirtest()
Dim strFolderPath As String
strFolderPath = Environ("USERPROFILE") & "\Desktop\TEST\"
CheckDir (strFolderPath)
End Sub
Function CheckDir(Path As String)
If Dir(Path, vbDirectory) = "" Then
MkDir (Path)
MsgBox "Making Directory!"
'End If
Else
MsgBox "Dir Exists!"
End If
End Function