Save As not working in Excel Macro

sam nick 346 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

Microsoft 365 and Office Development Other
Microsoft 365 and Office Excel For business Windows
Developer technologies Visual Basic for Applications
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.