SaveAs Runtime Error 1004 issue

[Jgordon]

Hi Guys,

I'm sometimes getting a runtime error on the "ActiveWorkbook.SaveAs" line
Other times it works like a dream. I'm confused...

Sub TestForDirAndSave()
  If MsgBox("Are you sure?", vbOKCancel, "Save?") = vbCancel Then Exit Sub
    
    
    Dim strDir As String
    strDir = Sheets("Todoist Checklist").Range("b48")
    strDir2 = Sheets("Todoist Checklist").Range("c48")
    strDir3 = Sheets("Todoist Checklist").Range("d48")
    
    'Makes sure there are values in boxes so folders with value 0 are not created
     Do Until False
   If Sheet11.Range("d7") = "" Then
        MsgBox "You must add a Referral Facility"
        Exit Sub
    Else
        If Sheet11.Range("d24") = "" Then
        MsgBox "You must add a LOB"
        Exit Sub
         Else
            If Sheet11.Range("D6") = "" Then
        MsgBox "You must add a Client Name"
        Exit Sub
            Else
            
            Exit Do
            End If
        End If
    End If
    Loop
    
    'Checks if folder exists.  if it doesn't it creates it.
   Do Until False
   If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
    Else
        If Dir(strDir2, vbDirectory) = "" Then
        MkDir strDir2
         Else
            If Dir(strDir3, vbDirectory) = "" Then
            MkDir strDir3
            Else
            MsgBox "Client Folder Created"
            Exit Do
            End If
        End If
    End If
    Loop
    'Saves the file into the created folder.
        ActiveWorkbook.SaveAs Sheet11.Range("C1") & "\" & Sheet11.Range("D6") & ", " & Sheet11.Range("D5") & "- PreQuote" & ".xlsm"
Sheet11.Range("a1").Value = "Saved"
End Sub

 

[Joe4]

Welcome to the Board!

You probably don't have a valid path of file name.
I recommend building the file name in a variable and add error handling, i.e.

Sub TestForDirAndSave()
  If MsgBox("Are you sure?", vbOKCancel, "Save?") = vbCancel Then Exit Sub
    
    
    Dim strDir As String
    strDir = Sheets("Todoist Checklist").Range("b48")
    strDir2 = Sheets("Todoist Checklist").Range("c48")
    strDir3 = Sheets("Todoist Checklist").Range("d48")
    
    'Makes sure there are values in boxes so folders with value 0 are not created
     Do Until False
   If Sheet11.Range("d7") = "" Then
        MsgBox "You must add a Referral Facility"
        Exit Sub
    Else
        If Sheet11.Range("d24") = "" Then
        MsgBox "You must add a LOB"
        Exit Sub
         Else
            If Sheet11.Range("D6") = "" Then
        MsgBox "You must add a Client Name"
        Exit Sub
            Else
            
            Exit Do
            End If
        End If
    End If
    Loop
    
    'Checks if folder exists.  if it doesn't it creates it.
   Do Until False
   If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
    Else
        If Dir(strDir2, vbDirectory) = "" Then
        MkDir strDir2
         Else
            If Dir(strDir3, vbDirectory) = "" Then
            MkDir strDir3
            Else
            MsgBox "Client Folder Created"
            Exit Do
            End If
        End If
    End If
    Loop
    
    'Saves the file into the created folder.
[COLOR=#ff0000]     Dim fname As String[/COLOR]
[COLOR=#ff0000]     fname = Sheet11.Range("C1") & "\" & Sheet11.Range("D6") & ", " & Sheet11.Range("D5") & "- PreQuote" & ".xlsm"[/COLOR]
[COLOR=#ff0000]     On Error GoTo err_chk[/COLOR]
[COLOR=#ff0000]     ActiveWorkbook.SaveAs fname[/COLOR]
[COLOR=#ff0000]     On Error GoTo 0[/COLOR]
[COLOR=#ff0000]     Sheet11.Range("a1").Value = "Saved"[/COLOR]
[COLOR=#ff0000]     Exit Sub[/COLOR]

[COLOR=#ff0000]err_check:[/COLOR]
[COLOR=#ff0000]    MsgBox "Cannot save file to name: " & vbCrLf & fname, vbOKOnly, "ERROR!!!"[/COLOR]
    
End Sub

 

[JLGWhiz]

Or, Probably need the keyword Value

ActiveWorkbook.SaveAs Sheet11.Range("C1").[COLOR=#FF0000]Value[/COLOR] & "\" & Sheet11.Range("D6").[COLOR=#FF0000]Value[/COLOR] & ", " & Sheet11.Range("D5").[COLOR=#FF0000]Value[/COLOR] & "- PreQuote" & ".xlsm"

 

[Jgordon]

Awesome! Thanks for this!

I just ran it and it worked. I had to correct the err_check to err_chk but after that it worked.

I'll update if i run into trouble.

 

[Joe4]

You are welcome!

I just ran it and it worked. I had to correct the err_check to err_chk but after that it worked.

Yes, typo on my part. Sorry about that.

Hopefully, the error becomes evident when it displays the calculated value.

 

[Jgordon]

Ok. I'll let you know how it goes.