Solved

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

Posted on 2013-06-19
6
713 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

932 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

11 Experts available now in Live!

Get 1:1 Help Now