View original (French)

Data retrieval from an Excel workbook

goldrigger -  
 goldrigger -
Hello,

I created an Excel workbook that contains cells pulling data from another workbook. To do this, I used copy/paste with a link. But the problem is that I need to open the source workbook each time.

Do you have any idea how to get data from this workbook without opening it?
Configuration: Windows XP Firefox 2.0.0.11 Excel 2003

4 answers

  1.
    tenace Posted messages 840 Status Member 224
     
    Good evening,
    Without being sure right away, try using the insert ---> object function, and there you choose the workbook to link
    take care
    Kev.
    0
  2. goldrigger
     
    Hi tenacious,

    I tried, but I just need certain cell values from the workbook. And right now, when you click on the object, it opens the workbook. It's true that it's better than opening it manually, but it's not exactly what I want.

    Thanks anyway.
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello Tenace, Goldrigger

    The macro below, called "Walkenbach," extracts the value from cell A1 (R1C1), B2 (R2C2), etc., from a closed file "source.xls"; the closed workbook and the "host" workbook are here in the same folder: adjust it to your case and your cells.

    Sub read_closed()

    Dim path As String

    path = ThisWorkbook.Path
    Range("A3") = ExecuteExcel4Macro("'" & path & "\[source.xls]Sheet1'!R1C1")
    Range("B4") = ExecuteExcel4Macro("'" & path & "\[source.xls]Sheet1'!R2C2")
    Range("C8") = ExecuteExcel4Macro("'" & path & "\[source.xls]Sheet1'!R5C3")
    Range("E9") = ExecuteExcel4Macro("'" & path & "\[source.xls]Sheet1'!R7C4")
    End Sub

    Good evening
    Michel
    0
  4. goldrigger
     
    Hi michel_m,

    actually it's a good solution but for each piece of data, we need to validate the source file. If there is a lot of data, it can take time.

    Thank you.
    Have a nice day.
    0