looping on excel files in directory

[christopher ward]

Hi All

If you can help my kindest thanks and sorry if I do not explain my issue
very well - I am no longer a programmer being old and grey ( whats left )

I have a direcory on my machine c:\finance
i want to loop in the directory on every .xls file ( open each SS and copy
data )
I am stuck on the loop I need to create and do not understand the DIR
structure very well - I suspect the code I want is simple in nature and is
more me being a poor VBA user.

If you can help please feel free -

Sub ProcessAll()

' ok we need to grab some meta data for Path and Extension from the META
cells

sPath = Worksheets("Meta").Cells(4, 2).Value
sExtension = Worksheets("Meta").Cells(6, 2).Value

If sPath = "" Or sExtension = "" Then
MsgBox "META data incorrect I will stop NOW check path and extension"
Exit Sub
End If

sFile = sPath & sExtension

MsgBox sFile

' so now we have the directory to loop within for all xls files ?


So far I have craeted this poor code

 

[Mike H]

Christopher

Put these 2 riutines in a general module. The first loops through the named
folder and opens every Excel workbook in that folder. It passes the name to
the second routing where you can do things.

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile <> ""
Workbooks.Open Filename:=MyPath & ActiveFile
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
ActiveFile = Dir()
Loop
End Sub

Sub DoSomething(Book As Workbook)
'do things
MsgBox ActiveWorkbook.Name

End Sub



Mike

 

[joel]

You need to add the slash in between the folder and filename

Sub ProcessAll()

' ok we need to grab some meta data for Path and Extension from the META
cells
folder = "c:\finance\"
FName = dir(Folder & "*.xls")
Do while Fname <> ""
set bk = workbooks.open(Filename:=Folder & FName)
'add yor code here
with bk

end with
bk.close savechanges:=False
FName = dir()
Loop

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/copy3.htm

Maybe you can use the Merge add-in

 

[christopher ward]

Thank you for your help - very kind and looks very good

 

[Eddy Stan]

Hi Ron,
How are you. It is long since i get help from you.
please help me on this

I want names of all workbooks open at sheet "archive", starting from range b5
then i want in the first sheet "Form 1", at b5, a pull down menu, showing
all the file name with path but without sheet name, which were listed in
"archive" sheet

the sheet i required to attach with the file is in "Form 1" c5
so b5: file name with path ; c5: sheet name
now i have a form below which should show data from the above file & sheet

if names exist in sheet "archive" from range b5:... then i need only the
pull down menu at b5 of sheet "Form 1" (this will be the situation, when i
run the macro second time)

thanks ron.