Getting data from another workbook

[MattArmitt]

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

 

[CharlesH]

Hi,


See if this link helps.

http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

 

[MattArmitt]

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]

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