Getting data from another workbook

MattArmitt

New Member
Joined
Aug 29, 2007
Messages
36
Hi

I am looking for a bit of help with a problem I am having.

I have a central workbook that collates the data from numerous other workbooks onto a master workbook. At the moment this achieved using links. Unfortunately there are a lot of workbooks and so the linking can take time and can be unstable.

What I would like to do is to use VBA to extract the information for me.

The source workbooks will all be kept in the same location. The worksheets on each workbook will be the same on source and master. Below is an extract of the code I have used to reference to the source files:

Dim SiteName As String
Dim FileName As String
Dim FilePath As String
FileName = InputBox("Enter source file name", "FileName")
SiteName = InputBox("Please Enter Site Name", "Site")
FilePath = "s:\Matt\NPO Reports\"
workbooks.open(FilePath & FileName)

In the context of the code I have written this works in that it opens the required file and I can then copy the data over to the master file using additional code.

My problem is that I don't want to keep having to open the source files to extract the data. Is there any way that I could extract data from a workbook without first having to open it?

Thanks

Matt
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

MattArmitt

New Member
Joined
Aug 29, 2007
Messages
36
Thanks for the reply Charles

It looks like it would solve my problem and I will have a go using it. The main issue I have is that code has to be put into the source files.

I should have explained that I want to use this method to update numerous different schedules, each of which pull data from different source files. These source files come from numerous people across the business so I reckon I could have an issue with spreadsheet maintainance if I have to put code on the source files.

On the article, it states that it is not possible to pull data from a closed workbook without opening it. Is this the case??

Thanks again

Matt
 

CharlesH

Active Member
Joined
Apr 23, 2005
Messages
467
HI,

You can pull data from a closed workbook. Ron De Bruin site has a "ADO" method to do this.
The only draw back to this approach is the I believe it pulls data from a specified range in the closed workbooks.

Check
http://www.rondebruin.nl/ado.htm

I'm not sure how you would be able to use this in your "source" file.
You may be able to use this in your main file.

Also look at this.
http://j-walk.com/ss/excel/tips/tip82.htm
 
Last edited:

Forum statistics

Threads
1,141,226
Messages
5,705,130
Members
421,379
Latest member
SoundlessResonance

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
Top