CODEWITHSUNDEEP
excelvbacompiler-errorssave
Dave Friesen
Dave Friesen

Reputation: 51

Run-time error '1004': Method SaveAs of object _Workbook failed

I'm writing a program that saves a workbook to a specific folder based on some cell values in the workbook. Everything works as it should until I get to the ActiveWorkbook.SaveAs line, where I get a Run-time error 1004.

`Sub Tester()

Dim qNum, fldr As String
Dim custName As String
Dim myFileName As String
Dim completePath As String
Dim division As String

custName = Range("B12").Value
qNum = Range("B19").Value

If custName = "CNUL - Albian" Then
    custName = "CNRL"
    division = "Albian"
End If
If custName = "CNUL - Horizon" Then
    custName = "CNRL"
    division = "Horizon"
End If
If custName = "CNRL - Albian" Then
    custName = "CNRL"
    division = "Albian"
End If
If custName = "CNRL - Horizon" Then
    custName = "CNRL"
    division = "Horizon"
End If

If custName = "CNRL" Then
    fldr = GetMatchingPathCNRL(qNum, custName, division) '<< find the        matching folder
    If Len(fldr) > 0 Then
        Debug.Print "Found folder for customer=" & custName & _
                        ", Qnum=" & qNum & vbLf & fldr
            '...use this path

    Else
        MsgBox "No matching folder!", vbExclamation
    End If
Else
    fldr = GetMatchingPath(qNum, custName) '<< find the matching folder
    If Len(fldr) > 0 Then
        Debug.Print "Found folder for customer=" & custName & _
                    ", Qnum=" & qNum & vbLf & fldr
        '...use this path

    Else
        MsgBox "No matching folder!", vbExclamation
    End If
End If


myFileName = custName & " " & qNum & " " & "MTO Rev A"
completePath = fldr & "\" & myFileName

ActiveWorkbook.SaveAs Filename:=completePath
End Sub

Function GetMatchingPath(qNum, custName) As String
Const ROOT As String = "P:\MyCompany\" '<< adjust to suit
Dim f
f = Dir(ROOT & custName & "\*" & qNum & "*", vbDirectory)
GetMatchingPath = ROOT & custName & "\" & f
End Function


Function GetMatchingPathCNRL(qNum, custName, division) As String
Const ROOT As String = "P:\MyCompany\" '<< adjust to suit
Dim f
f = Dir(ROOT & custName & "\" & division & "\*" & qNum & "*", vbDirectory)
GetMatchingPathCNRL = ROOT & custName & "\" & f
End Function

` The idea is that if the customer is CNRL there is an extra layer of folders to go through. The 4 'if' statements at the beginning are just to navigate a few different ways people may enter information into the spreadsheet.

I am always getting a 1004 error when it gets to the save file line, but the variables are all storing the correct names and folder paths. Any ideas?

Upvotes: 2

Views: 2071

Answers (1)

Tim Williams
Tim Williams

Reputation: 166850

There's no division in the return path from GetMatchingPathCNRL:

f = Dir(ROOT & custName & "\" & division & "\*" & qNum & "*", vbDirectory)
GetMatchingPathCNRL = ROOT & custName & "\" & f

should be 

GetMatchingPathCNRL = ROOT & custName & "\" & division & "\" & f

EDIT: I think it's preferable to centralize the "path" logic into one place, instead of having two different functions where you have to figure out which one to call...

Function GetMatchingPath(qNum, custName, division) As String
    Const ROOT As String = "P:\MyCompany\" '<< adjust to suit
    Dim fPath As String, f

    fPath = ROOT & custName & "\"
    If custName = "CNRL" Then fPath = fPath & division & "\"

    f = Dir(fPath & "*" & qNum & "*", vbDirectory)
    GetMatchingPath = IIf(f <> "", fPath & f, "")
End Function

Upvotes: 2

Related Questions