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 réponses

Réponse 1 / 4
tenace Posted messages 840 Status Membre 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
Réponse 2 / 4
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
Réponse 3 / 4
michel_m Posted messages 18903 Registration date   Status Contributeur 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
Réponse 4 / 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