Importing a text file with VBA

[darkwolf45]

I am actually redoing this from a file I did a couple years ago. I recorded the macro, gave the file to a coworker who needed it and thought I was done with it. Well, he actually lost the file and no one has it anymore. SO, i recorded what I could again, and at a sticking point, being that I do not know VBA. Here si the code:

Sub formatDHSdata()
'
' formatDHSdata Macro
' Macro recorded 4/7/2011 by Brian Rule
'
'
Workbooks.OpenText Filename:= _
"S:\SO\FIN\REIMB\Data Analysis\MA DSH Audit\2008\FFS fils received from DHS\Abbott\1760446256_FFSDATA_I_F_0812_20110324_102939_DH.dat" _
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(2, 1), Array(11, 1), Array(21, 1), Array(25, 1), Array(26, 1), Array(28, _
1), Array(30, 1), Array(38, 1), Array(55, 1), Array(67, 1), Array(68, 1), Array(85, 1), _
Array(95, 1), Array(105, 1), Array(115, 1), Array(135, 1), Array(141, 1), Array(154, 1), _
Array(167, 1), Array(176, 1), Array(189, 1), Array(200, 1), Array(211, 1), Array(224, 1)) _
, TrailingMinusNumbers:=True
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.LargeScroll ToRight:=-1
End Sub


The problem is that there are over 140 files that I need to open and modify, not just the single one at this location. How do I change the command to simply take me to the open dialog box to select the file I want?
Thanks for any isight you can offer,
Brian

 

[AlphaFrog]

Try something like this...

Sub formatDHSdata()

Dim strFileName As String

' Default drive
ChDrive "S:"

' Default Directory
ChDir "S:\SO\FIN\REIMB\Data Analysis\MA DSH Audit\2008\FFS fils received from DHS\Abbott\"

' Prompt for file
strFileName = Application.GetOpenFilename(FileFilter:="Data Files ,*.dat," & _
                                                      "Text Files ,*.txt," & _
                                                      "All Files ,*.*", _
                                          FilterIndex:=1, _
                                          Title:="Open Data File", _
                                          MultiSelect:=False)

If strFileName = "False" Then Exit Sub  ' if User canceled then exit

' Open selected file
Workbooks.OpenText Filename:=strFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, TrailingMinusNumbers:=True


End Sub

 

[darkwolf45]

Thanks Alpha Frog! This really came in handy!
Brian