Have you tried to follow the logic of the program?
The Operating System (windows) will not understand
"sub sub" folders.
You have folders.
If there are folders with the folder, they are sub folders.
What you're basically saying is that you only want to evaluate subfolders for
C:\temp.
In that case, in the function Get_FolderData(), I would add:
If (Foldername = "C:\temp\") then
or better yet:
If (ucase(Foldername) = ucase("C:\temp\")) then
prior to the line:
Set Fldr = fso.getfolder(Foldername)
be sure to put
End If
prior to :
Get_FolderData = cnt
---------------------------------------------
As for creating a list of folders processed,
I would create a "public" variable, like:
Public msg
Initialize it in the CopyData() macro with something like:
msg = "Folders Processed:"
then, in the Get_FolderData() function, add a line:
msg = msg & chr(13) & msg
back in the CopyData() macro, replace the msgbox line with:
msgbox msg
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: Sun, May 19, 2013 5:05:44 AM
Subject: Re: $$Excel-Macros$$ Macro to open all file in folder
Thanks Paul,
You solved all my problem, this code is doing exact what i wanted to do. The
only thing now i want as this code is working perfectly for my requirement. but
it is also searching "Book 1" file even in sub sub folders.
can you please suggest the code so its search "Book 1" only in the main folders
of "C:Temp" not the Sub Folders of Main folders in C:Temp"
Like i have folder named as "FLDR 1" in "C:Temp" then i have 2 sub folders name
"SF1", "SF2" in Folder "FLDR 1". Now the code should only search "Book 1" file
in FLDR 1, not in SF1 & SF2.
Because "Temp" is my Main folder and under "Temp" i would have my sub folders.
and i dont want sub sub folders to be looked at.
Request you to kindly help.
Also in msg box displayed at ending of macro, it would be great if i can have
list of files loaded instead of only count.
Kindly help.
Regards
Pankaj Pandey
On Thu, May 16, 2013 at 11:18 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
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.
>
>
>
--
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.
