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

[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


"Whitestar" wrote:

>
> 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...o&userid=28253
> View this thread: http://www.excelforum.com/showthread...hreadid=479820
>
>

[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.



Whitestar wrote:
>
> 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...o&userid=28253
> View this thread: http://www.excelforum.com/showthread...hreadid=479820

--

Dave Peterson

[Whitestar]

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.




Whitestar wrote:
>
> 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?
>
> --
> Whitestar
> ------------------------------------------------------------------------
> Whitestar's Profile: http://www.excelforum.com/member.php...o&userid=28253
> View this thread: http://www.excelforum.com/showthread...hreadid=479820

--

Dave Peterson