Using Access VBA to format Excel

[deejay_totoro]

Hello,

I am trying to automate the formatting of an Excel spreadsheet from Access, using VBA.

I have managed to do most of what I would like to do by first creating a Macro in Excel then using the macro code as a basis for the VBA code in Access.

But I have run into trouble to trying to execute the Autofill Destination command. These are the two different lines I have used:


Dim excel_app As Object

Dim excel_sheet As Object

...

excel_app.ActiveSheet.Range("I2").AutoFill Destination:=excel_app.ActiveSheet.Range("I2:I" & excel_app.ActiveSheet.Range("A65536").End(xlUp).Row)

excel_app.selection.AutoFill Destination:=Range("I2:I25403")

However both produce the following error:

Run-time error '1004':

Application-defined or object-defined error

Any help or guidance would be most welcome!

Cheers,

dj_T

 

[JANR]

Does the code work if you use EARLY-binding and not LATE-binding??

If so I think you must replace (xlUp) with a constant to use late-buinding. I have fallen into that trap myself.

Try:

[FONT=Courier New]excel_app.ActiveSheet.Range("I2").[B]AutoFill Destination[/B]:=excel_app.ActiveSheet.Range("I2:I" & excel_app.ActiveSheet.Range("A65536").End[COLOR=red][COLOR=red](-4162[/COLOR]).[/COLOR]Ro w)[/FONT]

Here is a list over excel constant replacement: http://msdn.microsoft.com/en-us/library/aa221100(office.11).aspx

Hope this helps

JR