VBA Save as custom file

Question

I've been trying to save some excel data as a custom text file format. By following this post I didn't got too far. Excel throws Run-time error '13': Type mismatch. Any ideas why this happens?

The error happens on third line:

Set dialog = Application.GetSaveAsFilename(FileFilter:="Custom file, *.custom")

Sub SaveCustomData()
    Dim dialog As Variant
    Set dialog = Application.GetSaveAsFilename(FileFilter:="Custom file, *.custom")
    ' !!! The code runs this far !!!

    With dialog
        .InitialFileName = Application.ActiveWorkbook.Path & Application.PathSeparator
        .AllowMultiSelect = False

        Dim file As String
        Dim fso As FileSystemObject
        Set fso = New FileSystemObject
        Dim fileStream As TextStream

        If .Show = True Then
            file = .SelectedItems(1)

            Set fileStream = fso.CreateTextFile(dialog, ForWriting, True)

            ' Writing data to file here

            fileStream.Close

        End If
    End With
End Sub

I'm pretty sure the code is working as intended. The Application.GetSaveAsFilename method returns as False if it's canceled, as per MSDN - hence producing the Type mismatch error (you can't open a boolean). Just create a simple if condition that handles this behaviour — Samuel Hulla
– Samuel Hulla, Commented Aug 13, 2018 at 8:32

FunThomas · Accepted Answer · 2018-08-13 08:28:43Z

The Application.GetSaveAsFilename returns the name of the selected file (or False if user cancelled the dialog). You cannot (and you don't want) to assign this to your dialog variable

The question of the post you are referencing uses Application.FileDialog(3) (the 3 should be replaced by the constant msoFileDialogFilePicker), this returns a Dialog - this is how the rest of your code is build.

However, in the answer of the post, Application.GetSaveAsFilename is used. As this command is using the folder of the actual sheet as starting folder and doesn't allow multi select, your code can be reduced to

Sub SaveCustomData()
    Dim fileName As Variant
    fileName  = Application.GetSaveAsFilename(FileFilter:="Custom file, *.custom")
    if fileName = False then 
        MsgBox "No filename selected, data not saved"
        exit sub
    end if

    Dim file As String
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim fileStream As TextStream

    Set fileStream = fso.CreateTextFile(fileName, ForWriting, True)
    ...
End Sub

Collectives™ on Stack Overflow

VBA Save as custom file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related