named cell value from closed workbooks

[Chris A]

I am trying to search a directory where we keep several different workbooks,
they all contain the same named range, or rarther single cell named.
however, the cells are in differing locations. The workbooks are closed and
i want to bring into the current workbook the named cells.
The code is a re-hash of something i found on exceltips, I'm stuck here, i
get an error at the .Address(True, True, xlR1C1) part, "method range of
object_worksheet failed"
I think it's trying to reference an absolute but i am struggling to get it
looking for the named cells.
To be honest there are parts that i still don't understand.
Hope someone has some light to throw on it.
Thanks alot
Chris A


Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

' I'm using the code below to add the values to the workbook

Sub GetInfo()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant,
Cval2 As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:\Foldername"
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
'Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1",
"ordnum").RefersToRange
Cval2 = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1",
"ctact").RefersToRange
Cells(r, 1).Value = wbList(i)
Cells(r, 2).Value = cValue
Cells(r, 3).Value = Cval2
Next i
End Sub

 

[GJones]

-----Original Message-----

I am trying to search a directory where we keep several different workbooks,
they all contain the same named range, or rarther single cell named.
however, the cells are in differing locations. The workbooks are closed and
i want to bring into the current workbook the named cells.
The code is a re-hash of something i found on exceltips, I'm stuck here, i
get an error at the .Address(True, True, xlR1C1) part, "method range of
object_worksheet failed"
I think it's trying to reference an absolute but i am struggling to get it
looking for the named cells.
To be honest there are parts that i still don't understand.
Hope someone has some light to throw on it.
Thanks alot
Chris A


Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

' I'm using the code below to add the values to the workbook

Sub GetInfo()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant,
Cval2 As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:\Foldername"
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
'Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList (i), "Sheet1",
"ordnum").RefersToRange
Cval2 = GetInfoFromClosedFile(FolderName, wbList (i), "Sheet1",
"ctact").RefersToRange
Cells(r, 1).Value = wbList(i)
Cells(r, 2).Value = cValue
Cells(r, 3).Value = Cval2
Next i
End Sub



.
Make sure the range your going after is in the

activeworkbook. You can do Range("MyRangeName").select
Selection.copy and the go to where you want it and paste
it.

There are more sofisticated methods but this should get
you started.

Thanks,
Greg