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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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:

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top