At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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.If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.