Sep 08 2023 12:30 PM
Sep 08 2023 12:30 PM
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.
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
Sep 08 2023 01:05 PM
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
Sep 08 2023 01:57 PM
Temporarily change the line
On Error GoTo errHandler
On Error GoTo 0
When the error occurs, click Debug in the error message box.
Which line is highlighted?
Sep 08 2023 02:03 PM - edited Sep 08 2023 02:05 PM
wsPaste.Name = strFile
Do I have it in the wrong order? I'm trying to set a default filename for the user who wants to save a copy (which seems to be working as intended in the Save As dialogue box).
Sep 08 2023 02:37 PMSolution
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
and press Enter.
Sep 08 2023 03:33 PM
Sep 08 2023 05:04 PM
@Hans Vogelaar Worked! It's 31 including the .xlsx, I think. Thanks for your help, Hans!