Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default looping on excel files in directory

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




--
C Ward
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default looping on excel files in directory

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

"christopher ward" wrote:

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




--
C Ward

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default looping on excel files in directory

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


"christopher ward" wrote:

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




--
C Ward

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default looping on excel files in directory

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

Maybe you can use the Merge add-in


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"christopher ward" wrote in message
...
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




--
C Ward


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default looping on excel files in directory

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


"Ron de Bruin" wrote:

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

Maybe you can use the Merge add-in


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"christopher ward" wrote in message
...
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




--
C Ward





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 151
Default looping all excel files open during the session

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.


"Ron de Bruin" wrote:

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

Maybe you can use the Merge add-in


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"christopher ward" wrote in message
...
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




--
C Ward



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help to copy files names in a column from one directory to another directory VBA Paul B[_3_] Excel Programming 4 November 10th 08 02:54 AM
Getting Range whilst looping through files in directory George Excel Programming 1 March 30th 06 04:15 PM
Check if directory empty OR no of files in directory. Michael Beckinsale Excel Programming 2 December 4th 03 11:12 PM
Looping through each directory Keith Willshaw Excel Programming 0 July 11th 03 10:21 AM
Looping through each directory Tom Ogilvy Excel Programming 1 July 11th 03 09:39 AM


All times are GMT +1. The time now is 02:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"