Have a look at Ron de Bruin's code to retrieve data from a closed workbook.

Adapt to suit.

http://www.rondebruin.nl/copy7.htm





On Sun, 18 Jan 2009 10:30:12 -0800 (PST), Jeffrey
wrote:

Hi Everyone,

Is anyone out there that can help me. I have two workbooks. The first
workbook is open and the second workbook is close. The first workbook
contains my product code and the cost. the second workbook contains
my product code with the MOST up to date cost. I manage to create a
macro which returns the MOST up to date cost in my first workbook. But
my code is opening the second workbook. I wonder if there is a way
that i dont have to open the second workbook but still manage to do
the cross referencing?

Please advise. Below is the code.

thanks much.


Sub CostUpdate()
'
' CostUpdate Macro
' Macro recorded 13/01/2009 by jeffreyst
'

Application.ScreenUpdating = False

Dim activeWorkbookName As String

activeWorkbookName = ActiveWorkbook.Name

Workbooks.Open Filename:="G:\Commercial Auckland\Estimating Dept\Bpcs
Cost\z UpdatedCost.xls"

Dim active As Workbook

Dim sourceRow As Long
Dim destination As Long
Dim code As Long
Dim cost As Double

sourceRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row
For i = 2 To sourceRow
Windows("z UpdatedCost.xls").Activate
code = Range("a" & i).Value
cost = Range("c" & i).Value

Windows(activeWorkbookName).Activate

destination = Sheets("MaterialDatabase").Range("b65536").End
(xlUp).Row

Dim cont As Boolean
Dim count As Integer

cont = True
count = 3

Do While cont
Dim matchCode As Long
matchCode = Sheets("MaterialDatabase").Range("B" &
count).Value

If matchCode = code Then
Sheets("MaterialDatabase").Range("C" & count).Value = cost
cont = False
End If

count = count + 1
If count destination Then
cont = False
End If

Loop

Next i

Windows("z UpdatedCost.xls").Activate
'Application.CutCopyMode = False
ActiveWorkbook.Close

End Sub