View original (French)

Data retrieval from an Excel workbook

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
    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
    1. 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
      1. michel_m Contributor
        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
        1. 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