defining a range using VBA

[cmazur71]

How do I define a range using VBA? The data for the range is located in another workbook that is not opened.

 

[santeria]

Depends on what you are wanting to do, it seems...

I just checked a bundle of Macros, and the range definition had a wide number of variants...

 

[Dunkay]

Hmmm...

Does the workbook have to stay closed?

Is it always in the same location?

I have used something before along the lines of:

Application.Workbooks.Open ("temp1.xls")
Workbooks("temp1.xls").Worksheets("Sheet1").Activate

'Do some stuff

Activeworkbook.Close

However, this is not very efficient programming as it is slow , going back and forth between workbooks like this!

Hope this is of some use.

Dunkay.

 

[cmazur71]

I have a combo box, and the ListFillRange contains a Range (CUST_LIST) which referes to a list in another workbook.
I am trying to defing this range without having to open up the other workbook. Is this possible?

 

[santeria]

Yes... So long as you have something like a list reference to the Cells, or something comparable ( darn... my workbook with what I used is not here, ah well... someone will no doubt come up with the appropriate linkage)

Actually.... this is one example of how to access a closed book :

http://www.mrexcel.com/board2/viewtopic.php?t=48998&highlight=reference+closed+book


I did something similar... just needs a reference to the Cells in that closed book, usually defining the First Line/row, and subsequent...

 

[cmazur71]

What would that code look like?