Language compatibility when linking variables to excel 1

[fedfue]

Hello, I have SE V20 and have the following problem. I have created a set of parameters or variables(in sketches, etc.) and have linked them to a file in excel.

My computer at work has excel installed in german, so the link is for example @<file>!<Sheet#>!Z9S9, where Z is the Row and S is the Column (initials in german), and the links work fine at work. However in my laptop I have excel (and windows) installed in english. When I try to open the same .par file in my laptop, and I see the current links, they are still of the form @<file>!<Sheet#>!Z9S9, and not @<file>!<Sheet#>!R9C9 as they should be. I tried changing the excel file to another one from solid to see if the 'Z' and 'S' changed automatically but they don't. The result, as you might expect is that the variables are not linked to the .xls file and the .par file is not updated automatically as you would like.

I could change the links manually (every single variable) every time I change computers, but they are too many so this is not really an option. Does someone know how this problem could be fixed?

 

[donyoung]

Hi,

Z9S9 refers to [Z]eile 9 palte 9 with an English version it
will obviously be [R]ow 9 [C]olumn 9. That's why Z9S9 --> R9C9.

The only thing you can do AFAIK is to assign a name to each
cell you would like to refer to:
- select the cell
- Top toolbar --> Insert --> Names --> Define

This must be done *before* copy/paste the link into SE's
variable. Now you should be independent of the different
language *and* the location of the cell. Maybe there are
other solutions as well (I'm not using Excel very often)

dy

 

[fedfue]

Thanks for the possible solution. I didn't know that when the link from Excel was pasted it copied the defined name of the cell to SE. I will take this into account in the future. Actually I find it a bit weird that it copies in the form R#C#, and not A1 (or any other cell), which is the predefined cell name from Excel.

However in my case, I have already defined the links to Excel for a considerable amount of variables, so it would be helpful to receive an alternate solution (an algorithmic way to change the 'Z' for 'R' and the 'S' for 'C') if someone else knows.

BTW, a word to the wise (or simply to the careful), in general, always define a name for your cells in Excel to prevent any time-consuming problems that could come up. My problem is the perfect example.

 

[donyoung]

Hi,

other than by programming thereby scanning all formulas and
replace the chars I don't see a solution. Maybe that there
is an option in Excel to use A# rather than the standard
notation R#C#

BTW: AFAIK the R#C# is the default notation for a cell in Excel
and so it copies that to the clipboard. SE does only add the
framing chars to the string

dy

 

[fedfue]

Hi dy and anyone else who may care,

Found the possible clever solution after some thinking (thanks to your naming cells technique). I wrote a macro (using VB) in Excel to rename the cells according to the configuration of letters found in the links in SE (which can be viewed using the variables table). Anyway, in my case the links were of the form ending in Z#S# (German configuration), so I had to change the names of the cells in my English Excel version to match those found in the links.

In case anyone is interested, to write a macro, you simply follow
-Tools > Macro > Visual Basic Editor
Once inside VB, follow
-Insert > Module
Then write the code, which is:

Sub RenameCells()
Dim cell As Range
For Each cell In Range("A1:Q50")
cell.Name = "Z" & CStr(cell.Row) & "S" & CStr(cell.Column)
Next cell
End Sub

The range "A1:Q50", should be replaced by your range in a particular sheet. Click Play on every worksheet you want in Excel (the macro must run independently on every worksheet), and save the .xls file. Now open the SE file and presto.

NOTE: The method of renaming the cells will work as long as there are no overlapping cells within the worksheets of the Excel file. That is, if you renamed A1 to Z1S1 in Sheet1, then you cannot rename A1 to Z1S1 in Sheet2, because the macro renames the cell of the whole file. Despite there being a method of naming cells with the same names in different worksheets (see

http://excel.tips.net/Pages/T002662_Using_the_Same_Range_Name_on_Different_Worksheets.html),

this method does not work when linking the variables from SE to Excel. I have tried it, and the result is that SE will update only one of the variables. The overlapping cells must therefore be carried through the process of copying and and pasting the links from Excel to SE with new (non-logical) names.