Appendix Thread 2. ( Codes for other Threads, HTML Tables, etc.)

[DocAElstein]

First main Demo code in support of this Thread:
http://www.excelfox.com/forum/showth...-a-named-range
Posts from approximately here:
http://www.excelfox.com/forum/showth...814#post10814:

Sub FoxySingleCellNamedRanges()
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  Dim WkBk As Workbook
260      For Each WkBk In Workbooks
270       Call FukYaWkBkNames(WkBk)
280       'Call GeTchaNms(280, WkBk)
290      Next WkBk
300   Workbooks("Data1.xls").Close savechanges:=True
310   Workbooks("Data2.xlsx").Close savechanges:=True
312  '0b) clear the entire data ranges in the first worksheet in the main workbook, both headers and data
315   ThisWorkbook.Worksheets.Item(1).Range("B5:C12").ClearContents
320  Rem _1) Data1 "Food" header
330  '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
340   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
350   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
360   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
370   Call GeTchaNms(370, dataWb1xls)
380   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
390   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' "Going" to Workbook  Data1.xls
400   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle4'!Dta1Foodheader" ' "Going" to any worksheet in  Data1.xls
410  '1b) Data1 cell Worksheet Scoped to one of its worksheets: Info needed is held in the named objects object of its second worksheets
420   Rem _1 Add some named ranges
430   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
440   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
450   dataWb1xls.Worksheets.Item(2).Names.Add Name:="Ws2Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
460   Call GeTchaNms(460, dataWb1xls)
470   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
480   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
490  '1b)(ii)
500   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
510   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
520   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
530   dataWb1xls.Close savechanges:=False ' I made no changes intentionally , so save without changes in case I accidentally changed anything
540  '1c) Data1 cell Workbook Scoped to a different (open) workbook : Info needed for a range in the data 1 file is held in the workbooks name objects collection object of that workbook, the main file in this case
550   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
560   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
570   WbMain.Names.Add Name:="MainDta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
580   dataWb1xls.Close savechanges:=False ' I had this open for the  Refers To:=  above, but I did not change anything, for example, this time i was not doing anything to any of its named range objects, so just iin case I accidentally changed anything I will close without saving any changes
590   Call GeTchaNms(590, WbMain)
600   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "MasturFile.xlsm'!MainDta1Foodheader" ' "Going" to Workbook  MasturFile.xlsm
610   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[MasturFile.xlsm]Tabelle4'!MainDta1Foodheader" ' "Going" to any worksheet in MasturFile.xlsm
620  '1d) This is an attempt to get at the named range object in a roundabout sort of a way. Here the data 1 cell s scoped to the second data file, "Data2.xlsx" ( Workbooks scoped to workbook "Data2.xlsx" )
630   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
640   Set dataWb1xls = Workbooks("Data1.xls")
650   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
660   Set dataWb2xlsx = Workbooks("Data2.xlsx")
670   dataWb2xlsx.Names.Add Name:="Dta2Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
680   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data2.xlsx'!Dta2Dta1Foodheader" ' "Going" to Workbook  MasturFile.xlsm
690   dataWb1xls.Close savechanges:=False ' I had this open for the  Refers To:=  above, but I did not change anything, for example, this time i was not doing anything to any of its named range objects, so just iin case I accidentally changed anything I will close without saving any changes
700   dataWb2xlsx.Close savechanges:=True ' A name object was Added, so I have a change to save
710   Let Application.Range(MBkTab1B5).Value = Application.Range(MBkTab1B5).Value ' I have done this here to "catch" the value put in, as it seems to vanish if I re enter the formula ??
720  Rem 2 Experiments with named ranges in the LHS , like in Range("rngNamed") =
730  '2a) scope to a data file
740   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
750   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Open an arbritrary data file to use one if its names objects as the place to go to get the info about the named range
760   dataWb2xlsx.Names.Add Name:="Dta2MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
770   Call GeTchaNms(770, dataWb2xlsx)
780   Let Application.Range(LisWkBkPath & "Data2.xlsx'!Dta2MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx     RHS is "Going" to Workbook  Data1.xls
790   dataWb2xlsx.Close savechanges:=True ' A name object was Added, so I have a change to save
800   Let Application.Range(LisWkBkPath & "Data2.xlsx'!Dta2MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx      RHS is "Going" to Workbook  Data1.xls
810  '2b) Workbooks Scope to main workbook: Info for named range is in Name Objects collection of Main workbook
820   WbMain.Names.Add Name:="MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
830   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
840   Call GeTchaNms(840, WbMain)
850  Rem 3 Bring in Header "Suppliment" from data 2 workbook directly without named ranges
860   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
870   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Needed for next line
880   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
890   dataWb2xlsx.Close savechanges:=False
End Sub