Extracting data from multiple workbooks.

cabala

New Member
Joined
Apr 26, 2002
Messages
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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...??
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font></font></font><A HREF= "http://www.gwds.co.nz/"><font color="green">http://www.gwds.co.nz/excel_files.html - Under Constru
This message was edited by Ivan F Moala on 2002-04-27 16:09
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top