convert A1 cell reference to xlR1C1 style

[ragtopcaddy via AccessMonster.com]

I have the following 2 lines of code:

XLobj.ReferenceStyle = xlR1C1
WkBk.Names.Add Name:=rngName, RefersToR1C1:="=" & wkshtName & "!" & XLobj.
ActiveCell.CurrentRegion.Address

I inserted the 1st line in a vain attempt to force .CurrentRegion.Address to
return an R1C1 string.
Unfortunately, the address still comes back in A1 style, which the Names.Add
function rejects with "Run-time Error '1004'", "The formula you typed
contains an error". The function is, unexpectedly, OK with the other 2
variables, rngName and wkshtName.

So, how do I convert the A1 reference to R1C1?

Thanks,

 

[Linq Adams via AccessMonster.com]

This would appear to be an MS Excel question, but you've posted it in an MS
Access forum!

 

[ragtopcaddy via AccessMonster.com]

I am an Access/VBA developer. As is quite common in my business, I am
reporting from an Access db to an Excel spreasheet. This is an Access
question.