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

  • Thread starter Thread starter Jesper
  • Start date Start date
J

Jesper

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
 
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,
 
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).
Click to expand...

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
 
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)
 
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)
Click to expand...

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
 
Back
Top