Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to copy files names in a column from one directory to another directory VBA | Excel Programming | |||
Getting Range whilst looping through files in directory | Excel Programming | |||
Check if directory empty OR no of files in directory. | Excel Programming | |||
Looping through each directory | Excel Programming | |||
Looping through each directory | Excel Programming |