+ Reply to Thread
Results 1 to 10 of 10

Looping through workbooks to retrieve data

  1. #1
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    275

    Looping through workbooks to retrieve data

    Hi
    I've tried to research retrieving data from a suite of (closed) workbooks and placing it in a list in a master workbook, but can't get my head around loop coding.

    I have a variable length list of workbook names in the Master workbook in column AZ (AZ3:AZ) on sheet named "Running Order".

    Each workbook has only one sheet in it and is stored in a folder on my C drive.

    I can recall each workbook individually with the Code
    Please Login or Register  to view this content.
    The name of the Folder is variable with the name of the current folder held in cell F1 of "Running Order".

    The individual CorrectbookName is variable and = Range("AL1") & (".xlsx")

    Each worksheet in each Workbook holds a variable length of data in columns DA, DB & DC.

    Columns DB or DC will be of the same length and will dictate the number of rows of data to copy.

    At the click of a button I want to loop through each workbook in the list in Running Order AZ3:AZ and copy the data form each of the workbooks sheets DA:DC to paste values in to a list in columns A2:C on the sheet "Results" in the Master workbook.

    As I've said, I have tried to find a suitable code and adjust it to work, but have seriously failed and cannot get my head round how Loops work.

    Anyone have any rough codes I could use as a starter or, (even better!) a code that is known to do what I want?!!

    Thanks in advance for your assistance.

    Frankie
    Last edited by Frankie_The_Flyer; 09-13-2019 at 12:28 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    Earth
    MS-Off Ver
    2016
    Posts
    692

    Re: Looping through workbooks to retrieve data

    Can you explain what CorrectbookName is?

    You say it's variable and held in AL1 but the workbooks you want to extract data from are listed in AZ from row 3 downwards. So what is CorrectbookName ... why are you retrieving it instead of the workbooks in AZ?
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top left of thread.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: Looping through workbooks to retrieve data

    Maybe something like this (not tested).

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    275

    Re: Looping through workbooks to retrieve data

    Hi MatrixMan.
    Bad explanation on my part! The books in the folder are usually selected individually and data entered during the day. The specific sheet to be called is named in AL1. At the end of the day I need to go through all the books and collate the results from columns DA:DC. Column AZ3:AZ in the Master book holds the names of all the books in the folder. I though I'd need a list to look up to open the books.

    Having said that I've found a code on the Internet that is supposed to search through a folder and return data from each book n the folder. I've had a go at modifying it for my requirements, but not having much luck so far! I'll have a go with the AlphaFrog response and see how that goes.
    Cheers!

    Frankie

  5. #5
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    275

    Re: Looping through workbooks to retrieve data

    And as if by Magic AlphaFrog does it again!!!

    Thanks AlphaFrog. Just what I was looking for!
    Last edited by Frankie_The_Flyer; 09-15-2019 at 09:37 PM.

  6. #6
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    275

    Re: Looping through workbooks to retrieve data

    However.....

    While your Code worked brilliantly AlphaFrog the range it was counting rows in was only one row long (my fault!) so I fiddled a few of the data cells in the worksheets and the code to ensure the first column in the code (now CY) had the most rows of data in. I then got a "Variable not defined" warning against ws.Range ("CY......etc.) So I put in Dim ws As Worksheet.
    Next issue is that it now doesn't show any of the results from the sheets in AZ! Results sheet is totally blank after the code has run.

    Two extra things I added; one is the clear contents for columns A2:D200 so if it's run twice it doesn't just stack more data in, but I commented it out in case it was doing the clear with every cycle. Second, I'm trying to collect a second lot of data from the worksheets so copied the code to make it take a second look at the worksheet and copy from CT2:CV to column L in the Master Results. This however, brings up an Application defined or Object defined error message.

    So, any ideas please??
    1. Why has it stopped producing results?
    2. Why do I get the Application defined error?

    My hacked about Code is as follows...

    Please Login or Register  to view this content.
    Thanks again for your time on this

    Frankie

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: Looping through workbooks to retrieve data

    For some reason you found it necessary to include extraneous 2s in the code. Remove them and watch the magic.

    Please Login or Register  to view this content.
    Keep the Dim ws As Worksheet. I should have declared that variable as well in my original code.
    Last edited by AlphaFrog; 09-15-2019 at 11:01 PM.

  8. #8
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    275

    Re: Looping through workbooks to retrieve data

    2 many changes!! (I thought that would ensure copying from row 2, but that's in the CY2 bit! )

    That's got the Code scrolling through without a fault, but it's only returning the data from CT2:CV; nothing from CY2:DB. CY2 has a formula in it to give me the Team Name (=IF(DA2="","",$CC$1)) so if there's nothing in the DA column I don't get an "N/A" or "VALUE" or some such in CY (and it's not blank in the sheets I'm working with). The only column in the CY2:DB group to have a firm value in is DA (the rest have formula in). Would that make any difference? (CT2:CV is generally only one two rows and has direct links to other cells to bring the data in).

    I don't understand why CY2:CV isn't returning anything from any of the sheets. I have revitalised wsRes.Range("A2:N200").ClearContents but it doesn't seem to make any difference.


  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: Looping through workbooks to retrieve data

    Try this...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 09-16-2019 at 05:15 AM.

  10. #10
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    275

    Re: Looping through workbooks to retrieve data

    Cracked it! It appears that the code is actually doing the copy paste but the first return was on Row 86. The lines returned from the second sheet checked were on Rows 174 and 175 and so on down to Rows 6000+.
    86(ish) is about the number of Rows that contain formula in the sheets (dragged down so not an exact end in each column). Then I noticed the original code is counting rows in column DB so I changed that back to DA (the column where data is placed by VBA and the only column in the range without formulas in).
    Finally I've cleared the A:D columns (Highlight columns and hit Delete) ran the code again and it works fine. Must have had something wwwaayyy down in columns A:D that caused the code to paste below it.

    So all good at the moment and another few lessons learnt!

    Thanks AlphaFrog for the fixes and MatrixMan for the interest.

    Frankie

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search for string in several workbooks and retrieve data from another column?
    By ESEKOCK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2018, 12:52 PM
  2. to compare between two workbooks and retrieve data..if match is found
    By killer12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-20-2014, 11:18 AM
  3. Retrieve data from several workbooks into one workbook
    By solob in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2013, 02:53 AM
  4. Pull retrieve data from multiple workbooks
    By Fett2oo5 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2013, 07:00 PM
  5. Retrieve data from numerous workbooks inside a Master File
    By Staci in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 03-29-2011, 07:30 PM
  6. Looping through all Workbooks in directory to retrieve rows of information
    By TechUseless in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-03-2008, 04:40 PM
  7. Workaround to retrieve data from closed workbooks by using formulas.
    By all4excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-08-2008, 08:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1