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 E9but 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/sampleonly 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
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 replyPlease find detila you asked forThere will be
seperate summary sheet which contain folowingPlease find attached sheetThanks
once again for great Help
RegardsShrinivas
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 FriendsWaiting 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 AllI 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 egSuppose main folder name is "complete"In this
folder there are folder,AA,BB,CC and so onIn each folder there areExcel file
may be 1,2,3I 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
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.
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+unsubscribe@ 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+unsubscribe@ 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.
--
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.
