Using VBA FileDialogFilePicker to open fixed width files for macro in Excel

I get these fixed-width files each week in the same format that I need to analyze in Excel.  I've written a macro that does what I need it to do, but it only works for the singular filename that I have to manually go in and edit.  I'd like to be able to use FileDialog's FilePicker to select a file.

The macro looks like this, basically:

Sub OpenFixedWidthFile()
    Workbooks.OpenText Filename:= _
        "B:\ORDERS\electronic submission\VMG061913.dat", Origin:=437, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(5, _
        3), Array(11, 9), Array(19, 1), Array(20, 9), Array(21, 1), Array(27, 1), Array(30, 9), _
        Array(53, 1), Array(59, 9), Array(60, 1), Array(63, 1), Array(70, 1), Array(151, 1), Array( _
        178, 1), Array(205, 1), Array(225, 1), Array(227, 2), Array(232, 9), Array(262, 1), Array( _
        265, 1), Array(272, 9), Array(294, 1), Array(375, 1), Array(402, 1), Array(429, 1), Array( _
        449, 1), Array(451, 2), Array(456, 9), Array(491, 1), Array(494, 9), Array(603, 1), Array( _
        653, 9), Array(654, 2), Array(660, 9)), TrailingMinusNumbers:=True


    ActiveCell.FormulaR1C1 = "Field1"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Field2"
    Range("A1:B1").Select
    Selection.AutoFill Destination:=Range("A1:Y1"), Type:=xlFillDefault

    ' etc

end sub
RossaminoAsked:
Who is Participating?
 
als315Commented:
You can use this function:
Function GetFile(Description As String, Mask As String) As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Filters.Add Description, Mask
fd.AllowMultiSelect = False
If fd.Show = True Then
    GetFile = fd.SelectedItems(1)
End If
End Function

Open in new window

Add it to module and changee first strings of your macro to:
Sub OpenFixedWidthFile()
Dim fname as string
fname = GetFile("All .dat files","*.dat")
If fname = "" then
    msgbox "Nothing selected"
    exit sub
end if
    Workbooks.OpenText Filename:= _
        fname, Origin:=437, _

Open in new window

0
 
byundtCommented:
You might consider using the GetOpenFilename method to display a file browser and let the user pick a file. Since it doesn't actually open the file, you can then use your existing code to perform the file opening.
Sub OpenFixedWidthFile()
Dim flPath As String
flPath = Application.GetOpenFilename("Data files (*.dat),*.dat", Title:= _
    "Please pick the desired file to process")
If flPath = "False" Then Exit Sub

Workbooks.OpenText Filename:=flPath, Origin:=437, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(5, _
    3), Array(11, 9), Array(19, 1), Array(20, 9), Array(21, 1), Array(27, 1), Array(30, 9), _
    Array(53, 1), Array(59, 9), Array(60, 1), Array(63, 1), Array(70, 1), Array(151, 1), Array( _
    178, 1), Array(205, 1), Array(225, 1), Array(227, 2), Array(232, 9), Array(262, 1), Array( _
    265, 1), Array(272, 9), Array(294, 1), Array(375, 1), Array(402, 1), Array(429, 1), Array( _
    449, 1), Array(451, 2), Array(456, 9), Array(491, 1), Array(494, 9), Array(603, 1), Array( _
    653, 9), Array(654, 2), Array(660, 9)), TrailingMinusNumbers:=True


With Range("A1:B1")
    .Value = Array("Field1", "Field2")
    .AutoFill Destination:=Range("A1:Y1"), Type:=xlFillDefault
End With
' etc

End Sub

Open in new window

Note how I used a With block to avoid .Select statements. This will speed up your macro and make it easier to maintain.

Brad
0
 
Zack BarresseCEOCommented:
You don't need a With block to avoid selecting...  ;)

Zack
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
byundtCommented:
Zack,
Have you found a good way of justifying an alternative to .Select and .Activate?

I'd like to be suggesting something that the Asker can easily extend to the rest of their macro. All too often, the Asker will post several hundred lines of recorded macro and ask for an update to one piece of it. I'll fix a piece of it and suggest that they try fixing the rest.

For the past few months, I've been espousing With blocks whenever I need to refer to a range more than once.

Brad
0
 
Zack BarresseCEOCommented:
I hear ya. I was partly ribbing ya, and part disdain for With blocks. That's all. :)
0
 
RossaminoAuthor Commented:
That's perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.