Edit Macro script

[BadRasta]

Hello All,
I'm putting a text file into excel and formatting it with text to column.
Another program spits out the text file, each team member must enter their
own, so they run the macro on their local machine. Is there a way for the
macro to choose only the text file and ignore all other files in the folder.
each txt file has a different alfa numeric number,
for example "ME_TOO123"
I recorded a Macro and now I'm trying to edit the following script...
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\CHANGED\My Documents\ME_TOO123.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=True, Other:=True, OtherChar:=":", FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1)), TrailingMinusNumbers:=True

Thanks in advance...
BadRasta

 

[OssieMac]

Hi,

Not sure if you want to do it this way but you could use the FilePicker
dialog box and set the filter to text files and allow the user to pick the
required file.

I have included commented out code for extracting the filename only from the
Path and filename just in case you want it but it is not required in this
case because you need both path and filename.

Note that I have used space and underscores (which are line breaks in
otherwise single lines of code) so that the code will not break at incorrect
places in this post and can be copied directly into your VBA editor without
having to fix broken line problems.

Sub OpenTxtFile()
Dim myTitle As String
Dim sFile As String 'Path and file name
Dim ShortName As String 'FileName only

myTitle = "Select the required text file"
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Text files", "*.txt", 1
.Title = myTitle
If .Show = False Then
Exit Sub
End If
sFile = .SelectedItems(1)
End With

'Following line of code extracts the
'filename only if required
'ShortName = Right(sFile, Len(sFile) _
- InStrRev(sFile, "\"))

Workbooks.OpenText Filename:= _
sFile, _
Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=True, _
Comma:=False, _
Space:=True, _
Other:=True, _
OtherChar:=":", _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1), _
Array(9, 1)), _
TrailingMinusNumbers:=True

End Sub

 

[BadRasta]

Thanks OssieMac,
That was very helpful. As you can probably tell I'm a beginner so this helps
to put some of this VB stuff in perspective.
The reason I thought the path was necessary is we are on a network and as
the other program (proprietary) spits out the txt file it goes in “thatâ€
folder on the operators hard drive
I need 15 pieces of information from the text file to go into one row on the
spreadsheet (that spreadsheet will have pivot tables)
I haven't got it to work yet because I have to splice it of the top of the
script.
However, if anyone can suggest other ways of doing this, or has resource
that might get the job done quicker please let me know
Once again good job OssieMac,

Thanks
BadRasta