CopyFromRecordset

[Salamander]

Hi guys,

Wasn't sure whether to put this Excel or Access forum but given that it's primarily in Access... here goes =D

I have the following code which I thought was very simple.

Rem ** Open RS
    rs.Open theQuery, cnn, adOpenDynamic, adLockOptimistic, adCmdTable
 
    If rs.EOF = True Then
        GoTo 8
    End If
    
Rem ** Export to a new Excel
    Dim myXL As Excel.Application: Set myXL = New Excel.Application
    Dim wb As Workbook: Set wb = myXL.Workbooks.Add
    
    With wb.Sheets(1).Range("a1")
        For i = 0 To rs.Fields.Count - 1
            .Offset(, i).Value = rs.Fields(i).Name
        Next i
    End With
    
    'myXL.Visible = True
    
    With wb.Sheets(1)
        .Range("a2").CopyFromRecordset rs
        .Cells.EntireColumn.AutoFit
        .Cells.Rows.Height = 12.75
        .Rows(1).Insert
        With .Range("a1")
            .Value = "Stage 4 Referrals"
            .Font.Bold = True
            .Font.Size = 22
        End With
    End With

However it gets to:

.Range("a2").CopyFromRecordset rs

and then just stops, it populates the spreadsheet, returns no error but stops running.

I'm not dealing with massive amounts of data here (currently only 145 rows..!) so at a bit of a loss as to where to go form here...

Any help would, as ever, be greatly appreciated! =D


Cheers,

S.

 

[Salamander]

Hi guys,

Sorry about that - sorted it myself.

The function was called within another sub which had an On Error in place, have found where the error was and fixed it.... d'oh.

Cheers anyway guys =D

 

[SydneyGeek]

Hi guys,

Sorry about that - sorted it myself.

The function was called within another sub which had an On Error in place, have found where the error was and fixed it.... d'oh.

Cheers anyway guys =D

Cascading error handling can be a real pain

Denis