Prompt for a file name using VBA

Function prompt4FileName(Optional sFolder As String = vbNullString, Optional sDefaultName As String = "Untitled") As String
    Dim sMacScript As String
    Dim sFileName As String

    If sFolder = vbNullString Then 'Get user document folder
        sFolder = userDocumentFolder()
    End If
    If isMac Then
        sMacScript = "set sFile to (choose file name with prompt ""Name this file"" default name """ & _
        sDefaultName & vbLf & _
        """ default location the path to documents folder)" & vbLf & _
        "return sFile"
        sFileName = MacScript(sMacScript)
    Else
        sFileName = Application.GetSaveAsFilename(sDefaultName, "Excel Files,*.xls,Excel macro enable files,*.xlsm,All Files,*.*", , "Name this file", "Save as")    End If
    prompt4FileName = sFileName
End Function
Advertisements

One Comment on “Prompt for a file name using VBA”

  1. PAtrick says:

    Where does tho get placed instead of the set file path in my module?

    Sub Macro6()

    ‘ Macro6 Macro


    With ActiveSheet.QueryTables.Add(Connection:= _
    “TEXT;fusion:Users:precision:Desktop:csv.csv”, Destination:=Range(“A2” _
    ))
    .Name = “csv”
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlMacintosh
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 2)
    .Refresh BackgroundQuery:=False
    .UseListObject = False
    End With
    ActiveWindow.SmallScroll Down:=-38
    End Sub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s