Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Extracting data from multiple workbooks.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello all,

    I am facing a very serious problem. I need help in form of an excel macro. I will explain my torubles in short.

    I want to extract data from some 12000 work books and pasted in a newworkbook. all this while without opening those 12000 files.

    Info:
    Files :"P046000.xls" to "P057999.xls".

    Cells to extract information from: "C2, I57, I58".

    Info to be copied to A2, B2, C2 and so on for 12000 rows, keeping the 3 columns common.

    Need help. Please if somebody could help?

    Jack



  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jack --

    If as you say you want a macro to extract data from another workbook without opening the workbook, especially for 12,000 workbooks, that isn't going to happen. VBA requires that the source workbook be opened in order for the data to be extracted. Of course VBA would handle the open and close process, but the open & close actions would need to take place, even if only for a split second.

    The other option, which isn't much of one in your case given the huge number of workbooks involved (and that it's a non-macro approach), is to use paste link formulas, which for 12,000 workbooks is a job you probably won't see too many people waiting in line to apply for.

    Sorry for the blunt response. I'm curious -- what in the world are 12,000 workbooks generated to keep track of, that apparently have the same theme or contain the same information that you need to consolidate?

    _________________
    Tom Urtis

    [ This Message was edited by: Tom Urtis on 2002-04-27 15:49 ]

    [ This Message was edited by: Tom Urtis on 2002-04-27 16:10 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom
    How are you

    Actually there is a way of getting data from
    a closed book BUT there are a lot of things
    to consider before doing this eg
    Data in the Same sheet, same range, Dir etc.

    12000 ??....I'm curious to would suggest that these files be kept in seperate Dir.

    I'll have a look for the workbook I did it on
    Plus I think this has been done by someone else...??

    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi guys,

    Thanks for the reply. Actually, Ivan, the 12000 files have the data on the dame cells and the file names also progress in ascending order. That is the good part. That said, i do understand the difficulty of the task on hand and appreciate all help.

    These files contain balance sheet extracts of insurance companies in the United states for the last year. I am doing my thesis on their financial rating. However, the files being so many, it is next to impossible to be go manually thru them to extract the info.

    Thanx.

    Jack

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    I have developed a tool to extract data from different workbooks like what you are suggesting.

    However, it involves opening each file, extracting data, then closing file. Also you would have to get all the filenames listed in a column.

    I think the tool I have would take a long time as it would involve opening 12,000 files.

    Anyway if you are interested in what I have let me know.

    RET79

    [ This Message was edited by: RET79 on 2002-04-27 15:45 ]

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ivan,

    I'd be interested to see what those steps are that would permit a data import without opening the source file at all.

    I'd imagine there'd also be an added step or 2 if the source file(s) is/are password protected?

    Thanks, look forward to seeing it!!

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom,

    Nope, the files aint password protected. The sheet name in each workbook is also the same and the dir/location of all the 12000 files is also the same (C:analysis).

    Infact, i can extract information from the closed workbook, but cannot paste it into the new workbook. Besides, i cannot also give a loop to ask the macro to go thru the 12000 files one after the other.

    Jack

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-27 15:50, cabala wrote:
    Hi Tom,

    Nope, the files aint password protected. The sheet name in each workbook is also the same and the dir/location of all the 12000 files is also the same (C:analysis).

    Infact, i can extract information from the closed workbook, but cannot paste it into the new workbook. Besides, i cannot also give a loop to ask the macro to go thru the 12000 files one after the other.

    Jack
    Hi Cabala
    1) What is the range of the data to import ?

    (Sorry got this from above)

    2) You say the same sheet BUT is the sheet
    name always the same ?



    _________________
    Kind Regards,
    Ivan F Moala
    http://www.gwds.co.nz/excel_files.html - Under Constru

    [ This Message was edited by: Ivan F Moala on 2002-04-27 16:09 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cabala...

    how about creating a loop through all the files, they seem easy to loop through, I'll give you some code to start u off:

    For i = 46000 to 57999 step 1
    workbooks.open "PO" & i & ".xls"

    workbooks(result).worksheets(summary).cell(2,1).offset(i-4600,0) = workbooks ("PO" & i & ".xls").sheets("yoursheet").range("C2").value

    workbooks(result).worksheets(summary).cell(2,2).offset(i-4600,0) = workbooks ("PO" & i & ".xls").sheets("yoursheet").range("I57").value

    workbooks(result).worksheets(summary).cell(2,3).offset(i-4600,0) = workbooks ("PO" & i & ".xls").sheets("yoursheet").range("I58").value

    workbooks("PO" & i & ".xls").close


    Next i


    It will take a while, but better than nothing.

    Hope this helps,

    RET79




    [ This Message was edited by: RET79 on 2002-04-27 16:06 ]

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks RET79

    I'll put it to use and see the developments.

    Jack

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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