Extracting data from a closed workbook
 

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

Extracting data from a closed workbook
 

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


"Barb Reinhardt" wrote:

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

Extracting data from a closed workbook
 

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


"Barb Reinhardt" wrote:

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