Forum Discussion

TheNeedsOfMany's avatar
TheNeedsOfMany
Copper Contributor
Sep 08, 2023
Solved

Error when saving macro workbook as non-macro VBA

I'm trying to create a button on a sheet that allows for a non-macro enabled copy of a sheet to be saved via VBA. I feel like I am 90% of the way there, and have it set up so that the copy is being displayed correctly but it's throwing up an error when I try to save it (save dialogue box to select a path shows up and everything, but then an error dialog stating "Excel file could not be saved" displays.

 

I'm relatively new to macros and VBA so I'm sure I'm missing something simple, but any help is appreciated.

 

Code:

Sub SaveXLSXCopy()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim strVer As String
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd")
strVer = ThisWorkbook.Sheets("FFA Policy").Range("E46").Value

'set datestamp and version number for printing
[B6] = "Price list saved on " & strTime & " | ver. " & strVer

'hide "View FFA Policy" link for printing
Range("7:7").EntireRow.Hidden = True

'hide Save As icons
ActiveSheet.Shapes("SaveAsExcel").Visible = False
ActiveSheet.Shapes("SaveAsPDF").Visible = False

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = "NDL Price List - " & ActiveSheet.Range("C3") & " " & strTime & ".xlsx"
strFile = Replace(strFile, "Plumbing | ", "")
strFile = Replace(strFile, "HVAC-R | ", "")
strFile = Replace(strFile, "Universal | ", "")
strFile = Replace(strFile, " | ", "_")
strPathFile = strPath & strFile

'select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="Excel Files (*.xlsx), *.xlsx", _
        Title:="Select Folder and FileName to save")

'save as xlsx if a folder was selected
If myFile <> "False" Then
    Application.DisplayAlerts = False

'copy-paste values and formatting to new workbook
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String

Set wsCopy = ThisWorkbook.Worksheets("Price List")
Set wb = Workbooks.Add
Set wsPaste = wb.Sheets(1)

    wsCopy.UsedRange.Copy
    wsPaste.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wsPaste.Range("A1").PasteSpecial xlPasteFormats
    wsPaste.Range("A1").PasteSpecial xlPasteColumnWidths
    ActiveSheet.Name = "NDL Pricing Snapshot"
    Rows(7).EntireRow.Delete

'save the file
wsPaste.Name = strFile
wb.SaveAs Filename:=strFile, FileFormat:=15

'confirmation message with file info
    MsgBox "Price list has been saved: " _
      & vbCrLf _
      & myFile
Application.DisplayAlerts = True

End If

exitHandler:
'Return to original workbook before exithandlder
wsCopy.Activate
Application.CutCopyMode = False
'unhide "View FFA Policy" link after printing
ActiveSheet.Range("7:7").EntireRow.Hidden = False

'remove printing datestamp
ActiveSheet.Range("B6") = ""

'unhide Save As icons
ActiveSheet.Shapes("SaveAsExcel").Visible = True
ActiveSheet.Shapes("SaveAsPDF").Visible = True

    Exit Sub
errHandler:
    MsgBox "Could not create Excel file"
    Resume exitHandler

End Sub

 

  • HansVogelaar's avatar
    HansVogelaar
    Sep 08, 2023

    TheNeedsOfMany 

    It might be that strFile is too long (the maximum length of a sheet name is 31), or that it contains characters that aren't allowed in a sheet name: \ , / , * , ? , : , [ , ].

    When the code is paused, press Ctrl+G to activate the Immediate window, type

     

    ? strFile

     

    and press Enter.

7 Replies

  • TheNeedsOfMany 

    strFile is a filename without path, ending in .xlsx

    But the line

     

    wb.SaveAs Filename:=strFile, FileFormat:=15

     

    has file format 15; this stands for .wk3, a Lotus 1-2-3 file format! I think you want

     

    wb.SaveAs Filename:=myFile, FileFormat:=xlOpenXMLWorkbook 

    • TheNeedsOfMany's avatar
      TheNeedsOfMany
      Copper Contributor
      Good catch! Made the changes but am still getting the error. 😞
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        TheNeedsOfMany 

        Temporarily change the line

         

        On Error GoTo errHandler

         

        to

         

        On Error GoTo 0

         

        When the error occurs, click Debug in the error message box.

        Which line is highlighted?

Resources