file name reuse

M

milus

I receive plain text files in the form of:


ADV1 R0001 040713VORVOR04072 9001229583
-H-07-05000036800000001305000000000
ADV218235592 PLUG-DRAIN 00005UOI0005EA
00005KY9001229583
ADV1 R0001 040713VORVOR04072 9001229583 ...

I open the files in Excel97 using the following code to isolate a
particular bits of information. The I reopen the file after changing
the fixed width arrays to isolate the next set of data. This is
repeated 8 times. How do I write the full file name to a cell to
automate the procedure? Thanks.

Dim sFile

sFile = Application.GetOpenFilename( _
FileFilter:="Text Files (*.adv), *.adv", FilterIndex:=1, _
Title:="Copy Text File To Worksheet")
If sFile <> False Then
Workbooks.OpenText Filename:=sFile,_
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
Array(Array(0, 9), Array(22, 2), Array(30, 9))
End If
 
T

Tom Ogilvy

Dim vArray as Variant
vArray = Application.GetOpenFilename( _
FileFilter:="Text Files (*.adv), *.adv", FilterIndex:=1, _
Title:="Copy Text File To Worksheet",MultiSelect:=True)
If isarray(vArray) Then
for i = lbound(vArray) to ubound(vArray)
Workbooks.OpenText Filename:=vArray(i),_
Origin:=xlWindows, StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:= _
Array(Array(0, 9), Array(22, 2), Array(30, 9))
Next
End If
 
M

milus

Tom Ogilvy said:
Dim vArray as Variant
vArray = Application.GetOpenFilename( _
FileFilter:="Text Files (*.adv), *.adv", FilterIndex:=1, _
Title:="Copy Text File To Worksheet",MultiSelect:=True)
If isarray(vArray) Then
for i = lbound(vArray) to ubound(vArray)
Workbooks.OpenText Filename:=vArray(i),_
Origin:=xlWindows, StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:= _
Array(Array(0, 9), Array(22, 2), Array(30, 9))
Next
End If
Click to expand...
snip

Tom
Thanks for the prompt reply. I'll give it a try after vacation
Pat
 
Top