using filesearch

[etpeterson]

I have some code that using filesearch to search a directory for all .txt files. This code stores in n, the number of files and FS.FoundFiles is an object where the file names are. At least that's what I think it does and have been told that as well. Here's the code just to be sure:

Set FS = Application.FileSearch
With FS
.NewSearch
.LookIn = Range("B5")
.Filename = "*.txt"
n = .Execute
End With

I then want to run some code on each file. This is what I have, and I believe all I have is a syntax error:


Dim counter
For counter = 1 To n
Workbooks.OpenText Filename:=FS.FoundFiles.counter _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, , 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Sheets(FS.FoundFiles.counter).Select
Sheets(FS.FoundFiles.counter).Move After:=Workbooks("Fault Log Creator.xls").Sheets(counter)
Call Auto_Format
Sheets("Sheet1").Select
Next counter
Application.StatusBar = False

In the bolded section, I want to open each file in sequence. My main question is how do I access the list of files in FS.FoundFiles. I'm currently trying:

Workbooks.OpenText Filename:=FS.FoundFiles.counter

Where I was hoping that FS.FoundFiles.counter would be the filename of the Xth file. I guess I'm wrong.

Please Help!!

Thanks,
Eric

 

[mdavide]

HI,

your .LookIn is wrong, it must be a path not a range (i.e "C:\")

then, FS.FoundFiles(#) return the file name

for i=1 to n
msgbox(FS.FoundFiles(i))
next

DM

 

[etpeterson]

For my .lookin, there is a path in the range ("B5"). Will this still work? or will I have to do something like ActiveSheet.Range("B5").Value.

 

[mdavide]

yes, you're right, it works.

however I think it's better:

dim ws as worksheet
set ws =thisworkbook.worksheets("your sheet name")

and then

...
.lookin=ws.range("B5").value
...

DM

 

[etpeterson]

I'm getting close, but still having problems with the file open part.

Dim counter
For counter = 1 To n
Workbooks.OpenText Filename:=(MsgBox(FS.FoundFiles(counter))) _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Sheets(MsgBox(FS.FoundFiles(counter))).Select
Sheets(MsgBox(FS.FoundFiles(counter))).Move After:=Workbooks("Fault Log Creator.xls").Sheets(counter)
Call Auto_Format
Sheets("Sheet1").Select
Next counter
Application.StatusBar = False

In this section, the lines:

Sheets(MsgBox(FS.FoundFiles(counter))).Select
Sheets(MsgBox(FS.FoundFiles(counter))).Move After:+Workbooks("Fault Log Creator.xls").Sheets(counter)

work perfectly. However, when I try to open different files, it doesn't work. With this code, I do get a message box with the proper path name for the file I want to open I click "OK" and then get a run-time error from excel. It says that 1.xls could not be found. What am I doing wrong?

Secondly, is there a way that I can get rid of the message boxes, being that I have 13 or more files to open.

Thanks a bunch,
Eric

 

[etpeterson]

I got the first part working, but still am having troubles removing the message box.

 

[mdavide]

no need to use msgbox, i wrote it in my ex only to see what foundfiles contains.

so delete every MSGBOX() from your code.

DM

 

[etpeterson]

unfortunately when I remove the message box command, I get an error:
Run-Time error '9': Subscript out of range
What is this error, why do I get it, and how can I avoid it? If I have the MsgBox() in there, it works perfectly.

Thanks,
Eric

 

[mdavide]

check every
Sheets(FS.FoundFiles(counter))

FS.FoundFiles(counter) is a path not a sheet name: "C:\10.txt" not "10"

when you open a text file, excel open a new workbook where data are inside the first sheet, so you could use

Sheets(1).Select

tell me if it works

DM

 

[etpeterson]

That did the trick. Thanks a bunch.