Extracting data from a closed workbook

[Barb Reinhardt]

I have a macro which defines the active workbook as aWB and the open
workbook as oWB. I'd like to define cell data as in the active workbook
based on what's in the open workbook.

I'd like to have something like this:

Cells(i,"H").Value = formula with info from oWB.

It looks something like this:

=MATCH(G$3,[Book1]Sheet1!$33:$33) in the EXCEL worksheet.

Where [Book1] is the open workbook.

How do I write this formula so that I get the VALUE in the listed cell
instead of the formula? I can get the formula, but want the value.

Alternatively, I can copy paste/special each row of data before I close
oWB, but if there's a more elegant way, I'd like to use it.

Thanks in advance.

Barb Reinhardt

 

[Guest]

I used this hope it helps?.
Regards
Charles

Sub TestGetValue()
'
p = Range("A1").Value
f = Range("A2").Value
s = Range("A3").Value
a = Range("A4").Value
MsgBox GetValue(p, f, s, a)
End Sub


Public Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)

End Function

 

[Guest]

Entering the formula programmatically and then using Copy + Paste Special...
| Values is probably the most reliable way to go. Of course, you don't have
to operate on one cell at a time. Instead, operate on entire ranges at one
go.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach