Excel SaveAs Macro-Enabled Workbook (VBA)

[itsmrjamestoyou]

Hi All! New to the forum... reaching out as I've never had this issue before; moreover this code works in other spreadsheets I have made.

I have a button titled "Save & SaveAs New" that onClick, it saves the "Template" and Save's a new copy in the appropriate folder that is made with all other quote documents, and data.

All of the sudden when doing a spreadsheet revamp and adjustment into a new sheet with last years revisions, it no longer works and throws a error.

This is my code.

Sub Excel_SaveAsNew()

    Dim myFolder As String, myYear As String, myBid As String, myBidRev As String, myBidFile As String, myFolder1 As String, myFolder2 As String, myFolder3 As String, myFolder4 As String, myFolder5 As String, myFolder6 As String, Filename As String
       
        myFolder = Environ("USERPROFILE") & "\Desktop\Proposals\"
        myYear = ActiveSheet.Range("BB2").Value
        myBid = ActiveSheet.Range("BB3").Value
        myBidRev = ActiveSheet.Range("BB4").Value
        myBidType = ActiveSheet.Range("BB5").Value
        myBidFile = ActiveSheet.Range("BB6").Text
        myFolder1 = ActiveSheet.Range("BB7").Value 'Drawings
        myFolder2 = ActiveSheet.Range("BB8").Value 'Excel
        myFolder3 = ActiveSheet.Range("BB9").Value 'Emails
        myFolder4 = ActiveSheet.Range("BB10").Value 'Specifications
        myFolder5 = ActiveSheet.Range("BB11").Value 'Material Take Off
        myFolder6 = ActiveSheet.Range("BB12").Value 'Additional Information
       
        ActiveWorkbook.Save
   
    On Error Resume Next
   
        MkDir myFolder
        MkDir myFolder & "\" & myYear
        MkDir myFolder & "\" & myYear & "\" & myBid
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myBidType
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder1
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder2
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder3
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder4
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder5
        MkDir myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder6
       
    On Error GoTo 0
   
        Filename = myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder2 & "\" & myBidFile
       
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename, FileFormat:=52, CreateBackup:=False
            Application.DisplayAlerts = True
       
End Sub

The error in debugger says the error is Run-time error '1004' on the line containing "ActiveWorkbook.SaveAs Filename, FileFormat:=52, CreateBackup:=False"

Any thoughts on what i'm overlooking? MANY THANKS!!

 

[itsmrjamestoyou]

It's also worth noting the following:

1. There are no special characters in the filename, or folders being created, its text like "CIS-22-0506 Rev 1"
   1. Ive tried it with no spaces, and underscores with no change.
   2. This same code works in another variation on another spreadsheet with spaces and underscores and it works just fine.
2. Length of the resulting filename (including file path) is less than 120 characters.

This issue is confusing me because it works in other spreadsheets so I'm genuinely stumped.

 

[rlv01]

My guess would be something has gone wrong with your folder creation process, resulting in a non-existent path. Add this code to test.

    Filename = myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder2 & "\" & myBidFile
    
    Dim FName As String, FPath As String
    FName = Split(Filename, "\")(UBound(Split(Filename, "\")))
    FPath = Left(Filename, Len(Filename) - Len(FName))
    
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(FPath) Then
            MsgBox "Folder not found:" & vbCrLf & vbCrLf & FPath, vbOKOnly Or vbExclamation, Application.Name
            Exit Sub
        End If
    End With
        
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename, FileFormat:=52, CreateBackup:=False
    Application.DisplayAlerts = True

 

[itsmrjamestoyou]

My guess would be something has gone wrong with your folder creation process, resulting in a non-existent path. Add this code to test.

    Filename = myFolder & "\" & myYear & "\" & myBid & "\" & myBidRev & "\" & myFolder2 & "\" & myBidFile
   
    Dim FName As String, FPath As String
    FName = Split(Filename, "\")(UBound(Split(Filename, "\")))
    FPath = Left(Filename, Len(Filename) - Len(FName))
   
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(FPath) Then
            MsgBox "Folder not found:" & vbCrLf & vbCrLf & FPath, vbOKOnly Or vbExclamation, Application.Name
            Exit Sub
        End If
    End With
       
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename, FileFormat:=52, CreateBackup:=False
    Application.DisplayAlerts = True

Thank you. I added the code, and no error or message box appears; only the same error dialog as before. My conclusion, in this case, is that no news means the error check passed?

Also, in the folder creation process, it creates the folders (they are visible in the designated folder), and the PDF output function just above that works perfectly and places the PDF output into the appropriate folder. It's so strange why the Excel export isn't working in this one. I can't see any specific reason...

 

[itsmrjamestoyou]

Thank you. I added the code, and no error or message box appears; only the same error dialog as before. My conclusion, in this case, is that no news means the error check passed?

Also, in the folder creation process, it creates the folders (they are visible in the designated folder), and the PDF output function just above that works perfectly and places the PDF output into the appropriate folder. It's so strange why the Excel export isn't working in this one. I can't see any specific reason...

FYI, This is resolved. I did some mouse over analysis of the FPath and was able to determine that I had a "\" in the myFolder direction, and was adding another "\" in the compilation of the Filename. I removed the extra, and problem solved! THANK YOU for prompting me to investigate that method.