Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
named cell value from closed workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
named cell value from closed workbooks
-----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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Data from Closed Workbooks | Excel Worksheet Functions | |||
Copying From Closed Workbooks | Excel Worksheet Functions | |||
Information from Closed workbooks | Excel Discussion (Misc queries) | |||
How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions |