Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book as hidden and getting value
In vbscript it is possible to create a new Excel object, open a workboo and get the value while keeping everything hidden (myXLApp.Visible False). My question is: In VBA, is it possible to have a workbook open (an visible) and then open another workbook and getting av value from i without displaying it? Say I have workbook A open and visible. I want to open workbook B, ge the value in cell A1 and close it without ever displaying it. Thank -- Whitesta ----------------------------------------------------------------------- Whitestar's Profile: http://www.excelforum.com/member.php...fo&userid=2825 View this thread: http://www.excelforum.com/showthread.php?threadid=47982 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book as hidden and getting value
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book as hidden and getting value
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book as hidden and getting value
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book as hidden and getting value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to open new book (workbook) with other | Excel Discussion (Misc queries) | |||
Excel does not open Book | Excel Discussion (Misc queries) | |||
Book.xlt in doesn't open as default | Excel Discussion (Misc queries) | |||
open work book | Excel Programming | |||
Open book, check for macros, close book | Excel Programming |