Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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"

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •