accessing closed workbook

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
I am using VBA code to basically update my workbooks from 3 specific workbooks. I have a code that I put in every new workbook I make that runs when the workbook is opened. This code opens 1 workbook and looks for data, then closes and opens another and looks for data, then closes and once more.

Everything runs fine, but opening each workbook then closing it gets annoying, and not sure if it's slower. But, is there a way to access a workbook without opening it.

Right now:
workbook("NAME").open
workbook("ORIGINAL").activate
**
workbook("NAME").close
x3
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
Is a formula the only way to return data from a closed workbook, or can it be done via VBA as well?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Depending on how the data is structured in the closed workbooks (eg are they traditional data tables with headers in row1 and data below?) you could use querytables and/or ADO to extract the data.

Unless they are data tables, you aren't going to be able to search in them without opening them though.
 

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
The closed workbooks do have headers in row1. Basically, I take x = cell.value in open workbook, then search the closed workbook in column a for a cell.value = x, then take the offsets of cell.value in the closed workbook and put them in the x offsets in the open workbook.

Hope that wasn't confusing!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Can't you use a VLOOKUP to do that?
 

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
I've never used a Vlookup before (will certainly learn it), but from watching fellow employees use them, it really bogs down their machines. I was trying to think of a different way that might increase our efficiency?
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
here is an example of doing vlookup from a closed file. It is simply a formula synthesized using VBA.
Code:
Path = "C:\export\coffee\"
Filename = "Original.xls"
Cells(1, 1) = "=Vlookup(F2,'" & Path & "[" & Filename & "]Sheet1'!A2:B100,2,false)"
Or
Code:
Cells(1, 1) = "=Vlookup(F2,'C:\export\coffee\[Original.xls]Sheet1'!A2:B100,2,false)"
It looksup F2 in sheet1 of original xls (closed) col A and corresponding col B value is pulled out
Ravi
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

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