I have the following code. It gives me "Run-time error '1004': Application-defined or object-defined error"
Search for '****' for the line with the error. I need to make this thing work. I need code to help fix this.
Any help greatly appreciated.
Chess
--------------------------
----------
----------
--------
Private Sub ExportData()
On Error GoTo errHandle
Dim sSql As String
Dim rs As New ADODB.Recordset
Dim sTradeId As String, iFundNumber As Long
Dim intCount As Integer
Dim sExportLogFileName As String
Dim fileNum As Long
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Applic
ation")
sExportLogFileName = App.Path & "\" & cmbFundNumber.Text & ".xls"
fileNum = FreeFile
If Dir(sExportLogFileName) <> "" Then
Kill sExportLogFileName
End If
intCount = 1
sSql = "Select RTE_TRADE_ID, FUND_NUM from ATI_RTE_TRADES WHERE FUND_NUM = " & cmbFundNumber.Text
sSql = sSql & " AND POST_DATE is null"
rs.Open sSql, gDBOracle, adOpenStatic
' Set header values for sheet 1.
With xlApp.Worksheets("Sheet1")
.Rows(intC
ount) '**** Error on this line****
.Font.Bold = True
.Cells(1, 5).Value = "Trade Id"
.Cells(1, 6).Value = "Fund Number"
End With
While Not rs.EOF
sTradeId = rs.Fields("RTE_TRADE_ID")
iFundNumber = rs.Fields("FUND_NUM")
intCount = intCount + 1
Call AddToSheet(intCount, sTradeId, iFundNumber)
rs.MoveNext
Wend
xlApp.ActiveWorkbook.Works
heets(1).S
aveAs sExportLogFileName
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
Exit Sub
errHandle:
MsgBox "Error " & Err.Number & " in Sub ExportData. The error is - " & Err.Description, vbOKOnly + vbCritical, APPNAME
End Sub
Public Function AddToSheet(intCount As Integer, sTradeId As String, iFundNumber As Long)
' This function adds recordset data to sheet one row at a time.
' Values are passed from GetData procedure.
With xlApp.ActiveWorkbook.Works
heets("She
et1").Rows
(intCount)
.Cells(10, 5).Value = sTradeId
.Cells(10, 6).Value = iFundNumber
End With
End Function