Retrieving a value at some cell from another Workbook

[Joao]

I want to retrieve some values from an existing Workbook to another one (Two
..XLS files)

What I am doing works, but isn't quite what I want.
After I retrieve a value on a workbook in another directory from other
workbook in a different directory, I get the value on the cell, but if you
mouseover the function it displays the following link:
='D:\directory\[excelfile.xls]Accounting'!$A$12

****I want the value, not the formula I use to get the data. Does .Evaluate
works?****

I use this code to retrieve and save the data:
Public Sub procInserirDados()
....
For n = 7 To 13
h = n
For Each Dados In objFolha(2).Range("A" & n)
objFolha(2).Range("A" & n).Value = _
"='" & strDirectory & "[" & strFile & "]" & strWorkbook &
"'!$A$" & h + 1
Next Dados
Next n
....
end sub

 

[Joel]

The best way is to open the workbook and get the values. Youcould also after
insterting the formula perform a copy and then Paste Values only

Here is code to open the workbook

Public Sub procInserirDados()
'...
Myfile = strDirectory & "\" & strFile
MySheet = strWorkbook

Workbooks.Open Filename:=Myfile
Set oldbk = ActiveWorkbook
With oldbk.Sheets(MySheet)
For n = 7 To 13
h = n
For Each Dados In ThisWorkbook.objFolha(2).Range("A" & n)
ThisWorkbook.objFolha(2).Range("A" & n).Value = _
.Range("A" & (h + 1))
Next Dados
Next n
oldbk.Close savechanges:=False
'...
End Sub

 

[Joao]

Thank you Joel, it works fine, however there's no other way to do it without
opening the retrieving workbook?

Thanks anyway - Big help solved it.

The best way is to open the workbook and get the values. Youcould also after
insterting the formula perform a copy and then Paste Values only

Here is code to open the workbook

Public Sub procInserirDados()
'...
Myfile = strDirectory & "\" & strFile
MySheet = strWorkbook

Workbooks.Open Filename:=Myfile
Set oldbk = ActiveWorkbook
With oldbk.Sheets(MySheet)
For n = 7 To 13
h = n
For Each Dados In ThisWorkbook.objFolha(2).Range("A" & n)
ThisWorkbook.objFolha(2).Range("A" & n).Value = _
.Range("A" & (h + 1))
Next Dados
Next n
oldbk.Close savechanges:=False
'...
End Sub

I want to retrieve some values from an existing Workbook to another one (Two
.XLS files)

What I am doing works, but isn't quite what I want.
After I retrieve a value on a workbook in another directory from other
workbook in a different directory, I get the value on the cell, but if you
mouseover the function it displays the following link:
='D:\directory\[excelfile.xls]Accounting'!$A$12

****I want the value, not the formula I use to get the data. Does .Evaluate
works?****

I use this code to retrieve and save the data:
Public Sub procInserirDados()
...
For n = 7 To 13
h = n
For Each Dados In objFolha(2).Range("A" & n)
objFolha(2).Range("A" & n).Value = _
"='" & strDirectory & "[" & strFile & "]" & strWorkbook &
"'!$A$" & h + 1
Next Dados
Next n
...
end sub

 

[Joel]

As I said before. You can add the formula the way you did before. Then copy
the values and use paste special pasting only the values. This will remove
the formulas.

Thank you Joel, it works fine, however there's no other way to do it without
opening the retrieving workbook?

Thanks anyway - Big help solved it.

The best way is to open the workbook and get the values. Youcould also after
insterting the formula perform a copy and then Paste Values only

Here is code to open the workbook

Public Sub procInserirDados()
'...
Myfile = strDirectory & "\" & strFile
MySheet = strWorkbook

Workbooks.Open Filename:=Myfile
Set oldbk = ActiveWorkbook
With oldbk.Sheets(MySheet)
For n = 7 To 13
h = n
For Each Dados In ThisWorkbook.objFolha(2).Range("A" & n)
ThisWorkbook.objFolha(2).Range("A" & n).Value = _
.Range("A" & (h + 1))
Next Dados
Next n
oldbk.Close savechanges:=False
'...
End Sub

I want to retrieve some values from an existing Workbook to another one (Two
.XLS files)

What I am doing works, but isn't quite what I want.
After I retrieve a value on a workbook in another directory from other
workbook in a different directory, I get the value on the cell, but if you
mouseover the function it displays the following link:
='D:\directory\[excelfile.xls]Accounting'!$A$12

****I want the value, not the formula I use to get the data. Does .Evaluate
works?****

I use this code to retrieve and save the data:
Public Sub procInserirDados()
...
For n = 7 To 13
h = n
For Each Dados In objFolha(2).Range("A" & n)
objFolha(2).Range("A" & n).Value = _
"='" & strDirectory & "[" & strFile & "]" & strWorkbook &
"'!$A$" & h + 1
Next Dados
Next n
...
end sub