Automating Excel from Access 97

[Guest]

Hello, my code is throwing up error messages at the following point:

xlSheet.Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-3]C)"

Can you advise what I'm doing wrong? When it occurs, I click CTRL + ALT +
DEL and close the Excel process and rerun, but get the same problem (I get a
variety of error messages..). Is there an alternative way of writing the
formula into cell D13?

Any help would be much appreciated.

Tim Long

 

[Tim Ferguson]

xlSheet.Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-3]C)"

Can you advise what I'm doing wrong?

First of all, you are asking this in an Access programming group: you'll
get much more useful help in an Excel NG.

Second, it's a good idea to get scripts running properly inside the host
application, then transfer them to the remote program.

Thirdly, statements like .Select and .ActiveCell depend on parts of the
user interface you cannot control remotely (e.g. which sheet is visible and
Activated etc). Why not just do the thing in one go vis:

xlSheet.Range("D13").FormulaR1C1 = "=SUM(R[-9]C:R[-3]C)"


Hope that helps


Tim F

 

[Guest]

Hello Tim, many thanks, I wasn't aware of that at all (that there are parts
of the Excel user interface that can't be controlled remotely etc), that
would explain a lot! I have removed all .selects and .activecells and so on
from the code and it seems to be working fine now..

Many thanks again

Tim Long

xlSheet.Range("D13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-3]C)"

Can you advise what I'm doing wrong?

First of all, you are asking this in an Access programming group: you'll
get much more useful help in an Excel NG.

Second, it's a good idea to get scripts running properly inside the host
application, then transfer them to the remote program.

Thirdly, statements like .Select and .ActiveCell depend on parts of the
user interface you cannot control remotely (e.g. which sheet is visible and
Activated etc). Why not just do the thing in one go vis:

xlSheet.Range("D13").FormulaR1C1 = "=SUM(R[-9]C:R[-3]C)"


Hope that helps


Tim F