Save As not working in Excel Macro

sam nick 286 Reputation points
2022-04-05T13:24:28.47+00:00

Hi,
I have a button in excel which is supposed to refresh the pivot tables, delete the connections and then allow the user to save the file as an excel file. But using the below code, i get an error and oddly, the save as dialog box > i enter the name of the file > i get a pop up of "save as " with the path and filename > and then save as dialog box opens again but fails with the error 'This extension cannot be used with the selected filter type."

`Sub RectangleRoundedCorners2_Click()
Dim Sheet As Worksheet, Pivot As PivotTable, XQuery As Object, XConnection As Object, selection As Variant, fileSaveName As Variant

For Each Sheet In ActiveWorkbook.Worksheets
    For Each Pivot In Sheet.PivotTables
        Pivot.RefreshTable
        Pivot.Update
    Next
Next

For Each XQuery In ActiveWorkbook.Queries
    If XQuery.Name <> "RandomQueryName" Then XQuery.Delete
Next XQuery

 For Each XConnection In ActiveWorkbook.Connections
    If XConnection.Name <> "RandomConnectionName" Then XConnection.Delete
Next XConnection

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Workbook (*.xlsx), *.xlsx")
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If

ActiveWorkbook.SaveAs FileName:=Application.GetSaveAsFilename( _
fileFilter:="Excel Workbook (*.xlsx), *.xlsx")`

Please advise what am i doing wrong

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,720 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
{count} votes