Excel Automation in VB2008 and Dynamic Named Range

Question

Hi,

Having created a small (Excel Automation) VB Appl.  to manipulate and present data from Excel sheets in an organised form, I ran across a problem resulting from creating a named range on a sheet, the code line:

oWB.Names.Add("DR_AppData2", RefersToR1C1:="=OFFSET(AppData!R3C1,0,0,(COUNTA(AppData!C1)-2),1)")

seems to work as expected, my only issue here is that this was written on a System that is US-en, and when trying this on a PC DE-de - we have users with varoius language sets - the code fails with a COMException. The only cure I have found to date, is to write the German language version of this:

oWB.Names.Add("DR_AppData2", RefersToR1C1:=BEREICH.VERSCHIEBEN(AppData!R3C1;0;0;(ANZAHL2(AppData!C1-2);1)

My query: How can I write my code / what tools do I need to use, in order that my application will work with any Excel language - and me not having to know all possible equivalents for a particular command or function?

Answer 1

From  the below code, range of (1,1) to (1,5) will merge

Dim LoExcel As Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

LoExcel = CreateObject("Excel.Application")
LoExcel.Workbooks.Add()
LoExcel.ReferenceStyle = Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1
oBook = LoExcel.ActiveWorkbook
oSheet = LoExcel.ActiveWorkbook.ActiveSheet

 

blah...blah...blah...

oSheet.Range(oSheet.Cells(1, 3), oSheet.Cells(1, 5)).Merge()

blah...blah...blah...

oSheet.Range(oSheet.Cells(1, 3), oSheet.Cells(1, 5)).Merge()

 

 

---

U.PARANTHAMAN

Answer 2

Hi David,

this is a localization issue that happens on PCs running an English Office with different locale (i.e. like in  my scenario, a German locale). In order to work around this, you'll need to switch to the en-US locale while performing Excel-automation, i.e.:

Dim ciOnStart As System.Globalization.CultureInfo = _
   System.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = _
   New System.Globalization.CultureInfo("en-US")

'Perform tasks ...

'Switch back
System.Threading.Thread.CurrentThread.CurrentCulture = ciOnStart

This of course is the short version and should of course be wrapped in a TryCatch-block.

Cheers,
Olaf