Dear Paul Sir
Really a excellent work
Its magic its work like anything.
Thanks very much
Regards
Shrinivas
On Thu, Jan 5, 2017 at 9:03 PM, Paul Schreiner <schreiner_p...@att.net>
wrote:
> Shrinivas:
>
> I wrote a macro that created from 1 to 5 files in 25 folders for a total
> of 73 files.
> I put values in cells A2 through E10.
>
> In your original email, you said you wanted the values from cells E3, *F5*,
> E8 and E9
> but in your sample, you used E5.
>
> I'm sure you can figure out how to modify the code to do whatever you want.
> The following macro assumes that the workbook with this macro resides in
> the "parent" folder of the folders containing the data files.
>
> It will look in all FIRST LEVEL sub-folders (regardless of name) and open
> all Excel files in the folder.
> That is to say, if there are subfolders like AA/1/sample
> only files in the /AA/ level will be opened.
> and only the information on the first sheet is retrieved.
>
> The macro reported the values from the 73 test files in 9-10 seconds.
>
> let me know if you need additional explanation.
>
> Option Explicit
> Sub ReadAllFiles()
> Dim fso, fldr, ext
> Dim sFldr, File
> Dim wb As Workbook
> Dim nRow
> Dim tstart, tstop, TElapsed, TMin, TSec, msg
>
> tstart = Timer
>
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fldr = fso.getfolder(ThisWorkbook.Path)
>
> ThisWorkbook.Sheets(1).Range("A2:Z65000").ClearContents
> nRow = 1
> Application.ScreenUpdating = False
>
> For Each sFldr In fldr.subfolders
> For Each File In sFldr.Files
> ext = fso.getextensionname(File.Path)
> If (UCase(Left(ext, 3)) = "XLS") Then
> Set wb = Workbooks.Open(File)
> nRow = nRow + 1
> ThisWorkbook.Sheets(1).Cells(nRow, "A").Value = File.Name
> ThisWorkbook.Sheets(1).Cells(nRow, "B").Value =
> wb.Sheets(1).Range("E3").Value
> ThisWorkbook.Sheets(1).Cells(nRow, "C").Value =
> wb.Sheets(1).Range("F5").Value
> ThisWorkbook.Sheets(1).Cells(nRow, "D").Value =
> wb.Sheets(1).Range("E8").Value
> ThisWorkbook.Sheets(1).Cells(nRow, "E").Value =
> wb.Sheets(1).Range("E9").Value
> wb.Close savechanges:=False
> End If
> Next File
> Next sFldr
> Application.ScreenUpdating = True
> 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> msg = "Processed " & nRow - 1 & " files in:"
> tstop = Timer
> TMin = 0
> TElapsed = tstop - tstart
> TMin = TElapsed \ 60
> TSec = TElapsed Mod 60
> msg = msg & Chr(13) & Chr(13)
> If (TMin > 0) Then msg = msg & TMin & " mins "
> msg = msg & TSec & " sec"
> MsgBox msg
> 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> End Sub
>
>
> *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*
> -----------------------------------------
>
>
> On Thursday, January 5, 2017 4:32 AM, Shrinivas Shevde <
> shrinivas...@gmail.com> wrote:
>
>
> Dear Paul
> Thanks for reply
> Please find detila you asked for
> There will be seperate summary sheet which contain folowing
> Please find attached sheet
> Thanks once again for great Help
>
> Regards
> Shrinivas
>
>
>
>
>
>
>
> On Wed, Jan 4, 2017 at 7:52 PM, Paul Schreiner <schreiner_p...@att.net>
> wrote:
>
> This can be easily done.
> I recently created a "Combined Summary" report that opened over 500 files
> in subfolders and copied 160 columns of data from a variable number of rows
> on the "Summary" sheet of each workbook to the "combined" workbook.
> Resulting in over 2700 rows of data.
> Since the files weren't large, it took slightly over 5 minutes to run.
>
>
> It should be simple to do what you want, but how do you want the final
> report to look?
> The values in cells E3,F5,E8,E9 from the file in folder AA should
> appear... where?
> The values in cells E3,F5,E8,E9 from the file in folder BB should
> appear... where? on the same sheet?
>
> Can you send me a couple of samples of what the files look like and what
> you want the summary sheet to look like?
>
>
> *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*------------------------------
> -----------
>
>
> On Wednesday, January 4, 2017 1:16 AM, Shrinivas Shevde <
> shrinivas...@gmail.com> wrote:
>
>
> Dear Friends
> Waiting for reply
>
> ---------- Forwarded message ----------
> From: *Shrinivas Shevde* <shrinivas...@gmail.com>
> Date: Tue, Jan 3, 2017 at 4:55 PM
> Subject: $$Excel-Macros$$ Macro for Copy Paste
> To: excel-macros@googlegroups.com
>
>
> Dear All
> I want a macro for following can Anyone help me.
>
> I have a folder ,which contain many folder,In each folder there are
> excel file(File having single sheet) .I want to copy E3,F5,E8,E9 from
> each file and paste in one excel sheet.
> For eg
> Suppose main folder name is "complete"
> In this folder there are folder,AA,BB,CC and so on
> In each folder there areExcel file may be 1,2,3
> I want to copy the data from each file from cell( E3,F5,E8,E9 ) and want
> to paste in one excel sheet.
>
> --
> Shrini
> --
> 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 <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+unsubscribe@ googlegroups.com
> <excel-macros+unsubscr...@googlegroups.com>.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/ group/excel-macros
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/ optout
> <https://groups.google.com/d/optout>.
>
>
>
> --
> Shrini
> --
> 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 <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+unsubscribe@ googlegroups.com
> <excel-macros+unsubscr...@googlegroups.com>.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/ group/excel-macros
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/ optout
> <https://groups.google.com/d/optout>.
>
>
> --
> 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 <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+unsubscribe@ googlegroups.com
> <excel-macros+unsubscr...@googlegroups.com>.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/ group/excel-macros
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/ optout
> <https://groups.google.com/d/optout>.
>
>
>
>
> --
> Shrini
> --
> 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 https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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 https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Shrini
--
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
