can't find how to create a browse macro

**curbster** · 08-28-2009, 12:52 PM

I have looked in books and online and can't seem to locate how to create a macro that opens a browse function. From Excel I would like to click a button to start a macro and have that macro prompt me where to browse for the file. There is a lot of other code that will go after this step (which I already have), so once I click to "open" a file I want the macro to continue.

I appreciate your help in advance!

**royUK** · 08-28-2009, 01:10 PM

Is this what you mean?

Option Explicit



Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
    On Error GoTo err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

    Workbooks.Open Filename:=sWb
    Exit Sub
err_handler:
    MsgBox "No selection made"
End Sub

Or

Sub get_file_name()
    Dim f      As FileDialog
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Title = "Select a file..."
        .InitialFileName = "C:\"
        .ButtonName = "Select"
        .Show
    End With
    Set f = Nothing
End Sub

**Palmetto** · 08-28-2009, 01:13 PM

This will activate the File Open dialog. Is this what you want

Application.GetOpenFilename

Edit: Posted just behind RoyUK (better/full solution).

**curbster** · 08-28-2009, 01:33 PM

It looks like all of those get me to the same point. The file I open is a .txt file and contains a total of 39 columns. The # of rows vary depending on the file I recieve. The program I'm working on converts this text file to an excel file. The opening code looks like this:

    Dim f      As FileDialog
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Title = "Select a file..."
        .InitialFileName = "P:\"
        .ButtonName = "Select"
        .Show
    End With
    Set f = Nothing
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 3), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 3), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 3), _
        Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1)), _
        TrailingMinusNumbers:=True

I get the debug error where it starts at "origin." Any ideas how to mesh the code?

thanks!

P.S. in case you're wonding what I started with I'll include it below. The code below works but not for what I need. It opens the same file every time instead of asking me to search for the file.

    Workbooks.OpenText Filename:="P:\Review\Macro Training\Drop Folders\20090531", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 3), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 3), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 3), _
        Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1)), _
        TrailingMinusNumbers:=True

**Leith Ross** · 08-28-2009, 02:12 PM

Hello curbster,

The setting for the named argument Origin can be omitted. 437 This indicates the file was was saved in DOS-US font. Omitting the entry lets your system choose the font the closest to the original.

    Dim f      As FileDialog
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Title = "Select a file..."
        .InitialFileName = "P:\"
        .ButtonName = "Select"
        .Show
    End With
    Set f = Nothing
        StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 3), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 3), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 3), _
        Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1)), _
        TrailingMinusNumbers:=True

**curbster** · 08-28-2009, 02:25 PM

Hi Leith,

that code in the editor is still red. Something is not meshing together

**curbster** · 08-28-2009, 02:44 PM

Ok, with a little trial and error I think I got it. This seems to be working. I had to take out the "on error" and the "exit sub" and manipulate the "workbooks.open" to be "workbooks.opentext". See below:

Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
 
    ' Set up list of file filters
    sFil = "All Files (*.*),*.*"
    ' Display *.* by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

    Workbooks.OpenText Filename:=sWb, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 3), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 3), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 3), _
        Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1)), _
        TrailingMinusNumbers:=True

Thanks for all of your help!