Appendix Thread. ( Codes for other Threads, ( Avinash ).)

[DocAElstein]

Second main Demo Code in support of this Thread:
http://www.excelfox.com/forum/showth...-a-named-range
For Posts from:
http://www.excelfox.com/forum/showth...0819#post10819

Sub FoxyMultiCellNamedRanges()
10   Rem -2 Range Info etc.
20   Dim WbMain As Workbook, dataWb1xls As Workbook, dataWb2xlsx As Workbook
30    Set WbMain = Workbooks("MasturFile.xlsm") 'Set WbMain = ThisWorkbook
40    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
50    Set dataWb1xls = Workbooks("Data1.xls")
60    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
70    Set dataWb2xlsx = Workbooks("Data2.xlsx")
80   '
90   Dim LisWkBkPath As String: Let LisWkBkPath = "=" & "'" & ThisWorkbook.Path & "\"
100  '-2b) Some variables to hold a full reference string which we will use in places where we might need any of these variations for a cell reference  Sheet7!B5  [myWorkbook.xlsm] Sheet4!B5  'G:\Desktop\MyFolder\[DataFile.xlsx]Tabelle1'!B5   The last one is the form we hold in the variables. Excel and Excel VBA , usually has no issues if you use the full reference in situations where one of the shorter versions may have been sufficient. But on the other hand,  you may get unexpected problems if you used a shorter version , and Excel then  guesses wrongly  for the remaining part, which I believe it always adds internally, ( possibly at some compiling stage ) , before it uses it.
110  Dim MBkTab1B5 As String ' To hold full string reference to B5 in Master Workbook
120   Let MBkTab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "MasturFile.xlsm" & "]" & "Tabelle1" & "'" & "!" & "B5"
130  Dim Dat1Tab1B5 As String ' B5 in data1 workbook
140   Let Dat1Tab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "Data1.xls" & "]" & "Tabelle1" & "'" & "!" & "B5"
150 '
160  Rem -1 Error handler
170   On Error GoTo ErrorHandlerCodeSection:
180  GoTo PastErrorHandler
190 ErrorHandlerCodeSection:
200   MsgBox prompt:="Code errored at line  " & Erl & " , error was:" & vbCrLf & vbCrLf & Err.Number & "     " & Err.Description
210   Debug.Print Err.Number & "     " & Err.Description
220   Resume Next
230 PastErrorHandler:
240  Rem 0 Clean up
250  '0a) remove any name objects made in last routine in the main file or the two data files
260  Dim WkBk As Workbook
270      For Each WkBk In Workbooks
280       Call FukYaWkBkNames(WkBk)
290       'Call GeTchaNms(280, WkBk)
300      Next WkBk
310   Workbooks("Data1.xls").Close savechanges:=True
320   Workbooks("Data2.xlsx").Close savechanges:=True
330  '0b) clear the entire data ranges in the first worksheet in the main workbook, both headers and data
340   ThisWorkbook.Worksheets.Item(1).Range("B5:C12").ClearContents
350  Rem _1) Data1 "Food" header
360  '1a) Data1 cell Workbook Scoped to its workbook : Info needed for a range in that data file is held in the workbooks name objects collection object of that workbook
370   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
380   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
390   dataWb1xls.Names.Add Name:="Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5) ' A personal preference of mine is , once again, to use a full reference. This time it is  in the Refers To range. This Refers To:= argument would never need the full file path reference, as the range referenced must be to a range in an open book. Never the less, as usual, VBA accepts the full reference
400   dataWb1xls.Close savechanges:=True ' I don't need the workbook open for the next line to work, but I made Added a named range object so I must save the changes for the next line to work as that named range is referenced
410   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' "Going" to Workbook  Data1.xls
420   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle4'!Dta1Foodheader" ' "Going" to any worksheet in  Data1.xls
430  Rem 2 Experiments with named ranges in the LHS , like in Range("rngNamed") =
440  '2b) Workbooks Scope to main workbook: Info for named range is in Name Objects collection of Main workbook
450   WbMain.Names.Add Name:="MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
460   Let Application.Range(LisWkBkPath & WbMain.Name & "'!MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx      RHS is "Going" to Workbook  Data1.xls
470  Rem 3 Bring in Header "Suppliment" from data 2 workbook directly without named ranges
480   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
490   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Needed for next line
500   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
510  '3b) "Fixed vector" B11 into main workbook at B11
520   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B11").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B11"
530  '3c) "Fixed vector" B11 into main workbook into  B11 C11 B12 and C12
540   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B11:C12").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B11"
550   dataWb2xlsx.Close savechanges:=False
560 '
570   Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B11:C12").ClearContents ' remove the data from the main file from data file 2 so as to do the same again using named ranges in the next code section, Rem 4
580  Rem 4 named ranges for data ranges in data workbooks and main file
590  '4a) Workbook to store name range object
600  Dim WbNmeObjs As Workbook
610   Workbooks.Open Filename:=ThisWorkbook.Path & "\StoredNamedRangeNameObjects.xls"
620   Set WbNmeObjs = Workbooks("StoredNamedRangeNameObjects.xls")
630   Call FukYaWkBkNames(WbNmeObjs)
640   Call GeTchaNms(640, WbNmeObjs)
650  '4b) named ranges for data in data range from data 1 workbook, "Data1.xls
660   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
670   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
680   WbNmeObjs.Worksheets("DataFileNameObjects").Names.Add Name:="NmsObjDta1Data", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[Data1.xls]Tabelle1'!B6:C7")
690   Call GeTchaNms(690, WbNmeObjs)
700  '4c) named ranges for data in data range from data 2 workbook, "Data2.xlsx
710   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
720   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' We need this open for the referred to range in the RefersTo:= range reference below
730   WbNmeObjs.Worksheets("DataFileNameObjects").Names.Add Name:="NmsObjDta2Data", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[Data2.xlsx]Tabelle1'!B11:C12")
740   Call GeTchaNms(740, WbNmeObjs)
750  '4d) named ranges for data import ranges in main workbook, ( This workbook )
760  '4d(i) data from Data 1 file import range in main book
770   WbNmeObjs.Worksheets("MainFileNameObjects").Names.Add Name:="NmsObjDta1Import", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[MasturFile.xlsm]Tabelle1'!B6:C7")
780  '4d(ii) data from Data 2 file import range in main book
790   WbNmeObjs.Worksheets("MainFileNameObjects").Names.Add Name:="NmsObjDta2Import", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[MasturFile.xlsm]Tabelle1'!B11:C12")
800  Call GeTchaNms(800, WbNmeObjs)
810  ' Close data books - I don't need them open to get at their named range data or their named range data
820   dataWb1xls.Close savechanges:=False ' I needed the workbook open for the referes to range reference and the  GeTchaNms( )  to work, but i added no names to it, so I did not intentiionally make any changes, so I will close with changes false in case I acidentally changed anything
830   dataWb2xlsx.Close savechanges:=False ' I needed the workbook open for the referes to range reference and the  GeTchaNms( )  to work, but i added no names to it, so I did not intentiionally make any changes, so I will close with changes false in case I acidentally changed anything
840  Rem 5 Using the Added data named ranges to bring in data from the data files into the main workbook
850  '5a) Food data data range ( B6:C7 in main File and B6:C7 in data 1 file )
860   Let Application.Range("='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import").FormulaArray = "='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
870  '5a)(ii) As file "StoredNamedRangeNameObjects.xls" is open we can also use
880   Let Application.Range("='[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import").FormulaArray = "='[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
890  '5b) Food data data range ( B11:C12 in main File and B11:C12 in data 2 file )
900   Let Application.Range("='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import").FormulaArray = "='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data"
910  '5b)(ii) As file "StoredNamedRangeNameObjects.xls" is open we can also use
920   Let Application.Range("='[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import").FormulaArray = "='[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data"
930  '5c)
940   WbNmeObjs.Close savechanges:=True ' Save the named range info on closing
950  '5d) Optional Change all formulas to their values
960   Let WbMain.Worksheets.Item(1).UsedRange.Value = WbMain.Worksheets.Item(1).UsedRange.Value
970  Rem 6 Final check of all named ranges
980  '6a) Open all workbooks so as to access Named range objects in them
990   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
1000   Set dataWb1xls = Workbooks("Data1.xls")
1010  Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
1020  Set dataWb2xlsx = Workbooks("Data2.xlsx")
1030   Workbooks.Open Filename:=ThisWorkbook.Path & "\StoredNamedRangeNameObjects.xls"
1040  Set WbNmeObjs = Workbooks("StoredNamedRangeNameObjects.xls")
1050 '6b) Loop through all open workbooks and check named range object info
1060 Dim Wbtemp As Workbook
1070     For Each Wbtemp In Workbooks ' Going through each workbook in the Workbooks collection object of open workbooks
1080      Call GeTchaNms(1080, Wbtemp)
'1085        If Wbtemp.Name <> ThisWorkbook.Name Then Wbtemp.Close savechanges:=False ' Close all but this workbook - can't do this here - I might need them in the next use of GeTchaNms
1090     Next Wbtemp
      'close workbooks
1100     For Each Wbtemp In Workbooks ' Going through each workbook in the Workbooks collection object of open workbooks
1110       If Wbtemp.Name <> ThisWorkbook.Name Then Wbtemp.Close savechanges:=False ' Close all but this workbook
1120     Next Wbtemp

End Sub

[DocAElstein]

Support Called routines for Thread:
http://www.excelfox.com/forum/showth...-a-named-range

Sub FukYaWkBkNames(ByVal WnkBuk As Workbook)
Dim Nme As Name
    For Each Nme In WnkBuk.Names
     Nme.Delete
    Next Nme
End Sub
Sub GeTchaNms(ByVal CodLn As Long, ByVal WnkBuk As Workbook) ' To get info aboout all Name objects in a Workbook,m WnkBuk
Dim Cnt As Long, Nme As Name, strOut As String
' Name objects in Workbook Names Colection object (Workbooks scope and Worksheets scope)
     For Each Nme In WnkBuk.Names '   For convenience it goes through the Workbook named  objects collection object  for a workbook, as this has "its own" named range objects, that is to say the Workbooks scoped named range objects, and also the  named range objects for all the worksheets. So I do not need to go through the named range objects collection object of every worksheet in that workbook separately for every worksheet.
      Let Cnt = Cnt + 1 ' A simple count number of each workbooks collection names objects in order it finds in looping them
      ' We look now for a "!" in the string name, ...  Excel adds a bit onto the name we give to a name Added to a Worksheet’s named objects collection ( Scoped to a Worksheet’s named objects collection = worksheet “scoping”     We scoped to the Names object of a particular Worksheet = We Added the named range Name object to the names objects collection object of that particular Worksheet( and also indirectly  the names objects collection object of the workbook in which that worksheet is) = We scoped that named range to that Workbook = That named range has Workbook Scope  ). That added bit is something like “Sheet1!” . In other words, if you had given Name:=”MyName” in a code line for a worksheets scope Named range object Addition, like, …_   Worksheets("Sheet2").Names.Add Name:="FoodHeader", RefersTo:=____    _.. Then excel seems to hold and use a name like “Sheet2!FoodHeader"
        If InStr(1, Nme.Name, "!", vbBinaryCompare) > 0 Then ' A name for a worksheet scope, has an extra bit added onto the name we gave it which includes a "!"
         Let strOut = strOut & Cnt & "  Name object Name is  """ & Nme.Name & """" & vbCrLf & "(you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheet scope and" & vbCrLf & "it refers to range  """ & Nme.RefersTo & """" & vbCrLf & "and if in a spreadsheet formula you only want to use" & vbCrLf & """" & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """  without any preceding info about" & vbCrLf & "where that named range is," & vbCrLf & "then you must  be in spreadsheet with tab name  """ & Nme.Parent.Name & """" & vbCrLf & "If you want to be sure to access this named range from anywhere," & vbCrLf & "you should use   """ & "=" & "'" & WnkBuk.Path & "\" & "[" & WnkBuk.Name & "]" & Nme.Parent.Name & "'" & "!" & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """"
            If Nme.Parent.Name <> Application.Range(Nme.RefersTo).Parent.Name Then Let strOut = strOut & vbCrLf & "Note: The refered to range is in worksheet  """ & Application.Range(Nme.RefersTo).Parent.Name & """"
            If Nme.Parent.Parent.Name <> Application.Range(Nme.RefersTo).Parent.Parent.Name Then Let strOut = strOut & vbCrLf & "Note also: The refered to range is in File  """ & Application.Range(Nme.RefersTo).Parent.Parent.Name & """"
        Else ' Assume we have a workbook scoped name... we will see that a name for a workbook scope, remains just as we gave it
         Let strOut = strOut & Cnt & "  Name object Name is  """ & Nme.Name & """ (the same as you gave)" & vbCrLf & "It has workbook scope and" & vbCrLf & "it refers to range  """ & Nme.RefersTo & """" & vbCrLf & "and if in a spreadsheet formula you only want to use" & vbCrLf & """" & Nme.Name & """" & vbCrLf & "with no preceding info " & vbCrLf & "about where that named range is," & vbCrLf & "then you must be in any spreadsheet in workbook  """ & Nme.Parent.Name & """" & vbCrLf & "If you want to be sure to access this named range from anywhere," & vbCrLf & "you should use   """ & "=" & "'" & WnkBuk.Path & "\" & WnkBuk.Name & "'" & "!" & Nme.Name & """" & vbCrLf & "or alternatively use a similar string like this with any of the worksheets in it:" & vbCrLf & """" & "=" & "'" & WnkBuk.Path & "\" & "[" & WnkBuk.Name & "]" & WnkBuk.Worksheets.Item(1).Name & "'" & "!" & Nme.Name & """"
            If WnkBuk.Name <> Nme.Parent.Name Then Let strOut = strOut & vbCrLf & "Note the refered to range is in" & vbCrLf & """" & Application.Range(Nme.RefersTo).Parent.Parent.Name & """  worksheets  """ & Application.Range(Nme.RefersTo).Parent.Name & """  !!"
        End If
     Let strOut = strOut & vbCrLf & vbCrLf & vbCrLf ' To clearly seperate each name object
    Next Nme
    If strOut = "" Then
     MsgBox prompt:="The workbooks names object collection object is empty," & vbCrLf & "and so there are no named range objects in" & vbCrLf & "workbook   """ & WnkBuk.Name & """", Title:="At " & CodLn & " , for File  """ & WnkBuk.Name & """": Debug.Print "'_= ========" & vbCrLf & "You have no named range Name objects in workbook " & WnkBuk.Name & vbCrLf & vbCrLf
    Else
     MsgBox prompt:=strOut, Title:="At " & CodLn & " , """ & WnkBuk.Name & """ Names Collection has:-": Debug.Print "'_= ========" & vbCrLf & "You have " & Cnt & " named range Name objects in workbook " & WnkBuk.Name & vbCrLf & strOut
    End If
End Sub

[DocAElstein]

In support of this post:
http://www.excelfox.com/forum/showth...0814#post10814


_____ Workbook: MasturFile.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D
1
2
3
4		Nutrition	Energy
5		Food
6		Orange	50
7		Apfel	60
8
9
10		Suppliment
11		BCAA	398
12		EAA	400
13
14
15

Worksheet: Tabelle1

[DocAElstein]

I am trying to do 2 things: Use 2 named ranges.. One works. The other doesn’t.
I have made a demo to help explain my problem
I have 3 Files: I have a Main Excel workbook file, usually open, and two other files, usually closed
_Main File is:- “Main.xls” https://app.box.com/s/u8yy4rcqg0eglvy362v13hyro8cgd9n7 – - This is usually open. It has all my codes in it
_A DataFile is:- “ClsdData.xls.” https://app.box.com/s/65w1hnih1vvay70vtdzk3da50we3gxvh – This is usually closed. It has 2 data ranges and one named range name object in it
ClsdDataDataRanges.JPG : https://imgur.com/vs0vX0G
_____ Workbook: ClsdData.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	dataA1	dataB1
2

Worksheet: DataSht_1

_A third file is:- “NameObjectFile.xls” https://app.box.com/s/wsxycb3t2y1hmv0wr12cqav0qlcytzjn – This is usually closed, ( preferably ). It only has a named range name object in it

So the goal is to have a main file, “Main.xls” open whilst the files “ClsdData.xls.” and “NameObjectFile.xls” are closed, and from a code in the main file, “Main.xls” , put formulas of this sort of form in the first two cells of the main workbook.
NamedRangeReferrenceFormulasPutInMainFile.JPG : https://imgur.com/1wDM3ug
_____ Workbook: Main.xls ( Using Excel 2007 32 bit )

Row\Col	A	B
1	= ' C: \ FolderPath \ [ClsdData.xls] DataSht_1 ' ! NameForDataSht_1A1	= ' C: \ FolderPath \ [NameObjectFile.xls] NameObjectsSht_1 ' ! NameForDataSht_1B1

Worksheet: Tabelle1
Those formulas “go” to the name objects of the named ranges with string names:
“ NameForDataSht_1A1” referring to the range of data file first cell ,
and
“NameForDataSht_1B1” referring to the range of data file second cell
The result of those formulas should then be to have the actual seen values in those two cells as:
MainFileDataIn.JPG : https://imgur.com/vQlhedZ
_____ Workbook: Main.xls ( Using Excel 2007 32 bit )

Row\Col	A	B
1	dataA1	dataB1

Worksheet: Tabelle1
( I have determined that, contrary to much literature, you can actually access a named range from anywhere as long as you include the full path and full string range name: the so called “scope” only determines the default path that Excel uses if you only give the string range name )

_._____________________
Demo Code:
(This code is in File: “Main.xls” )
With all the files in the same Folder, this code can be used to make the two named range Name objects. ( I put one named range Name object in the first worksheet of the file: “ClsdData.xls” and the other named range Name object in the first worksheet of the file: “NameObjectFile.xls” ).
The code also tries to access the first two cells values from the closed workbook using named ranges in these two code lines: The code lines put in those two long named range reference formulas

 '_1 
Workbooks("Main.xls").Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[ClsdData.xls]DataSht_1'!NameForDataSht_1A1"

and

'_2 
Workbooks("Main.xls").Worksheets.Item(1).Range("B1").Value = "='" & ThisWorkbook.Path & "\[NameObjectFile.xls]NameObjectsSht_1'!NameForDataSht_1B1"

.
Those are the two things I am trying to do.
That last code line fails.
That last code line does not fail if I have the workbook “NameObjectFile.xls” open
Full Code:

Sub Make2NamedRangeObjectsAndTryToUseEm()
' scope named range to first worksheet's collection of Name objects object of Workbook "ClsdData.xls"
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "ClsdData.xls"
 'Let Workbooks("ClsdData.xls").Worksheets.Item(1).Name = "DataSht_1"
 Workbooks("ClsdData.xls").Worksheets("DataSht_1").Names.Add Name:="NameForDataSht_1A1", RefersTo:=Workbooks("ClsdData.xls").Worksheets("DataSht_1").Range("A1")
 Workbooks("ClsdData.xls").Close savechanges:=True ' Save Added name object
'_1 access first cell in closed data workbook from main file using named range name object with string name "NameForDataSht_1A1
 Let Workbooks("Main.xls").Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[ClsdData.xls]DataSht_1'!NameForDataSht_1A1"
 Workbooks("Main.xls").Save
' scope named range to first worksheet's collection of Name objects object of Workbook "NameObjectFile.xls "
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "NameObjectFile.xls"
 'Let Workbooks("NameObjectFile.xls").Worksheets.Item(1).Name = "NameObjectsSht_1"
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "ClsdData.xls" ' Needed for RefersTo below
 Workbooks("NameObjectFile.xls").Worksheets("NameObjectsSht_1").Names.Add Name:="NameForDataSht_1B1", RefersTo:=Workbooks("ClsdData.xls").Worksheets("DataSht_1").Range("B1")
 Workbooks("ClsdData.xls").Close savechanges:=False ' No changes made - was only needed for RefersTo above
 Workbooks("NameObjectFile.xls").Close savechanges:=True ' Save Added name object
'_2 access second cell in closed dataworkbook from main file using named range name object with string  NameForDataSht_1B1
 Let Workbooks("Main.xls").Worksheets.Item(1).Range("B1").Value = "='" & ThisWorkbook.Path & "\[NameObjectFile.xls]NameObjectsSht_1'!NameForDataSht_1B1"

End Sub

_.__________
Let me put again into words what I am doing. I am doing two things:

'_1 This works: I have a closed data workbook, ("ClsdData.xls" ). That has a named range, ( string name is “NameForDataSht_1A1” ) . That name, “NameForDataSht_1A1” , is for the first cell in that closed data workbook, ("ClsdData.xls" ). That named range is scoped to the first worksheet in that closed data file, (closed data workbook, ("ClsdData.xls" ) . In other words, the named range object with string name “NameForDataSht_1A1” is in the first worksheets name objects collection of the closed data workbook ( "ClsdData.xls" ). This named range object with string name “NameForDataSht_1A1” refers to the first cell, A1, in the closed data workbook, ("ClsdData.xls" ).

'_2 This does not work , ( unless file "NameObjectFile.xls" is open ). I am using a file, ( "NameObjectFile.xls" ), only for holding name range objects. It has one named range name object in it which has the string name "NameForDataSht_1B1". This is the name range object for the second cell in the closed data workbook, ("ClsdData.xls" ). In other words, the named range object with string name “NameForDataSht_1B1” is in the first worksheets name objects collection of the workbook “NameObjectFile.xls”. This named range object with string name “NameForDataSht_1B1” refers to the second cell, B1, in the closed data workbook, ("ClsdData.xls" ).

I don’t understand yet why '_2 does not work. I am not totally sure why '_1 does work either.
I guess I don’t really understand exactly what I am doing. I don’t really understand what is really going on in the two cases.

I am thinking that I should be able somehow to get the string reference information that I require , that is to say, for the right hand side of the last equation I have this:
"='" & ThisWorkbook.Path & "\[NameObjectFile.xls]NameObjectsSht_1'!NameForDataSht_1B1"
But somehow I am thinking that I should be able to get the referred to string reference of
"='" & ThisWorkbook.Path & "\ [ClsdData.xls]DataSht_1'!$A$1"

[DocAElstein]

I did this..
Took file “NameObjectFile.xls”,
first save as .xlsx,
then save as .zip ( “NameObjectFile - Kopie.zip” : https://app.box.com/s/ih9k6o7s5f3vkb21jyyso0mcqoh82isb )
and then double click on it and get this: NameObjectFile_xls_xlsx_zip.JPG : https://imgur.com/iAVFSOh




I get stuff like this:
_____ Workbook: Main.xls ( Using Excel 2007 32 bit )

NameObjectsFileAsZip	NameObjectsFileAsZip
	[Content_Types].xml
NameObjectsFileAsZip\docProps		docProps
		app.xml
		core.xml
		thumbnail.wmf
NameObjectsFileAsZip\xl		xl
		styles.xml
		workbook.xml
NameObjectsFileAsZip\xl\externalLinks			externalLinks
			externalLink1.xml
NameObjectsFileAsZip\xl\externalLinks\_rels				_rels
				externalLink1.xml.rels
NameObjectsFileAsZip\xl\theme			theme
			theme1.xml
NameObjectsFileAsZip\xl\worksheets			worksheets
			sheet1.xml
NameObjectsFileAsZip\xl\_rels			_rels
			workbook.xml.rels
NameObjectsFileAsZip\_rels		_rels
		.rels

Worksheet: NameObjectsFileAsZip

NameObjectsFileAsZip_NameObjectsFileAsZip
_____________________[Content_Types].XML Content Types--xml.jpg . https://imgur.com/n9FQUxR
________________
NameObjectsFileAsZip\docProps_______docProps docProps.JPG : https://imgur.com/SRBBdyg
____________________________________app.XML app xml.JPG : https://imgur.com/qeeWrpm
____________________________________core.XML core xml.JPG : https://imgur.com/jZ3iSo7
____________________________________thumbnail.wmf
________________
NameObjectsFileAsZip\xl_____________xl xl.JPG : https://imgur.com/408pO7A
____________________________________Styles.XML styles xml.JPG : https://imgur.com/71fDgcw
____________________________________Workbook.XML workbook xml.JPG : https://imgur.com/AJ3et9N
________________
NameObjectsFileAsZip\xl\externalLinks___________externalLinks externalLinks.JPG : https://imgur.com/SPj3lZY
________________________________________________ex ternalLink1.XML externalLink1 xml rels.JPG : https://imgur.com/qHnFz7u
________________
NameObjectsFileAsZip\xl\externalLinks\_rels______________rels _ rels.JPG : https://imgur.com/GwEBoFG
__________________________________________________ _______externalLink1.XML.rels externalLink1 xml rels.JPG : https://imgur.com/qHnFz7u
________________
NameObjectsFileAsZip\xl\theme___________________theme theme.JPG : https://imgur.com/KyceI30

________________________________________________th eme1.XML theme1 xml.JPG : https://imgur.com/hGgsgOQ
________________
NameObjectsFileAsZip\xl\worksheets______________worksheets worksheets.JPG : https://imgur.com/D8hqFpr
________________________________________________sh eet1.XML Sheet1 xml.JPG : https://imgur.com/ycxiL62
________________
NameObjectsFileAsZip\xl\_rels____________________rels _ rels.JPG https://imgur.com/u84DcoX

________________________________________________Wo rkbook.XML.rels workbook xml rels.JPG : https://imgur.com/L8fNakM
________________
NameObjectsFileAsZip\_rels___________rels _rels.JPG https://imgur.com/Tahoick
____________________________________.rels rels.jpg . https://imgur.com/pWaSeIo

[DocAElstein]

I took this, “ClsdData.xls” , saved it as “ClsdData.xlsx” ,
then changed it to “ClsdData.zip” ,
closed it,
then double clicked on it and get this:
ClsdDataZip.JPG : https://imgur.com/oUtHu34
I copied all that to one folder,
and put that Folder in another folder:
copied all that to one folder, and put that Folder in another folder.JPG : https://imgur.com/an58FA7
I ran the code Sub DoStuffInFoldersInFolderRecursion() which is in the uploaded version of “Main.xls” , and that gives a Folder and File tree something like this if you select one of the above folders when it asks you to select a Folder:
_____ Workbook: Main.xls ( Using Excel 2007 32 bit )

FolderForClsdDataZipContents	FolderForClsdDataZipContents
	[Content_Types].xml
FolderForClsdDataZipContents\docProps		docProps
		app.xml
		core.xml
		thumbnail.wmf
FolderForClsdDataZipContents\xl		xl
		sharedStrings.xml
		styles.xml
		workbook.xml
FolderForClsdDataZipContents\xl\theme			theme
			theme1.xml
FolderForClsdDataZipContents\xl\worksheets			worksheets
			sheet1.xml
FolderForClsdDataZipContents\xl\_rels			_rels
			workbook.xml.rels
FolderForClsdDataZipContents\_rels		_rels
		.rels

Worksheet: ClsdDataZipTree



'FolderForClsdDataZipContents_FolderForClsdDataZip Contents
'__________________________[Content_Types].XML
'
'FolderForClsdDataZipContents\docProps_______docPr ops docProps.JPG : https://imgur.com/6i1gIK4
'____________________________________________app.X ML app XML.JPG : https://imgur.com/XxiZCL9
'____________________________________________core. XML core XML.JPG : https://imgur.com/BwQxqi6
'____________________________________________thumb nail.wmf
'
'FolderForClsdDataZipContents\xl_____________xl xl.JPG : https://imgur.com/YxJFYV4
'____________________________________________share dStrings.XML sharedStrings XML.JPG : https://imgur.com/7dSdvM6
'____________________________________________Style s.XML Styles XML.JPG : https://imgur.com/whytQOj
'____________________________________________Workb ook.XML Workbook XML.JPG: https://imgur.com/P3G2qNC
'
'FolderForClsdDataZipContents\xl\theme____________ theme theme.JPG : https://imgur.com/Vj2RSyM
'_________________________________________________ theme1.XML theme1 XML.JPG : https://imgur.com/zimRsPL
'
'FolderForClsdDataZipContents\xl\worksheets_______ worksheets worksheets.JPG : https://imgur.com/O8KBgSB
'_________________________________________________ sheet1.XML sheet1 XML.JPG : https://imgur.com/LWVPyXn
'
'FolderForClsdDataZipContents\xl\_rels____________ _rels xl_rels.JPG : https://imgur.com/fwYmQwR
'_________________________________________________ Workbook.XML.rels Workbook XML rels.JPG : https://imgur.com/NOxE816
'
'FolderForClsdDataZipContents\_rels___________rels _rels.JPG : https://imgur.com/RTVajJI
'____________________________________________.rels Dot rels.JPG : https://imgur.com/NOxE816

[DocAElstein]

Summary of info in the XML files for "ClsdData.xls" and "NameObjectFile.xls"

app.xml
"ClsdData.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"><TotalTime>0</TotalTime><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector baseType="variant" size="4"><vt:variant><vt:lpstr>Arbeitsblätter</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant><vt:variant><vt:lpstr>Benannte Bereiche</vt:lpstr></vt:variant><vt:variant><vt:i4>2</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector baseType="lpstr" size="3"><vt:lpstr>DataSht_1</vt:lpstr><vt:lpstr>DataSht_1!NameForDataSht_1A1</vt:lpstr><vt:lpstr>DataSht_1!Sht_1A1</vt:lpstr></vt:vector></TitlesOfParts><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>12.0000</AppVersion></Properties>
"NameObjectFile.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"><TotalTime>0</TotalTime><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector baseType="variant" size="2"><vt:variant><vt:lpstr>Arbeitsblätter</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector baseType="lpstr" size="1"><vt:lpstr>NameObjectsSht_1</vt:lpstr></vt:vector></TitlesOfParts><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>12.0000</AppVersion></Properties>

_.________________________________________________ _________________

sharedStrings.XML
"ClsdData.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<sst uniqueCount="2" count="2" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">-<si><t>dataA1</t></si>-<si><t>dataB1</t></si></sst>

"NameObjectFile.xls"
-
_.________________________________________________ _____________________

workbook.xml
"ClsdData.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fileVersion rupBuild="4507" lowestEdited="4" lastEdited="4" appName="xl"/><workbookPr defaultThemeVersion="124226" codeName="DieseArbeitsmappe"/><bookViews><workbookView windowHeight="11535" windowWidth="14910" yWindow="30" xWindow="240"/></bookViews><sheets><sheet r:id="rId1" sheetId="1" name="DataSht_1"/></sheets><definedNames><definedName name="NameForDataSht_1A1" localSheetId="0">DataSht_1!$A$1</definedName><definedName name="Sht_1A1" localSheetId="0">DataSht_1!$A$1</definedName></definedNames><calcPr calcId="125725"/></workbook>

"NameObjectFile.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fileVersion rupBuild="4507" lowestEdited="4" lastEdited="4" appName="xl"/><workbookPr defaultThemeVersion="124226" codeName="DieseArbeitsmappe"/><bookViews><workbookView windowHeight="11535" windowWidth="14910" yWindow="30" xWindow="240"/></bookViews><sheets><sheet r:id="rId1" sheetId="1" name="NameObjectsSht_1"/></sheets><externalReferences><externalReference r:id="rId2"/></externalReferences><definedNames><definedName name="NameForDataSht_1B1" localSheetId="0">[1]DataSht_1!$B$1</definedName></definedNames><calcPr calcId="125725"/></workbook>


_.________________________________________________ __________________________________________

sheet1.XML
"ClsdData.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetPr codeName="Tabelle1"/><dimension ref="A1:B1"/><sheetViews><sheetView workbookViewId="0" tabSelected="1"><selection sqref="B8" activeCell="B8"/></sheetView></sheetViews><sheetFormatPr defaultRowHeight="12" baseColWidth="10"/><sheetData><row r="1" spans="1:2"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c></row></sheetData><pageMargins footer="0.3" header="0.3" bottom="0.78740157499999996" top="0.78740157499999996" right="0.7" left="0.7"/></worksheet>

"NameObjectFile.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetPr codeName="Tabelle1"/><dimension ref="A1"/><sheetViews><sheetView workbookViewId="0" tabSelected="1"/></sheetViews><sheetFormatPr defaultRowHeight="12" baseColWidth="10"/><sheetData/><pageMargins footer="0.3" header="0.3" bottom="0.78740157499999996" top="0.78740157499999996" right="0.7" left="0.7"/></worksheet>

_.________________________________________________ _______
Workbook.XML.rels
"ClsdData.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Id="rId3"/><Relationship Target="theme/theme1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Id="rId2"/><Relationship Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId1"/><Relationship Target="sharedStrings.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Id="rId4"/></Relationships>

"NameObjectFile.xls"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Target="theme/theme1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Id="rId3"/><Relationship Target="externalLinks/externalLink1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLink" Id="rId2"/><Relationship Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId1"/><Relationship Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Id="rId4"/></Relationships>

[DocAElstein]

Some notes to support other posts: A brief introduction to objects and class objects in VBA

This is to support a Tips and Tutorial on advanced Event coding. ( http://www.excelfox.com/forum/showth...ication-Events ) It is difficult to look at advanced events coding without hitting some fundamental ideas behind objects and class objects in VBA.

This thing, "Tabelle2" , ( https://imgur.com/hHHdxyD ) .._
Attachment 2114 , _.. could loosely be described as a ""worksheet" object with a code in it"…

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name
End Sub

Right mouse click Or double click in VBA explorer Project window to get code module.JPG : https://imgur.com/gsz6s2N
That coding results in you getting a simple message if you change the value in the first worksheet cell :
Automatic message after change value in first cell .JPG : https://imgur.com/WFINlbq , https://imgur.com/hHHdxyD

_ The actual object: where what how to get at or change
_ what precisely/ physically any object is, is not precisely defined. Consequently what we actually use, and where, in order to "use" an object is somewhat abstract and can be different at different times or for different purposes. As example, In the code example above we were using the second worksheet in a workbook. That worksheet object could "physically" be described as the spreadsheet we "see" when clicking on the second tab. Writing into cells could be described as using the worksheet object. But you will see that in the simple routine above, we referred to the second worksheet object using ".Me" ( Me.JPG : https://imgur.com/R5nJ4n9 ). This is because the code module and code window shown in the screenshots above is also often considered to be that worksheet object. This should confuse you. The concept is not precise. I think possibly in the last 20 years there were too many people employed in the computer industry who had nothing to do. They may have gone a bit mad in their boredom.

_ Class. Class object
_ If we "go back up" the programming hierarchy from, say a worksheet, then we would often have a class object which could / is sometimes seen as actually physically being a Class code module. So that would be a code module similar "looking" to our worksheet code module, but placed somewhere further "up" the hierarchy. A "Class" in VBA is as vague a concept as most VBA stuff follows the word definition of something along the lines of a blueprint or template or Type.
One could thing of the Class as the instructions, as simple text , on how to build something, and a VBA object could be built following those instructions.
A Variable used for an object will generally need to be declared ( Dimed ) to a specific type, and early on in VBA programming one may have, unknowingly, used a Class without realising it, for example , in code lines like these , the word Range , refers to the class Range
Dim Rng As Range
_ Set Rng=Range("A1")
In general, any object will be of a certain type , and the coding or information needed to use those objects will to a large extent be contained in its class. This may or may not be "see able" or accessible to us: it may or may not have a class code module. Such a code module, if it exists, can , and often is, loosely define as that Class object and which we then may or may not be able to access, see and/ or change:…
Class Class object WorksheetType2.JPG : https://imgur.com/PPUfc2w
Class Class object.JPG : https://imgur.com/3WDRcpU
It is very confusing to try and get a clear picture of this structure in the VBA Project window because Microsoft Excel and Microsoft Excel VBA is a disorganised mess:
On the one hand: We see in the VB Editor VBA Project window the individual worksheet objects modules, but not the Class object module from which they "come".
On the other hand: We can add a Class module , which we see then in the VBA Project window, MakeClass.JPG: https://imgur.com/GoKHDoq , but usually we cannot see the individual objects which we make from that Class.

[Class "WorksheetType2" made by us, seen as module ] _ [Class "Worksheet" made by Microsoft, invisible to us ]
___ [ "ShTyp2_1" ] _ [ __ ] [ _ ] ….. ___________________________ ["Sheet1"] ["Tabelle2"] ["MySheet"] ["Sht_4"]…..

So we could make one of those Classes / class modules , for example from the VB Editor VBA Project window by selecting the appropriate right mouse click option… _..
InsertClassModule.JPG : https://imgur.com/vcZSEAj , https://imgur.com/u1orh81
_.. and change its name to, for example , WorksheetType2 via the VBA Project properties window
NameClass.JPG : https://imgur.com/S6u7Gbf
We could add some simple coding "within that object" to "make that object" , for example a simple "Name" Property.
BuildAClass.JPG : https://imgur.com/4WGRbDC
(There is no significance to what that Name Property for the Class WorksheetType2 is at this stage. For the Class Worksheet the Name property is given further significance due to other coding in the Worksheet Class module which we do not have any access to. )

Class Module, Named by us - "WorksheetType2"

' Class (Modules) : https://www.youtube.com/watch?v=jHa8W52mD1k&index=65&list=PLS7iHfqXNVhK3yzd_4XS5k4zsvnu2mkJC : https://www.youtube.com/watch?v=MjbmsVDnAL0
Public Name As String

We can then use that class "WorksheetType2" in a similar way to which we use the existing class "Worksheet". We even get the options added to the intellisense drop down lists:
SimpleWorksheetNamingCode.jpg : https://imgur.com/5pYovYt
SimpleWorksheetNamingCode .jpg : https://imgur.com/v8ZUVVx
So in any code module, we can now do like:

Sub NameAWsType2()
' Make a Worksheet object
Dim Ws4 As Worksheet
 Set Ws4 = Worksheets.Item(4)
' Make a WorksheetType2 object
Dim WsTyp2 As WorksheetType2
 Set WsTyp2 = New WorksheetType2
' Name the worksheets
 Let Ws4.Name = "Sht_4"
 Let WsTyp2.Name = "ShTyp2_1"
' Access the names	
 MsgBox prompt:=Ws1.Name & vbCrLf & WsTyp2.Name
End Sub

The way that our given name WorksheetType2 is used in coding such as that above, supports the idea that in the case of a Class the code module itself can be thought of as the Class object

Just to help clarify. There will be somewhere "hidden" from us, a Worksheet class module, and that will include a vast amount of coding, some of which will include functions / methods which will be associated with the Worksheet Name Property. I guess if we had access to that it might be dangerous as we might change something that could cause a chaos somewhere, as other things will likely be organised in the Excel we use, based on how that coding is.
The word New "creates" an object (a process called instantiating ).
The internal coding which we have no access to will have created the Worksheets already "existing".
We have to do this instantiating for any objects we create, either
through instancing a Class which we have made, as we are discussing here
or
by accessing other objects not included as default in Excel, often referred to as Binding ( http://www.excelfox.com/forum/showth...ing-Techniques )
As I am not allowed such access to the Worksheet class, I cannot use Set __ = New ___ , I can only assign a variable to the existing object like Set __ = ___

Finally, I try to here to sketch in
_ the "invisible" Class object module for the standard Excel worksheets,
and
_ two object modules for the objects I might "make" from the see able Class object module which we "made" with the coding above
Class Object Mess.JPG : https://imgur.com/r6hrPSK
Attachment 2116

[Class Worksheet]_ [First worksheet object]
_____________________[Second worksheet object]

_ [Class WorksheetType2 ] __ [First object (ShTyp2_1)]
________________________________[Second object]

Also we have a code module, which is not so often called an object, and a Thisworkbook ( In German DieseArbeitsmappe ) code module usually regarded as an object.

It is a mess because it is a mess. :-)

Here is a special "Excel" file which I have which has 6 worksheets.
It has the Class object modules and object modules for
the Application Excel
and
the worksheets. ( Each worksheet has a Class object with just one worksheet "made" from it )
Alans Full Excel.JPG : https://app.box.com/s/iaozdmu9jhu33wo9r2ntcdhkkz1bwu9g , https://imgur.com/0k2NDVX
Attachment 2115

[Class ExcelAppThisWorkbook] _ [ThisWorkbook object]

_[ Class Worksheet1 ] ________ [First worksheet object]

_ [Class Worksheet2 ] ________ [Second worksheet object ]

_ [Class Worksheet3 ] ________ [Third worksheet object]

_ [Class Worksheet4 ] ________[ Forth worksheet object]

_ [Class Worksheet5 ] ________ [Fifth worksheet object]

_ [Class Worksheet6 ] ________ [Sixth worksheet object]

_ [Class Worksheet7 ] ________ [Seventh worksheet object]





Ref
http://www.cpearson.com/excel/classes.aspx ( RiP Chip Pearson http://excelmatters.com/2018/04/30/rip-chip-pearson/ )

[DocAElstein]

Routine for following excelfox Thread
http://www.excelfox.com/forum/showth...0865#post10865

Sub TestieCall()
 Call PubeProFannyTeas__GLetner("23 12 2018")
End Sub
Sub PubeProFannyTeas__GLetner(ByVal strDte As String)
Rem 0 VBA project instantiated VBIDE
Dim VBIDEVBAProj As Object ' For convenience a variable is used for this code module
 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule                                                 ' ThisWorkbook.VBProject.VBComponents(Me.CodeName) 'varible referring to this code module
Rem 1 This code module data range
'1a) get full data range as string
Dim Cnt As Long, Lr As Long, ReedLineIn As String
 Let Lr = VBIDEVBAProj.countoflines: Let Cnt = Lr + 1
    Do
     Let Cnt = Cnt - 1
     Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=Cnt, Count:=1)
    Loop While Not (Left(ReedLineIn, 7) = "End Sub" Or Left(ReedLineIn, 7) = "End Fun")
    If Cnt = Lr Then MsgBox Prompt:="No range data values in code module  " & VBIDEVBAProj.Name: Exit Sub
'1b) Complete data region as single string.
Dim strIn As String: Let strIn = VBIDEVBAProj.Lines(StartLine:=Cnt + 1, Count:=Lr - Cnt)
 Let strIn = Mid(strIn, 3) ' take off first vbCr & vbLf
 'WotchaGot (strIn)
'1c) split into date ranges, get most recent of any dates to match given  strDte
Dim DtedRngs() As String: Let DtedRngs() = Split(strIn, vbCr & vbLf & vbCr & vbLf) ' Split range by empty line which is double  vbCr & vbLf
 'WotchaGot (DtedRngs(0)): Debug.Print: WotchaGot (DtedRngs(1))
    For Cnt = UBound(DtedRngs()) To LBound(DtedRngs()) Step -1
    '1d)Check for date match, if so the main code working begins
        Dim FndDte As String: Let FndDte = Mid(DtedRngs(Cnt), 4, 10) ' looking at like this typical start of a data range,    '_-23 12 2018 Wo....  we see that 10 characters from character 4 will give us the date
        If FndDte = strDte Then
         'MsgBox Prompt:=FndDte
        Rem 2 manipulation of found date range
        Dim strRng As String: Let strRng = DtedRngs(Cnt)
         Let strRng = Mid(strRng, 27) 'takes off up to start of worksheet name... no speacial reason toher than why not? - its not needed anymore
        '2a) range info
        Dim RngInfo As String: Let RngInfo = Left(strRng, InStr(1, strRng, """)" & vbCr & vbLf, vbBinaryCompare) - 1) '    This gets us at like        Tabelle1").Range("$I$2513:$J$2514
        Dim ShtName As String, RngAdrs As String
         Let ShtName = Split(RngInfo, """).Range(""", 2, vbBinaryCompare)(0) '    split above string ,  using as seperator  ").Range("   ,  into 2 bits   ,   for exact computer binary type compare     Then we have first array element (indicie (0)) as the worksheet name  and the second array element (indicie (1)) as the range address
         Let RngAdrs = Split(RngInfo, """).Range(""", 2, vbBinaryCompare)(1) ': Debug.Print ShtName & "  " & RngAdrs
        Dim Ws As Worksheet, Rng As Range: Set Ws = Worksheets("" & ShtName & ""): Set Rng = Ws.Range(RngAdrs)
        '2b) get data value range
         Let strRng = Mid(strRng, InStr(1, strRng, vbCr & vbLf, vbBinaryCompare) + 2) ' take off first line & the first vbCr & vbLf
         Let strRng = Left(strRng, InStr(1, strRng, "'_- EOF ", vbBinaryCompare) - 1) ' take off last line, ( but leave on the vbCr & vbLf as that seems to typically be on a string from an excel range
         'WotchaGot strRng
         Let strRng = Replace(strRng, " | ", vbTab, 1, -1, vbBinaryCompare) 'Change code window cell wall seperator for that used by Excel
         Let strRng = Replace(strRng, "'_-", "", 1, -1, vbBinaryCompare)
         Let strRng = Replace(strRng, "  ", "", 1, -1, vbBinaryCompare) ' Bit of bodge to remove my added spaces
         'Debug.Print strRng
        Rem 3 output to worksheet
        Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"): objDataObject.SetText strRng: objDataObject.PutInClipboard ' Text is given to Data object which in turn uses its method to put that in the clipboard
         Ws.Paste Destination:=Rng 'Worksheets Paste method with optional argument to determine where, ( default would be from top left of active range )
         Exit Sub 'This code only gets the first found range looking from code window bottom
        Else '     No matching date found yet, so do nothing but
        End If '    go on to
    Next Cnt '    next date range ' ( There is no check for no matching date. The code will simple end after all ranges have been looped through.)
End Sub

[DocAElstein]

4, 5, 6 and 7 data section output after running Sub SpltTests() from http://www.excelfox.com/forum/showth...0881#post10881
https://tinyurl.com/yd95w5v2

_____ Workbook: Sample.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H
41	40	1416		80	1456		120	1496
42	S1	S2		S1	S2		S1	S2
43	121	1497		161	1537		201	1577
44	122	1498		162	1538		202	1578
45	123	1499		163	1539		203	1579
46	124	1500		164	1540		204	1580
47	125	1501		165	1541		205	1581
48	126	1502		166	1542		206	1582
49	127	1503		167	1543		207	1583
50	128	1504		168	1544		208	1584
51	129	1505		169	1545		209	1585
52	130	1506		170	1546		210	1586
53	131	1507		171	1547		211	1587
54	132	1508		172	1548		212	1588
55	133	1509		173	1549		213	1589
56	134	1510		174	1550		214	1590
57	135	1511		175	1551		215	1591
58	136	1512		176	1552		216	1592
59	137	1513		177	1553		217	1593
60	138	1514		178	1554		218	1594
61	139	1515		179	1555		219	1595
62	140	1516		180	1556		220	1596
63	141	1517		181	1557		221	1597
64	142	1518		182	1558		222	1598
65	143	1519		183	1559		223	1599
66	144	1520		184	1560		224	1600
67	145	1521		185	1561		225	1601
68	146	1522		186	1562		226	1602
69	147	1523		187	1563		227	1603
70	148	1524		188	1564		228	1604
71	149	1525		189	1565		229	1605
72	150	1526		190	1566		230	1606
73	151	1527		191	1567		231	1607
74	152	1528		192	1568		232	1608
75	153	1529		193	1569		233	1609
76	154	1530		194	1570		234	1610
77	155	1531		195	1571		235	1611
78	156	1532		196	1572		236	1612
79	157	1533		197	1573		237	1613
80	158	1534		198	1574		238	1614
81	159	1535		199	1575		239	1615
82	160	1536		200	1576		240	1616
83	S1	S2
84	241	1617
85	242	1618
86	243	1619
87	244	1620
88

Worksheet: Result