CODEWITHSUNDEEP
vbaexcelfilesave-as
user6236754
user6236754

Reputation:

VBA Save as custom file

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

Upvotes: 0

Views: 1414

Answers (1)

FunThomas
FunThomas

Reputation: 29146

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

Upvotes: 2

Related Questions