Save copy of workbook as new xlsm, runtime error 1004

Question

I'm trying to save a copy of the workbook as a new .xlsm file via the following code:

SaveAs FileName:=StrPadHoofdDocument & "\Docs\" & "\n\" & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I get the following error: "runtime error 1004: method of object SaveAs of object_Workbook failed" I've read a lot of other topics with the same kind of problem but I just can't quite solve it. Hope you guys can help!

full code:

Sub motivatieFormOpmaken()

Const StBestand = "Stambestand.xlsm"
Const motivatie = "Template motivatieformulier opstapregeling.xlsx"

    Dim wbMotivTemp As Workbook
    Dim wsMotiv As Worksheet
    Dim PathOnly, mot, FileOnly As String
    Dim StrPadSourcenaam As String

    Set wbMotivTemp = ThisWorkbook
    Set wsMotiv = ActiveSheet

    StrHoofdDocument = ActiveWorkbook.Name
    StrPadHoofdDocument = ActiveWorkbook.Path
    StrPadSourcenaam = StrPadHoofdDocument & "\" & c_SourceDump

    If Not FileThere(StrPadSourcenaam) Then
       MsgBox "Document " & StrPadSourcenaam & " is niet gevonden."
    Exit Sub
    End If

    Application.ScreenUpdating = False

    Workbooks.Open FileName:=StrPadSourcenaam
    Application.Run "Stambestand.xlsm!unhiderowsandcolumns"
    Worksheets("stambestand").Activate

    iLaatsteKolom = Worksheets("stambestand").Cells.SpecialCells(xlLastCell).Column
    iLaatsteRij = Worksheets("stambestand").Cells.SpecialCells(xlLastCell).row

    VulKolomNr
    If KolomControle = False Then Exit Sub

    Aantalregels = AantalZichtbareRows
        Dim rng As Range
        Dim row As Range
        Dim StrFileName As String
        'If Aantalregels > 1 Then
         Set rng = Selection.SpecialCells(xlCellTypeVisible)
         For Each row In rng.Rows
           iRijnummer = row.row
           If iRijnummer > 1 Then
              'Windows(c_SourceDump).Activate
              wsMotiv.Range("motiv_cid") = Cells(iRijnummer, iKolomnrCorpID).Text
              wsMotiv.Range("motiv_naam") = Cells(iRijnummer, iKolomnrNaam).Text
              wsMotiv.Range("motiv_ldg") = Cells(iRijnummer, iKolomnrHuidigeLeidingGevende).Text

              n = naamOpmaken

              wbMotivTemp.Activate

              ActiveWorkbook.SaveAs FileName:=StrPadHoofdDocument & "\Docs\" & "\n\" & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
            End If
         Next row

End Sub

Function naamOpmaken() As String
    Dim rng As Range
    Dim row As Range
    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    iRijnummer = rng.row
        If iRijnummer > 1 Then
            s = Cells(iRijnummer, iKolomnrNaam).Text
            Dim Position As Long, Length As Long
            Dim n As String
            Position = InStrRev(s, " ")
            Length = Len(s)
            n = Right(s, Length - Position)
        End If
    naamOpmaken = n
End Function

score 1 · Accepted Answer · answered Apr 03 '18 at 09:28

Change this part:

FileName:=StrPadHoofdDocument & "\Docs\" & "\n\" & ".xlsm",

With this:

FileName:=StrPadHoofdDocument & "\Docs\" & n & ".xlsm",

As you see, the problem is that you are using twice \\. Furthermore, n is a variable and it is passed as string. In future similar cases, print the problematic string and examine it closely, with code like this:

Debug.Print StrPadHoofdDocument & "\Docs\" & "\n\" & ".xlsm"
Debug.Print StrPadHoofdDocument & "\Docs\" & n & ".xlsm"

The errors would be visible then.

Top, thanks! Not at all something I took into consideration as a possible source of the error. Good tip for future reference as well. — MyName, Apr 03 '18 at 10:45

Save copy of workbook as new xlsm, runtime error 1004

1 Answers1