CODEWITHSUNDEEP
excelvba
aitpeter
aitpeter

Reputation: 3

Initial filename not shown in saveas dialogbox

I'm trying to show a filename suggestion with the GetSaveAsFilename method but this initialfilename variable isn't shown.

Public Sub SaveWorkBooks()

Dim strWbName As String
Dim strFileName As String

        For Each w In Workbooks
            strWbName = Trim(w.Name)
            If w.Name <> ThisWorkbook.Name Then
                strFileName = Application.GetSaveAsFilename(strWbName, fileFilter:="Microsoft Excel Files (*.xlsx), *.xlsx")
                If strFileName <> False Then
                    w.SaveAs Filename:=strFileName
                End If
            End If
        Next w
End Sub

I expect that the value of strWbName is shown as the suggested filename in the textbox on the save as dialog. But now no suggestion is shown.

What am I doing wrong?

Upvotes: 0

Views: 781

Answers (1)

Storax
Storax

Reputation: 12167

Add Microsoft Scripting Runtime to your references (Extra/Reference) and modify your code like that

Public Sub SaveWorkBooks()

Dim strWbName As String
Dim strFileName As String
Dim fso As New FileSystemObject

        For Each w In Workbooks
            'strWbName = Trim(w.Name)
            strWbName = fso.GetBaseName(Trim(w.Name))
            If w.Name <> ThisWorkbook.Name Then
                strFileName = Application.GetSaveAsFilename(strWbName, fileFilter:="Microsoft Excel Files (*.xlsx), *.xlsx")
                If strFileName <> False Then
                    w.SaveAs Filename:=strFileName
                End If
            End If
        Next w
End Sub

Upvotes: 1

Related Questions