Access to ranges in hidden sheets

**gstremer** · 03-14-2007, 11:38 AM

Hello,

Within VBA I want to get access to the data in ranges on hidden sheets. Is this possible while keeping these sheets hidden?
Best regards,

g

**mudraker** · 03-14-2007, 02:43 PM

Yes

In the ame way that you get data from any sheet. Just because it is hidden does not mean you can not work with it.

You do not need to activate or select books, sheets, rows, columns, cells etc to work with them

sheets("sheet2").range("a1:c5").copy
activesheet.paste

**ern2ern** · 03-14-2007, 03:08 PM

Mudraker,

Extracting a cell value from a hidden workbook with VB code could be very useful! What would be the syntax for [the workbook] in the string below?

ActiveSheet.Range("A1") = [the workbook]Sheets("Sheet1").Range("A1")

Thanks!

**mudraker** · 03-14-2007, 03:44 PM

Just add the workbooks(???) before sheets(???)
Remember that until a book is saved it does not have .xls as part of its name

ActiveSheet.Range("A1") = workbooks("book1.xls").Sheets("Sheet1").Range("A1")

You cann also use a variable to refer to a book by name. Replace "book1.xls" with the variable name - no " required

or you can set a variable to become a book or a sheet

dim wS as worksheet
set wS = workbooks("book1.xls").Sheets("Sheet1")
ActiveSheet.Range("A1").value  = wS.range("a1").value

**gstremer** · 03-14-2007, 03:47 PM

Thanks. The attached example might make things clearer. I want to read the first cell in the range myRange on Sheet2. Sheet2 is made hidden at the start of the test sub.
Thanks much!

g

**gstremer** · 03-14-2007, 03:51 PM

mudraker:

Understood!
Thanks again,

g

**ern2ern** · 03-14-2007, 04:26 PM

g,
Please excuse the encroachment.

Mudraker,

That's great info. It appears to work in the reverse as well...it writes to the hidden workbook w/o messing with screenupdating, etc.

I probably could have saved 10MB of my 40MB, 29 worksheet "program" (THE INTERPRETER) had I known more tricks!

See my stuff at http://www.e-linq.com/New_ElinQ/index.php (soon to be published)

Can you also tell me if there is a way in Excel VB to check if a specified Excel file exist in my web?
As far as I can see, the Dir Function will only work with local files...but maybe I haven't hit on the proper syntax of the string.
Thanks!