using filesearch

etpeterson

New Member
Joined
Jan 19, 2005
Messages
18
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mdavide

Board Regular
Joined
Oct 19, 2004
Messages
211
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

New Member
Joined
Jan 19, 2005
Messages
18
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

Board Regular
Joined
Oct 19, 2004
Messages
211
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

New Member
Joined
Jan 19, 2005
Messages
18

ADVERTISEMENT

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

New Member
Joined
Jan 19, 2005
Messages
18
I got the first part working, but still am having troubles removing the message box.
 

mdavide

Board Regular
Joined
Oct 19, 2004
Messages
211

ADVERTISEMENT

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

New Member
Joined
Jan 19, 2005
Messages
18
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

Board Regular
Joined
Oct 19, 2004
Messages
211
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
 

Forum statistics

Threads
1,147,623
Messages
5,742,208
Members
423,712
Latest member
edzubur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top