Inserting array in Excel cells via vba fails every other time.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jesper - 15 Sep 2005 21:42 GMT

I'm doing some automation to Excel from Access with the following.
This code fails exactly every other time I run it and works fine every other
time.

Dim objExcelApp As Object 'late binding
Dim objExcelDoc As Object 'late binding
Dim v(50) As String

Set objExcelApp = CreateObject("excel.application") 'late binding
Set objExcelDoc = objExcelApp.Workbooks.Add
objExcelApp.Visible = True
'fill array
For i = LBound(v) To UBound(v)
v(i) = CStr(Int(Rnd() * 100))
Next
objExcelDoc.Sheets(1).Range(Cells(1, 1), Cells(1, UBound(v))) = v
Set objExcelDoc = Nothing
Set objExcelApp = Nothing

When it fails I get:

Runtime error 1004
Method 'Cells' og object '_Global' failed

and it fails at this line:
objExcelDoc.Sheets(1).Range(Cells(1, 1), Cells(1, UBound(v))) = v

Does anyeone have any idea what's going on here?
Am I terminating Excel properly?

Jesper, Denmark

Reply to this Message

George Nicholson - 16 Sep 2005 00:39 GMT

Try
objExcelDoc.Sheets(1).Range(objExcelDoc.Sheets.Cells(1, 1),
objExcelDoc.Sheets.Cells(1, UBound(v))) = v

You were using Cells in a vacuum. Excel couldn't figure out what you meant
(quite often the case with 1004 errors). Excel can't read minds, contrary to
legend <g>.

If you disambiguate Cells the error should go away (or at least that error
will go away).

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

> I'm doing some automation to Excel from Access with the following.
> This code fails exactly every other time I run it and works fine every
[quoted text clipped - 27 lines]
>
> Jesper, Denmark

Reply to this Message

Jesper - 16 Sep 2005 18:57 GMT

> Try
> objExcelDoc.Sheets(1).Range(objExcelDoc.Sheets.Cells(1, 1),
[quoted text clipped - 6 lines]
> If you disambiguate Cells the error should go away (or at least that error
> will go away).

I'm assuming that:
objExcelDoc.Sheets(1).Range(objExcelDoc.Sheets.Cells(1, 1),
objExcelDoc.Sheets.Cells(1, UBound(v))) = v
should be on one line and not wrap.

However I'm getting a "Object doesn't support this property or method" error
at that line. Have I understood it correctly?
Thanks

Jesper

Reply to this Message

George Nicholson - 16 Sep 2005 21:10 GMT

My bad.
Both occurances of "objExcelDoc.Sheets.Cells(x,x)" are missing the sheet
qualifier "(1)". They should be objExcelDoc.Sheets(1).Cells(x,x)

Try:
objExcelDoc.Sheets(1).Range(objExcelDoc.Sheets(1).Cells(1, 1),
objExcelDoc.Sheets(1).Cells(1, UBound(v))) = v
(yes, on one line)

Signature

George Nicholson

Remove 'Junk' from return address.

>> Try
>> objExcelDoc.Sheets(1).Range(objExcelDoc.Sheets.Cells(1, 1),
[quoted text clipped - 18 lines]
>
> Jesper

Reply to this Message

Jesper - 16 Sep 2005 23:10 GMT

> My bad.
> Both occurances of "objExcelDoc.Sheets.Cells(x,x)" are missing the sheet
[quoted text clipped - 4 lines]
> objExcelDoc.Sheets(1).Cells(1, UBound(v))) = v
> (yes, on one line)

I should've caugt that, but it works great now. I was working with a very
slooow solution before finding out how to send an array directly into a
range. Thanks very much!

Jesper Fjølner, Denmark

Reply to this Message