Range referencing in Excel

[meboz]

Hi all,

I have a problem that seems to be fairly common around the place ive noticed, after doing some googling.

It seems cell references made in an Access module need to be hung off the excel.application object, and if they are not the code runs and then hangs without the user knowing. But you can still see the excel process in task manager.

Ok, so the solution would be to hang all your range references off the excel.application object right?

No so... 2 examples are as follows. They both set the value of "A1" to 20

1. objXL.Range("A1").value = 20
2. objXL.Range(Cells(1, 1), Cells(1, 1)).value = 20

both lines of code are correct except that a procedure with the 2nd line will hang and the excel instance remains open in the processes.

How can I keep the flexibility of the2nd method of cell referencing but ensure that the process is properly ended?

What is the correct way to create a hidden instance of excel in automation?

 

[meboz]

A possible workaround

If you build the range argument as a string then pass it to the range object, the problem is avoided...

 

[fpendino]

Try using this.

objXL.Cells(1, 1).Value = 20

 

[RoyVidar]

I like using both workbook and worksheet object variables, so my code would probably look like this:

objWS.range(objWs.cells(1,1),objWS.cells(1,1)).value=20

- where objWS is set to the worksheet I'm working with

As also presented in this MS link:
http://support.microsoft.com/default.aspx?kbid=178510

 

[meboz]

RoyVidar,

Looking at you line of code, i see what my problem was.

The Cells reference that was inside the Range object wasnt hung off the excel.application variable.

 

[RoyVidar]

Yeah..., one usually only look at the start of the lines, then perhaps at what's at the immediate right of an equal sign - why do I get this 1004 error...

But I'll still continue recommending object variables for both the workbook and worksheet - I've found that it seems to reduce some of the anomalities one may encounter when automating other office applications (or rather the possibilities of implicit referencing, causing such).