Open book as hidden and getting value

[Whitestar]

In vbscript it is possible to create a new Excel object, open a workbook
and get the value while keeping everything hidden (myXLApp.Visible =
False).

My question is: In VBA, is it possible to have a workbook open (and
visible) and then open another workbook and getting av value from it
without displaying it?

Say I have workbook A open and visible. I want to open workbook B, get
the value in cell A1 and close it without ever displaying it.

Thanks

--
Whitestar
------------------------------------------------------------------------
Whitestar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28253
View this thread: http://www.excelforum.com/showthread.php?threadid=479820

[K Dales]

Yes: you can automate a new Excel session from inside Excel VBA and open the
book in the new session, which will be hidden by default:
Dim NewXl As Excel.Application, NewBook as Workbook
Dim A1Val as Variant
Set NewXL = New Excel.Application
Set NewBook = XL.Workbooks.Open("FilePath")
A1Val = NewBook.Sheets(1).Range("A1").Value
...
NewBook.Close
NewXL.Quit
Set NewBook = Nothing
Set NewXL = Nothing
--
- K Dales

[Dave Peterson]

Yep.

But why not just open it in the same instance of excel.

If you turn application.screenupdating to false, open the workbook, then
retrieve the value, then turn application.screenupdating to true, the end user
shouldn't even be aware.

But John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

And you could also just build a formula in an empty cell, retrieve the value and
then clean up that helper cell.

--

Dave Peterson

[Whitestar]

Dave Peterson Wrote:
> And you could also just build a formula in an empty cell, retrieve the

> value and then clean up that helper cell.Dave Peterson
How do you mean exactly?

[Dave Peterson]

One way:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myVal As Variant

With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

myCell.Formula = "='C:\my documents\excel\[book1.xls]Sheet1'!$A$1"
myVal = myCell.Value

myCell.ClearContents

MsgBox myVal

End Sub

If you're having trouble building that formula:

open the other workbook
copy the cell you want to retrieve
go to a worksheet in a different workbook.
edit|paste special|check that Paste Link button.
Close the 2nd workbook
Look at how excel built that formula.

--

Dave Peterson