Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Extract Data from Closed workbook (Excel 2000)

    HI all,

    Question is it possible to extract data from a closed workbook? If so, does anyone know of a post that might help me accomplish this task?

    Thanks Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Data from Closed workbook (Excel 2000)

    You can use a formula to refer to a cell or range in a closed workbook. To create such a formula:
    - Open the workbook (this makes things easier)
    - Activate the workbook in which you want to refer to the other workbook.
    - Type =
    - Use the Window menu to activate the other workbook.
    - Activate the appropriate worksheet.
    - Click the appropriate cell.
    - You'll get a formula that looks like this:

    ='[MyWorkbook.xls]MySheet'!$A$3

    - Now activate the other workbook and close it.
    - The formula will change automatically to look like

    ='C:Excel[MyWorkbook.xls]MySheet'!$A$3

    - When you save the workbook with the link formula, close and reopen it, you'll be prompted if you want to update the link.
    - You can update the link at any moment by selecting the cell with the formula and pressing F9.
    - If you need the external value in code, you can refer to the value of the cell with the link formula.
    - There is no way to refer to data in a closed workbook directly in VBA code.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Extract Data from Closed workbook (Excel 2000)

    Thanks Hans

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Data from Closed workbook (Excel 2000)

    >- There is no way to refer to data in a closed workbook directly in VBA code.

    Absolutely no way (<post#=393956>post 393956</post: >)?
    Gre

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Data from Closed workbook (Excel 2000)

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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