• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

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
0
Rossamino
Asked:
Rossamino
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now