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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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