Solved

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

Posted on 2013-06-19

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