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.
