opening each workbook in a folder 1

[Luis939]

Hi, I'm tryin to open every workbook in a folder I have, I thought i would set a variable to the path of the folder and say

dim wrbk as workbook

mypath = folderpath....

for each wrbk in mypath
workbooks.open filename:=wrbk
next wrbk

but thats no use i get an object required error when i try to execute the for each statement...any suggestions

actually i dont really want to open them, i just want to be able to access them in my program, thanks!!

 

[Chattin]

Any use?

http://www.mrexcel.com/board2/viewtopic.php?t=48811&highlight=opening+workbook+folder

HTH

 

[Luis939]

well i tried this code that came from that forum, and its not working, for some reason it keeps opening files from another directory I have access too i tried chaning the code to

mypath = "C:\temp\"
myfile = dir(mypath, vbnormal)
etc....


but only one file opened, the rest could not be open because according to the error message it was not in the folder, when indeed it was.
Does anyone else have any ideas for me, thanks!!




Sub temp()

' Look for Excel files
ChDir "C:\temp\"
MyFile = Dir("*.xls", vbNormal)

Do While MyFile <> &quot;&quot;

' Open file
Workbooks.Open Filename:=MyFile

' Put your calculations or whatever here
'
' Get next file
MyFile = Dir
Loop

End Sub

 

[calculus]

Your code looks close. You should change the line that says:

MyFile = Dir(&quot;*.xls&quot;,vbnormal)

to

MyPath = &quot;C:\temp&quot;
MyFile = Dir(MyPath & &quot;\*.xls&quot;,vbnormal)

This should fix the problem. Basically you were making the Temp directory the active directory, then tell the system to ckeck the entrire c:\ drive for excel files. I'm guessing it defaults to the c:\ drive.

calculus

 

[Luis939]

yea thanks alot...i did that and i also did

Workbooks.Open Filename:=Mypath & MyFile

and it worked thanks!!

 

[Bowers74]

Check out my two FAQs they might help you in the future:

faq707-4116 File and Folder Procedures

faq707-4114 Open File(s) using the GetOpenFilename dialog

Enjoy!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Luis939]

Thanks! but i have one other question, what if i dont want to open the workbooks each time, the first time ill open them all, i suppose i could simply refer to that file as the activeworkbook, since itll be the only one active, but what if i want to scan each workbook again (without opening them twice), is there a way to just reference, say, the first workbook in that folder and then the second etc, that way i can repeatedly visit those workbooks? It doesnt matter that i have to run a loop each time, i just want to scan each workbook once and then again at a later time, thanks!!

 

[Luis939]

Is there also a way to ahead of time count all the files in a folder,thanks!!!