write and read on a closed wbook (2000 sr.1)

[sal21]

I have in a server dir a wbook with one sheet
My problem is to read and write on this wbook without open it.

For example the wbook is in the path:
serverdir_serverwbook.xls

READ FROM THE CLOSED WBOOK
==========================
read from a macro the value in A1 of the closed wbook
with this condition:
If in the A1 is value "TEST1" goto on the MACRO1
If in the A1 is value "TEST2" goto on the MACRO2
If in the A1 is value "TEST3" goto on the MACRO3

WRITE THE THE CLOSED WBOOK
===================================
Simply write in A1 of the wbook closed a value "TEST1"

[sdckapr]

As far as I know there is no way from excel or excel VB to write to a closed sheet - it must first be opened. (but perhaps someone else has some tricks)

Using excel you can directly read from a closed workbook (put a formula in a cell like):
='C:Path[File Name.xls]Sheet Name'!$A$1

Using VB you can NOT directly read from a closed sheet. You can do it indirectly from VB by using excel. From VB you can put a formula in an unused cell of a worksheet (to be safe you can create a new worksheet if desired, add the formula, then read that cell:

Something like this added into your code:
<pre>'define the variables
'variable to hold the "readvalue" from the closed book
Dim vReadValue 'variable to get the value
'the location of the cell to read
Dim sReadLocation As String
'other variables as need for your code

'define location to read (directory, path, filename, sheet name, clee
sReadLocation = "='C:Path[Filename.xls]SheetName'!A1"
'create a new sheet, add a formula, read into the array, delete the sheet
With Worksheets.Add
.Range("a1").Formula = sReadLocation
vReadValue = .Range("a1")
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With</pre>


So adding some code like the above to a routine, will allow VB to read from a closed workbook. You probably coud create a function that given the path/name/cell location you could return the value if desired.

Steve

[sdckapr]

Here is the functin I alluded to in my last post

<pre>Option Explicit
Function ReadClosed(sPath As String, sFilename As String, _
sSheetname As String, sCell As String)

Dim sReadLocation As String
If Right(sPath, 1) <> "" Then _
sPath = sPath & ""
If Right(sFilename, 4) <> ".xls" Then _
sFilename = sFilename & ".xls"
If Dir(sPath & sFilename) = "" Then
ReadClosed = "'" & sPath & sFilename & "' does not exist"
Else

sReadLocation = "='" & sPath & "[" & sFilename & "]" & _
sSheetname & "'!" & sCell
With Worksheets.Add
.Range("a1").Formula = sReadLocation
ReadClosed = .Range("a1")
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End If
End Function</pre>


You could call it like:

vRead = readclosed("cath", "filename", "sheetname", "A1")

The routine will automatically add the ".xls" and the end "" to the path (if not added). It will also check for the existence of the file.

Steve

[AlanMiller]

Hi Steve

I spotted this one some time back (haven't tried it though):

Aspose.Excel .Net Excel spreadsheet reporting component which enables you to read and write Excel spreadsheets without utilizing Microsoft Excel.

Not really what the poster was asked about, but does claim to do what you were wondering.

Alan

[pieterse]

You can read/write to excel workbooks using ADO, but of course writing to them is limited to values, not formulas Look at this site for more info:

http://www.erlandsendata.no/english/...php?t=envbadac