EDIT: NEW BEHAVIOR - Relocating the master XLSM to a new starting directory caused all issues to disappear. Placing the XLSM into a newly created (but same name) directory in its original location had maintained no issues as well.
I am using Excel/VBA 2010. My project requires me to copy a single sheet from a master XLSM workbook that has a sheet for each user. My approach was to iterate through each sheet of the master document, create a user-specific directory and workbook, copy that user's sheet over, and then save their file. I have managed to execute this code numerous times with some success, but there is a hiccup on a few cases. Sample code below
Sub createDirectories()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Dim ws As Worksheet
Dim NewBook As Workbook
For Each ws In ThisWorkbook.Worksheets
If StrComp(ws.Name, "only_nonUser_sheet", vbTextCompare) <> 0 Then
If Not fso.FolderExists(ThisWorkbook.Path & "\" & ws.Name) Then
Set oFile = fso.CreateFolder(ThisWorkbook.Path & "\" & ws.Name)
End If
If fso.FolderExists(ThisWorkbook.Path & "\" & ws.Name) Then
If Not fso.FileExists(ThisWorkbook.Path & "\" & ws.Name & "\" & ws.Name & ".xlsm") Then
Set NewBook = Workbooks.Add
ws.Copy Before:=NewBook.Sheets(1)
'Prevents sheet name from exceeding a 31 character limit
NewBook.Sheets(Sheets(ws.Name).Index).Name = Left(ws.Name, 20) & " " & Format(Now(), "mm-dd-yyyy")
'Source of failure
NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name & "\" & ws.Name, FileFormat:=52
NewBook.Close SaveChanges:=False
End If
End If
End If
Next ws
'Clean up file management objects
Set fso = Nothing
Set oFile = Nothing
End Sub
Folders are created without any obvious problems (despite a concern of mine below). NewBook.SaveAs produces run-time error '1004', Failed to SaveAs object "_workbook". Varying the arguments produces interesting results:
NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name, FileFormat:=52
Properly saves the file in the same directory as the master XLSM, but not in the desired user-specific folder. This is expected.
NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & "testFolder01" & "\" & "testFile02", FileFormat:=52
Properly saves testFile02 in the testFolder01 (provided I create the directory first).
NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & "testFolder01" & "\" & ws.Name, FileFormat:=52
Properly saves ws.Name.xlsm in a pre-created folder.
NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name & "\" & "testFile02", FileFormat:=52
Produces run-time error '1004' for the SaveAs failure.
My two hunches are: 1. Although the user-specific directories appear in File Explorer, they are not properly finished. My attempt to add oFile.Close within my fso.CreateFolder() line results in run-time error '483', and I am not sure how to address this. I have Microsoft Scripting Run-Time checked as a reference. 2. The SaveAs method does not allow for a variable path, but allows for a variable file name, which would be horrendously unfortunate and non-intuitive.
My overall path names do not exceed 150 characters total. My path/file names do not contain any illegal characters. My workbook path is on a mapped drive, but this poses no problems for my variations of my SaveAs line. My code is verbose regarding which workbook I am attempting to save. Can my code be modified to complete my task? If not, is there an alternative method of creating and saving XLSM files that allows for variable path/filenames?
