Fair enough.
The loop to look through the folders isn't easy to get to from the recorder.
If you look at the Help text for the SubFolders property, you'll see an example
of how to process subfolders.
This set of macros starts in the folder "C:\Temp"
and looks for the file: "Book1.xls".
It then calls a function to open the file, copy the data, then close the file.
It then loops through all of the subfolders in the folder and calls ITSELF to
repeat the cycle.
it uses a technique that makes use of the concept of "local" variables and
"public" variables.
take a look and step through the macro to see if you can follow what it does:
(You'll have to add your own code to the "Copy Stuff" macro)
---------------------
Option Explicit
Public fso
Sub CopyData()
Dim FCnt
Set fso = CreateObject("Scripting.FileSystemObject")
FCnt = Get_FolderData("C:\Temp")
MsgBox "Loaded " & FCnt & " Files"
End Sub
Function Get_FolderData(Foldername)
Dim cnt, stat
Dim Fldr, SubFldr, SubFldrs
If (Right(Foldername, 1) <> "\") Then Foldername = Foldername & "\"
If (fso.fileexists(Foldername & "Book1.xls")) Then
cnt = cnt + 1
stat = Copy_FileData(Foldername)
End If
Set Fldr = fso.getfolder(Foldername)
Set SubFldrs = Fldr.subfolders
For Each SubFldr In SubFldrs
cnt = cnt + Get_FolderData(SubFldr.Path)
Next SubFldr
Get_FolderData = cnt
End Function
Function Copy_FileData(Foldername)
Application.ScreenUpdating = False
Workbooks.Open Foldername & "Book1.xls"
'.... copy stuff ...
Workbooks("Book1.xls").Close savechanges:=False
Application.ScreenUpdating = True
Copy_FileData = True
End Function
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: Secret Shot <secrets...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Thu, May 16, 2013 1:24:51 PM
Subject: Re: $$Excel-Macros$$ Macro to open all file in folder
Thanks for the prompt paul,
But i tried few recording of macro, but didnt work. Can you please help me with
Sample Macro so that i can mobify that as per my requirement.
Regards
Pankaj k Pandey
On Thu, May 16, 2013 at 10:49 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
How comfortable are you with VBA?
>How much of this do you need help with?
>
>The Filesystem object is used to set up the folder/subfolder navigation.
>the function calls themselves are not difficult.
>
>Do you then need help copying rows of data?
>
>Paul
>-----------------------------------------
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-----------------------------------------
>
>
>
>
>
________________________________
From: Secret Shot <secrets...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Thu, May 16, 2013 12:52:34 PM
>Subject: $$Excel-Macros$$ Macro to open all file in folder
>
>
>
>Dear Group,
>
>
>I want help in writing a macro to make a list of of all sub folder in a folder
>and then open each sub folder and then open a common file from each sub folder
>and then copy Row 1 to 5 from sheet on and paste in the another file (On which
>macro is written)
>
>
>For Example there is a folder Named as "MASTER FOLDER" in this master folder
>there are 5 sub folder with any name, in every sub folder there is a specific
>file Book 1.
>
>
>Now i want to copy row no 1 to 5 of sheet from all Book 1 from each sub folder.
>My condition these sub folder can be m numbers. these are not fix.
>
>
>Kindly help in this.
>
>
>--
>Pankaj Pandey
>Bhopal --
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members
>are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email
>to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members
>are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email
>to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
Pankaj Pandey
Bhopal --
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
