1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro to Search All .xlsx in Folder and Return Column

Discussion in 'Business Applications' started by ccrossing, Sep 2, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, lets do it a step at a time.
    I want you to test this code which should load all your Folder names in to Sheet2 and then open them one at a time, at this stage it doesn't actually do anything with them.
    You may have to convert this to an xlsx workbook and enable macros etc.
     

    Attached Files:

  2. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    It does not need to say where it comes from as it is already labeled in the column (I did not show this on the .xls files I uploaded). So ideally it would look something like this attachment. The order that the files are searched, and thus the order they show up in the spreadsheet is not important.
     

    Attached Files:

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Have you tested the code in the Workbook I posted, to run the code click on the Sheet2 "Get Files" button?
     
  4. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    Sorry for the delay--I had to run to a meeting. I ran it once but it hit a Runtime error when it tried to open "~Temperature.xlsx" which is an autobackup file I think.

    Running again...I will keep you posted.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, if you can get that to work, this version does all the rest.
    It opens the files and transfers the data.
    What you need to do is ensure that the Worksheet names match the items that you are going to search for ie. dog fish etc.
    You also need to ensure that the headings in column A match your actual values.
    It works fine with the 2 workbooks that you provided. It is just a case of getting it set up correctly for your actual data.

    It runs pretty quckly, so if yours isn't it probably has a problem.
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    If really necessary you could email some real data to set it up with.
     
  7. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    It seems to have run well. It ran quickly considering the amount of data it has to churn through. I am going to run the newer version you uploaded and see how it goes.
     

    Attached Files:

  8. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    Ok, so I finished making sure my spreadsheets were in the exact format the macro required and...everything worked brilliantly!

    Thank you very much for your help and I am deeply indebted to you. This script literally saved me from days of copying and pasting and any accompanying errors.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1015610