Excel 2019 VBA SaveAs generates runtime 1004

Question

I want to save an Excel file to XLMS.

In office 2010 - 2016 this code works but in 2019 I get runtime error 1004.

I searched the internet for a solution.

I get the error on the SaveAs line.

Dim Wb As Workbook
Dim NewFileName As String
Dim NewFileFilter As String
Dim myTitle As String
Dim FileSaveName As Variant
Dim NewFileFormat As Long

Set Wb = ThisWorkbook

NewFileName = "Test.xlsm"
NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
NewFileFormat = 52

myTitle = "Navigate to the required folder"

FileSaveName = Application.GetSaveAsFilename _
         (InitialFileName:=NewFileName, _
          filefilter:=NewFileFilter, _
          Title:=myTitle)

If Not FileSaveName = False Then
    Wb.SaveAs FileName:=FileSaveName, _
                 FileFormat:=NewFileFormat
    'Call PDF_PRINT

Else

    'MsgBox "File NOT Saved. User cancelled the Save."
    X = MsgBox("ExcelFile NOT Saved.User cancelled the Save." & vbCrLf & _
      "Click CANCEL to abort pdf-save function as well." & vbCrLf & _
      "Or click OK to proceed with the pdf-saving function.", vbOKCancel, _
      "EXCEL SAVE FUNCTION")

    If X = vbOK Then PDF_PRINT Else Exit Sub

End If

On what line do you get the error? – GMalc Jan 15 '20 at 13:58 — GMalc, Jan 15 '20 at 13:58
Always on the line Wb.SaveAs FileName:=FileSaveName, _ – Jorgen69 Jan 16 '20 at 16:50 — Jorgen69, Jan 16 '20 at 16:50

score 0 · Answer 1 · answered Jan 16 '20 at 08:40

Please try this, it is working to save a PDF for me.

Sub MacroTest()

Dim Wb As Workbook
Dim NewFileName As String
Dim NewFileFilter As String
Dim myTitle As String
Dim FileSaveName As Variant
Dim NewFileFormat As Long

Set Wb = ThisWorkbook

NewFileName = "Test.xlsm"
NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
NewFileFormat = 52

myTitle = "Navigate to the required folder"

FileSaveName = Application.GetSaveAsFilename _
         (InitialFileName:=NewFileName, _
          filefilter:=NewFileFilter, _
          Title:=myTitle)

If Not FileSaveName = False Then
   Wb.SaveAs Filename:=FileSaveName, _
                 FileFormat:=NewFileFormat
   Call PDF_Print

Else

   'MsgBox "File NOT Saved. User cancelled the Save."
   X = MsgBox("ExcelFile NOT Saved.User cancelled the Save." & vbCrLf & "Click CANCEL to abort pdf-save function as well." & vbCrLf & "Or click OK to proceed with the pdf-saving function.", vbOKCancel, "EXCEL SAVE FUNCTION")

End If

End Sub

Sub PDF_Print()

    Dim strPth As String, strFile As String

    strPth = "C:\"
    strFile = "Test.pdf"

    'If X = vbOK Then PrintPDF Else Exit Sub

  If X = vbOK Then PrintPDF(0, strPth & strFile) Then
  MsgBox "Printing failed"

End If

End Sub

It's not the PDF creation i have problem with. It's the save to XLSM that is not working. The code is a part of a bigger script and later in it i save to PDF also. But this is not the problem. — Jorgen69, Jan 16 '20 at 16:51

Excel 2019 VBA SaveAs generates runtime 1004

1 Answers1