accessing closed workbook

[detlion1643]

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

 

[Andrew Poulsom]

It depends what you mean by access. A formula can return data from a closed workbook.

 

[doofusboy]

Andrew, could you please exapnd on or show a sample of:

A formula can return data from a closed workbook.

 

[detlion1643]

Is a formula the only way to return data from a closed workbook, or can it be done via VBA as well?

 

[Richard Schollar]

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]

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]

Can't you use a VLOOKUP to do that?

 

[detlion1643]

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]

Hi
here is an example of doing vlookup from a closed file. It is simply a formula synthesized using VBA.

Path = "C:\export\coffee\"
Filename = "Original.xls"
Cells(1, 1) = "=Vlookup(F2,'" & Path & "[" & Filename & "]Sheet1'!A2:B100,2,false)"

Or

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