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: Read excel file B while in excel file A

Discussion in 'Business Applications' started by cmanes, Apr 1, 2009.

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

    cmanes Thread Starter

    Joined:
    Mar 31, 2009
    Messages:
    9
    While in excel, I would like to open another excel file and read some information.

    The file I am reading from, will have a row with "Avg." in Col A with data in Col B through L in that row that I want to extract. The data line will be in a different place in all excel files and there may be 2 lines to read.

    At this time, I have used the Open command successfully to find the file but do not know how to proceed (?the open structure may be wrong?).

    I am familiar with loops and variables but can can not figure out the rest unique to excel.

    Any help would be appreciated.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to the board.

    And that's as far as we can go before the first question. Which is:

    "How many sheets are in the file you're reading from?"
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Bomb#21 is good at coding and will help you here, but there is another thing that should be cleared up here as well as his question.
    He asks "how many sheets..." but I am wondering, given "The data line will be in a different place in all excel files" how many files you are going to want read as well - and then, after that is known, how many sheets in each file.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    We need to know the data structure of your files. What you are looking at to get values from (i.e. starts here, headers here, goes down to here, may continue to here), where you want the data into (i.e. header row here, data starting here, going from col x to y). Also, what do you want to do if you run this again? Shall it delete all data and start over copying again? Shall it append all data? Do you need to keep track of what data has been copied and what has not? Will it only be the two files you're dealing with? Do you want to pick the file to copy values from? Do you want values transferred or formulas as well? Is there any formatting you want brought forward?
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    poor cmanes! LOL Oh, cmanes, I should have said this earlier - Welcome to the Forum!

    With the brains you have here, you are bound to get lots of questions, (sheesh Zack!) but you will get answers! :)
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    LOL! You think I went over the top w/ questions? :cool:

    And yes, where the heck are my manners.. somewhere around here... ah, here they are - welcome to the board!! :D
     
  7. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Even better would be a copy of your workbooks, with sensitive data remove, and good dummy data input; with an example of a source workbook or 2.
     
  8. cmanes

    cmanes Thread Starter

    Joined:
    Mar 31, 2009
    Messages:
    9
    There is 1 sheet in each excel file that want data from.
     
  9. cmanes

    cmanes Thread Starter

    Joined:
    Mar 31, 2009
    Messages:
    9
    The number of files is not the issue. If I can get the data for 1 file, I can get the data from the rest as needed.
     
  10. cmanes

    cmanes Thread Starter

    Joined:
    Mar 31, 2009
    Messages:
    9
    This is a general response to questions.

    Each files contains numbers with a average line that I want to read.
    The line format is;

    Avg. Num1 Num2 Num3 ....... Num10

    I have a list of files to get information from. As I get each data set, I will close the file and work on the next.

    As I get each data set, I will place it within the main file. I will keep track of each data set.

    I want values only.

    I will create a template of the main file and a typical data file and get back soon.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Do you want to automatically loop through a folder and grab values? How will you associate the different files in the target file? I.e. if you pull from book1, will you make a notation in the target file with "book1" in a column? If the data goes from A1:K1 (or whatever row your data is on, which we'd need to know how to find programmatically), will it go into a new row in the target book, and L1 would contain "book1"? Please explain a little more. Do not tell us how to get it done, just what it is you are looking to do. We're the experts. No offense intended. :cool:
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If one of the other guys writes code, it could look at all the files in a folder and bring the data in - actually, if you don't do that, you will need to either change the code each time listing the new file name or name every file the same thing when you want to extract the data from it.
    So, no, the number of files isn't an issue, but in terms of efficiency - and good coding - they could do better if they know if it is just 1 other file or 5 or a varied number.
     
  13. cmanes

    cmanes Thread Starter

    Joined:
    Mar 31, 2009
    Messages:
    9
    I have created a master file where I will store the data and 3 sample files to get data from. Where can I send them for review?
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    When you click Post Reply, there will be a button that says Manage Attachments, you can upload your file(s) that way.

    We would still need to know how you want to choose the files. Do you want to have a file open dialog picker or do you want to choose the folder and loop through all folders? How do you wish to proceed?
     
  15. cmanes

    cmanes Thread Starter

    Joined:
    Mar 31, 2009
    Messages:
    9
    Here are some sample files. 1 master to initiate the read / store the data and 3 other source files.

    I only want values on the Avg. line(s)

    Thanks for looking.
     

    Attached Files:

  16. 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/814965

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice