How do I make a summary sheet to capture info from all .xml in a folder?

friedtater22

New member
Joined
Apr 26, 2014
Messages
2
Reaction score
0
Points
0
I'm trying to create a project for work. There are multiple workbooks in folders by name. We'll call this folder "R:\customers\invoices". Inside this folder are folders by name (Bob, John, Sarah, Etc.) This is a network drive by the way (in case that matters). The workbooks are located in the "Name" folders and are all the same template. Each workbook contains multiple sheets. Each workbook also contains a "Stats" worksheet that has only the summary information for each sheet in that particular workbook. My worksheets are named "Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Stats". On the "Day" worksheets, there is a cell, located at C1 that is the person's name and the summary information is located in "Q21 through Q25 (These are the "headings") and R21 through R25 (These are the "values"). In the "Stats" worksheet, the combined information is located in A7:A11 & B7:B11. I need to combine all workbooks in this folder (new workbooks added daily) to a summary workbook that we will call "R:\Customers\InvoiceSummaries.xls". On this Summary workbook, I would like to be able to summarize each name on a worksheet, then have all names summarized together on the first worksheet. Also, in case it matters, These files are all in Excel 2010 and opened mostly on a Mac, although occasionally on a PC. I've looked and looked, and researched, and studied. I just can't grasp this whole VBA thing. It's like a foreign language to me. I hope that I've been as precise as possible with what I need. Any help that you can offer would be greatly appreciated.


I have posted this on another forum 2 weeks ago and have received no answer, So I'm reaching out to other forums. Please visit www .mrexcel. com/forum/excel-questions/771261-i-need-create-macro-i-think-please-help.html#post3778770 to give an answer. (Without the spaces) Thank you!
 
HI,

This is a bit of code that you can review.
You will need more code that will open each file.
You may need loop that will loop through each name.
You can use a "For" statement or "Do While"
Do you have a worksheet that has the file name's you wish to open?

Code:
Sub Open_workbooks()
Dim Myfile As String
Myfile = "Your workbook" ''this is the workbook you want to open
'' you will need to loop thru the Names that you wish to get the data from
'' This is just a generic code '''
Workbooks.Open Filename:="R:\customers\invoices\" & Myfile
End Sub

Code:
Sub Open_workbooks()
dim i as long
Dim Myfile As String
Myfile = "Your workbook" ''this is the workbook you want to open
'' you will need to loop thru the Names that you wish to get the data from
'' This is just a generic code '''
For i = 1 to 4
    Myfile=sheets("Sheet1").text ''this will be the first person file to open
   Workbooks.Open Filename:="R:\customers\invoices\" & Myfile
''''' rest of code that will do as you want '''
next i '' this set the loop to open the next person
End Sub
 
Edit code error

Sorry The code I posted was not correct it should be.
Code:
Sub Open_workbooks()
dim i as long
Dim Myfile As String
'' you will need to loop thru the Names that you wish to get the data from'' This is just a generic code 
For i = 1 to 4
  '' corrected to add cell information    
   Myfile=sheets("Sheet1").cells(i,1).text ''this will look at the first row (i=1) and column A for Name   
   Workbooks.Open Filename:="R:\customers\invoices\" & Myfile
''''' rest of code that will do as you want '''
next i '' this set the loop to open the next person
End Sub
 
Last edited:
Hi,

If you can provide a copy of your file/files. It will help with the development of a code.
 
Back
Top