Solved

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

Posted on 2013-06-19
6
746 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

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 81

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 81

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 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