Solved

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

Posted on 2013-06-19
6
702 Views
Last Modified: 2013-06-21
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
Comment
Question by:Rossamino
6 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39260396
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
 
LVL 80

Expert Comment

by:byundt
ID: 39260722
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39261266
You don't need a With block to avoid selecting...  ;)

Zack
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 80

Expert Comment

by:byundt
ID: 39261285
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39261304
I hear ya. I was partly ribbing ya, and part disdain for With blocks. That's all. :)
0
 

Author Closing Comment

by:Rossamino
ID: 39266469
That's perfect.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now