HTML Code Test --post8798

[DocAElstein]

Rem 2 Second part of first main code

Rem 2
So far we have looked at the right hand side of the basic code line of, simplified,
Range(“B5”).Value = “ = myNamedRange “
This is very similar to investigating manual uses of named ranges, that it to say, typing things like …_
__ “ = myNamedRange “
____ _... in a spreadsheet cell: That simple code line basically writes the string in the cell , as a person would do manually.

We take the experimenting a little further now , so as to include a named range in the left hand side, like
Range(“RangeName”).Value = “ = myNamedRange “

Range ( “ _ “ ) What it is
As far as I know, this Range(“ “) thing, ( that confusingly pops up all over the place as an object or property, ( an occasionally in some senior professionals opinion as a method) ), is not really supposed to be used with nothing before it, but usually it is, and usually Excel guess correct what to put in front of it, so it usually works as expected.
The Range(“ “) thing is usually used in two main situations, in a worksheets range property
or
“ Application Range “ .
The worksheets range property would have a syntax like, for example, to reference the second cell in Sheet1
Sheet1.Range(“B1”)
Application Range is something similar, and is what in most situations is the default that Excel uses when you just write like:…_
Range(“ ”)
_.... _ In many situations, Excel will take that as:
Application.Range(“ ”)

There is a bit more to it than all that, and that can easier be explained by looking at what that Range(“ “) thing seems to do..

( One thing to note here is that the official documentation is often wrong or at least questionable. It seems that nobody really understands what goes on in the internal “wiring” anymore. Often what it seems to have been done is to interpret what happens, and then Methods, Properties are given to explain what seems to happen. I have often had heated discussions with professionals that disagree with my interpretations. But sometimes literature from Microsoft has been changed to reflect my interpretations, whilst the same professionally , sometimes Microsoft MVP’s (Most Valuable Professional’s) at the time, haven’t been able to get any response from Microsoft about anything… )

Range ( “ _ “ ) What it does
This usually returns a range object. It takes in the (“ “) a string reference to that wanted range. That reference is similar to those discussed already. ( Once again, I believe that Excel will add to what you give, so as to give a full reference , should you only give part of it).
If you give a full reference to say, a range in Sheet2 using Sheet1 range property, like …_
Sheet1.Range(“=’C:\MyFolder\[MyFile.xls]Sheet2’!G5”)
_... then that will error as it will not find G5 from Sheet2 in Sheet1
As far as I know, Application.Range(“ “) will take any valid range reference and return the range object of that range.

Range(“ ”) will accept a full range reference ( which is a reference to a closed workbook ) , and it will return the range object wanted, but only if that workbook is open. I expect it is designed that way as Excel will not let you make a range object of a range in a closed workbook.
_.___________


On now with the code part Rem 2 description.
Initially I will make a named range for the range B5 which we were referencing so far like:
__ Application.Range(“='C:\Folder\[MasturFile.xlsm]Tabelle1'!B5”).Value = ____
'2a) I scope to one of the data files, data 2 file, “Data2.xlsx”. Then the code line4 above is used inn this form.
(For the right hand side of the equation( which is required to get the value from data 1 file, B5 , we use a reference containing one of the existing named ranges Added/created in Rem 1)
Application.Range(“='C:\Folder[Data2.xlsx]Tabelle1'!MainFoodheader").Value = ____
Just to refresh our memories of what we are doing with that last line in the left hand side: We have in Range(“ “) a reference to a named range object in data 2 file. That in turn has the info we want of the range Referred To by that name which is B5 in the main file. This will result in range(“ “) returning us the range object of that cell. Then assigning a .Value to that range object will result in that .Value appearing in the cell in the spreadsheet. That .Value is a full reference to the ( closed) data file, ( ='C:\Folder\Data1.xls'!Dta1Foodheader ) , which brings the text “Food” into the cell B5 in the main workbook.
With the data 2 file open, the code line ( ) works . What is perhaps slightly surprising is that with the data 2 file closed, the code line ( ) errors as it can’t define the range. ( 1004 The Range method for the _Application object failed ) . Possibly the “wiring” of Range(“ “) is set to error if any workbook referenced is closed. That is required for the more usual range reference in the (“ “), and possibly such a usage as I am doing here was simply not envisaged at the time….

Rem 3
As a quick reminder to simple referencing of ranges , this simply brings in the Header "Suppliment" from data 2 workbook directly without named ranges. The code line shows similar strings on both sides

The code line is this sort of form:
Range("=" & "'" & WbMain.Path & "" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
The actual string references are like:
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B10 ").Value = " ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10"

_.___

For comparison, some corresponding code lines for bringing in the Header "Food" from data 1 workbook , using some of our created named ranges are:
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\Data1.xls'!Dta1Foodheader"
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\MasturFile.xlsm'!MainDta1Foodheader"
Range("='C:\MyFolder\MasturFile.xlsm'!MainFoodheader ").Value = " ='C:\MyFolder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
The same basic code line without using named ranges would be
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\[Data1.xls]Tabelle1'!B5""
Remember the difference in what “goes on” with and without the named ranges is: Without the named ranges we are referencing the referred to range directly. With the named ranges, we reference somehow to the relevant named range Name object ( via its string Name) . That Name object contains, and somehow “gives out” to Excel, the referred to range: We give that Refered To range, along with the string Name when we create/Add that named range Name object to either a workbook’s named objects collection or a worksheets named objects collection.
_._____________________________




In the next post we consider how to bring in the data to the master workbook from the two data workbooks.


So far we have got this far, that is to say we all the headers, that originally in the main book,
Nutrition _ | _ Energy
along with now the sub headers also
Food


Suppliment



Using Excel 2007 32 bit

Row\Col	A	B	C	D
1
2
3
4		Nutrition	Energy
5		Food
6
7
8
9
10		Suppliment
11
12
13
14
15
16
17
18
19
20
21
22

Worksheet: Tabelle1


The words “Food” and “Suppliment” are seen in the spreadsheet cells, but in the formula bar we see for
B5 _ - _
B10 _ - _
MainFoodHeader.JPG : https://imgur.com/uJCkJwb



The words “Food” and “Suppliment” are seen in the spreadsheet cells, but in the formula bar we see for
B5 _ - _ ='C:\MyFolder\Data1.xls'!Dta1Foodheader
MainFoodHeader.JPG : https://imgur.com/uJCkJwb

B10 _ - _ ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10
MainSupplimentHeader.JPG : https://imgur.com/wQD5FPB