Extracting data from multiple workbooks.

cabala · Apr 27th, 2002, 02:41 PM

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

Tom Urtis · Apr 27th, 2002, 04:24 PM

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 ]

Ivan F Moala · Apr 27th, 2002, 04:36 PM

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...??

cabala · Apr 27th, 2002, 04:40 PM

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

RET79 · Apr 27th, 2002, 04:42 PM

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 ]

Tom Urtis · Apr 27th, 2002, 04:45 PM

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!!

cabala · Apr 27th, 2002, 04:50 PM

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

Ivan F Moala · Apr 27th, 2002, 05:02 PM

Quote:

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 ]

RET79 · Apr 27th, 2002, 05:04 PM

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 ]

cabala · Apr 27th, 2002, 05:12 PM

Thanks RET79

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

Jack

Apr 27th, 2002, 04:36 PM	#3
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	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

Apr 27th, 2002, 04:45 PM	#6
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	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!! __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 27th, 2002, 02:41 PM	#1
cabala New Member Join Date: Apr 2002 Posts: 6	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

Apr 27th, 2002, 04:24 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	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 ]

Apr 27th, 2002, 04:40 PM	#4
cabala New Member Join Date: Apr 2002 Posts: 6	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

Apr 27th, 2002, 04:42 PM	#5
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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 ]

Apr 27th, 2002, 04:50 PM	#7
cabala New Member Join Date: Apr 2002 Posts: 6	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

Apr 27th, 2002, 05:04 PM	#9
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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 ]

Apr 27th, 2002, 05:12 PM	#10
cabala New Member Join Date: Apr 2002 Posts: 6	Thanks RET79 I'll put it to use and see the developments. Jack