Excel Automation Error

[SeanK]

I'm getting an Automation Error when attempting to use CopyFromRecordset to dump the contents of a recordset created in VB6 to Excel. Here is my code

Dim oXlApp As Object, oXLWb As Object, oXLWs As Object
 
Dim vRecArray As Variant
    Dim lRecCount As Long
    Dim iFldCount As Integer, iCol As Integer, iRow As Integer
     
    'Create an instance of Excel and add a workbook
    Set oXlApp = CreateObject("Excel.Application")
    Set oXLWb = oXlApp.Workbooks.Add
    Set oXLWs = oXLWb.Worksheets("Sheet1")
    
    'Display Excel and give user control of Excel's lifetime
    oXlApp.Visible = True
    oXlApp.UserControl = True
    
    'Copy field names to the first row of the worksheet
    iFldCount = adoRS.Fields.Count
    
    For iCol = 1 To iFldCount
        oXLWs.Cells(1, iCol).Value = adoRS.Fields(iCol - 1).Name
    Next iCol
 
   [HL="#FFFF80"]oXLWs.Cells(2, 1).CopyFromRecordset adoRS[/HL]

The error occurs on the highlighted line, and here is the error.

[si_the_geek]

That looks ok to me.. the only difference between that an the way I normally do it is that I use Range rather than Cells, eg:

oXLSheet.Range("B15").CopyFromRecordset oRecordset

..as Cells is basically a Range object this (theoretically) shouldnt make a difference - but it's worth a go.

If that doesn't help, what version of Excel are you using, and how is the recordset declared/opened?

[SeanK]

what version of Excel are you using

Excel 2003

and how is the recordset declared/opened?

strSQL = "SELECT blah blah WHERE blah blah etc "
Set adoRS = New ADODB.Recordset
adoRS.Open strSQL, cn

cn is a global connection object and adoRS is a global recordset object.

[si_the_geek]

I have just used exactly the same code (with Excel 2000) and it works fine.

Is the recordset declared like this?

Public adoRS As ADODB.Recordset

Which version of ADO have you referenced, and which version of MDAC do you have? (see the MDAC link in my signature for a tool to find out).

Does it work if you use Range instead of Cells?

[SeanK]

I have just used exactly the same code (with Excel 2000) and it works fine.

Is the recordset declared like this?

Public adoRS As ADODB.Recordset

Yes

Which version of ADO have you referenced, and which version of MDAC do you have? (see the MDAC link in my signature for a tool to find out).

Why would MDAC be an issue working between VB6 and Excel 2003?

Does it work if you use Range instead of Cells?

No.

[si_the_geek]

Why would MDAC be an issue working between VB6 and Excel 2003?

MDAC contains several DB technologies, most notably ADO and database drivers. It is possible that whatever version of ADO you are using (or have available) is not compatible for some reason.

I would be surprised if it isn't compatible, but the only other thing that I can think of at the moment is the "Display Excel" part of your code should arguably be after the rest of the code - but I dont think that is the problem either.


On a side note, are you setting your object variables to Nothing after the code runs?

[SeanK]

On a side note, are you setting your object variables to Nothing after the code runs?

Well, kinda yes and kinda no.

The code is there to close and set the objects to nothing, but the program never reaches that point. It blows up well before then