+ Reply to Thread
Results 1 to 7 of 7

Importing data from Excel files

  1. #1
    Registered User
    Join Date
    09-16-2008
    Location
    Madrid, Spain
    Posts
    46

    Importing data from Excel files

    I have lots of different excel files with data filled in by technicians who install computers and for each computer, they make an excel file that is pre-made and they just fill it out. So for each computer, I have a different file.

    Is there a way to take a new excel file and have it import data from each separate excel file?


    For example, Imagine I have excel files named Computer 1 and Computer 2, each of them with the same exact layout but different data, THEN I have another filed named Computer_Summary, also an excel file.

    Can I have the data from cells A1, A2, and A3 from both Computer 1 and Computer 2, go into the cells B1, BG2, and B3 in Computer_Summary ?

    Thank you so much in advanced for any help you can give me or point me to resources where I can find this information.

  2. #2
    Registered User
    Join Date
    08-15-2008
    Location
    Mass
    MS-Off Ver
    2016, 64- Bit
    Posts
    28
    Try this. Create and save a workbook called "Master". Then create and save a workbook called Test1 and a workbook called Test2. In cell reference A1 on Test1 type "Computer is good". In cell reference A1 on Test2 type "Fried Computer". Then go to the Master workbook. Make sure all three workbooks are open and only those 3 are open in excel. Go to the Windows menu in Excel and click on "Arrange", then choose "Horizontal". In cell reference A1 on the Master file type an "=" symbol then click on cell A1 on the Test1 spreadsheet. You'll notice it will make a long but correct path to the file and reference. Imediately following the reference in A1 type &","& .Then back on the Master file click on cell reference A1 in Test2, the formula should then be extended by the path and cell you are referencing on Test2. Hit the enter button. Notice that the Master file now has the values from both files (test1 and test2) in one cell? The "&" are used to concatenate other values, in my example it added a coma between the Test1 and Test2 A1 values. Close all files, then just open up the Master file. You will be prompted that the file contains links, click the Update button. Master will show the values that you linked in. Apply this same logic for as many cells as you want.

  3. #3
    Registered User
    Join Date
    08-15-2008
    Location
    Mass
    MS-Off Ver
    2016, 64- Bit
    Posts
    28
    Try this. Create and save a workbook called "Master". Then create and save a workbook called Test1 and a workbook called Test2. In cell reference A1 on Test1 type "Computer is good". In cell reference A1 on Test2 type "Fried Computer". Then go to the Master workbook. Make sure all three workbooks are open and only those 3 are open in excel. Go to the Windows menu in Excel and click on "Arrange", then choose "Horizontal". In cell reference A1 on the Master file type an "=" symbol then click on cell A1 on the Test1 spreadsheet. You'll notice it will make a long but correct path to the file and reference. Imediately following the reference in A1 type &","& .Then back on the Master file click on cell reference A1 in Test2, the formula should then be extended by the path and cell you are referencing on Test2. Hit the enter button. Notice that the Master file now has the values from both files (test1 and test2) in one cell? The "&" are used to concatenate other values, in my example it added a coma between the Test1 and Test2 A1 values. Close all files, then just open up the Master file. You will be prompted that the file contains links, click the Update button. Master will show the values that you linked in. Apply this same logic for as many cells as you want.

  4. #4
    Registered User
    Join Date
    09-16-2008
    Location
    Madrid, Spain
    Posts
    46
    Thank you so much, I will try this when I get to work tomorrow!!!

  5. #5
    Registered User
    Join Date
    09-16-2008
    Location
    Madrid, Spain
    Posts
    46
    Can I do this without opening them all in Excel at the same time??

    How could I use "Microsoft Query" for this?

    Basically, Technicians submit excel files filled in each time they do a install and then put them on a server, in a specified folder. I would like to create a file taht goes into each file in that folder and extracts data from specified cells.

    Example.
    FOLDER -> C:\Installs\Excel1.xls, C:\Installs\Excel2.xls, C:\Installs\Excel3.xls and so on.

    I have MASTER.XLS, and I would like it to go to each file in that folder and extract the information from each XLS file.


    A macro? Import tool? MS Query ?


    (I am very new to excel as you can see hehe)
    Last edited by cultavix; 09-17-2008 at 03:35 AM.

  6. #6
    Registered User
    Join Date
    09-16-2008
    Location
    Madrid, Spain
    Posts
    46
    I found this

    http://www.mrexcel.com/tip025.shtml

    It shows you how to create a macro to import from an excel file daily, and puts a date. I could change this around a little to fit my needs.

    I am having a really hard time using Office 2007 because I cant find half the stuff they tell me....

    is there some kind of help for this?? can I use the old layout??




    ___ I found out that using this macro, it just imports the entire document. I need it to import just specified cells.
    Last edited by cultavix; 09-17-2008 at 04:28 AM.

  7. #7
    Registered User
    Join Date
    09-16-2008
    Location
    Madrid, Spain
    Posts
    46
    Here is a schematic that i drew to show what i want exactly.

    Thank you all so much for helping me!!

    *FILE IS ATTACHED, ITS IN JPG FORMAT, I DREW THIS IN MS PAINT
    Attached Images Attached Images

+ 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. Excel files mixing together
    By bn.ahhs in forum Excel General
    Replies: 0
    Last Post: 08-06-2008, 04:30 PM
  2. shifting online data comes to excel as per formula with very minimum timelag
    By cramatr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2007, 10:08 PM
  3. Replies: 4
    Last Post: 03-15-2007, 06:53 PM
  4. Importing raw data into Excel from TCP source
    By Akif in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2007, 01:41 PM
  5. Importing data into Excel from Access
    By Blondegirl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2006, 05:51 PM

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