Access to Excel Automation Error 1

[Wolfen02]

I'm copying a recordset obtained from a subform based on parameters set on the main form into Excel. The Excel workbook has both a "dump" page for the raw data and a template. Based on the number of records, the template is copied as needed. It then inserts a set number of records into each template sheet and deletes the sheet holding the raw data.

The code will run fine the first time through, but keeps giving various errors when attempting to re-run based on a new set of parameters. The raw data will transfer into Excel, but then the code hangs up when attempting to obtain the number of records in Excel (see code below).

Any suggestions?

'\\Obtain number of rows of data to be used
        r = 1
        xl.Worksheets("Dump").Activate
        With xl.Worksheets("Dump").Range("A2")
            .Select
            .Activate
            Do Until IsEmpty(ActiveCell.Value)
                If Not IsEmpty(ActiveCell.Value) Then
                    r = r + 1
                    ActiveCell.Offset(1, 0).Select
                End If
            Loop
        End With

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[Wolfen02]

Am receiving Run Time error 91 - Object variable or with variable not set. Also, when the subform is requeried again the code will run again. (works 1st time, error 2nd time, works 3rd time)

Any assist appreciated.

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[PHV]

Replace this:
ActiveCell.Value
By this:
xl.ActiveCell.Value
and so on.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wolfen02]

Thanks PH!!! Works perfect now! Deserving of a star!

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions