Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
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
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, _
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
Note how I used a With block to avoid .Select statements. This will speed up your macro and make it easier to maintain.Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.