Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Acolyte Charles's Avatar
    Join Date
    Feb 2013
    Location
    Biloxi
    Posts
    34
    Articles
    0
    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

  3. #3
    Acolyte Charles's Avatar
    Join Date
    Feb 2013
    Location
    Biloxi
    Posts
    34
    Articles
    0

    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 by Charles; 2014-04-27 at 07:58 PM. Reason: corrected code

  4. #4
    Acolyte Charles's Avatar
    Join Date
    Feb 2013
    Location
    Biloxi
    Posts
    34
    Articles
    0
    Hi,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •