View Single Post
  #5 (permalink)  
Old November 2nd, 2004, 10:41 AM
clueless_may clueless_may is offline
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I changed my code to the following: (I "pasted" each cell one at a time) and now the error I get is Run-time error '1004' Application-defined or object-defined error. Any suggestions would be greatly appreciated!

Thanks!!
Private Sub cmdGetReport_Click()

Dim adoConn As New ADODB.Connection
Dim adoRec As New ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim strsql As String
Dim xlWB As Excel.Workbook
Dim strfilename As String
Dim i As Long

Set xlApp = New Excel.Application

adoConn.CommandTimeout = 0
adoConn.Open "DSN=dw"




strsql = "select * from dw_play.mays_proc_test"
adoRec.Open strsql, adoConn


Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Worksheets("Sheet1")

For i = 0 To adoRec.Fields.Count - 1
   xlSheet.Cells(1, i + 1).Value = adoRec.Fields(i).Name
Next


Dim rownum As Long
adoRec.MoveFirst
Do Until adoRec.EOF
      *******ERRORS HERE***********************
      xlSheet.Cells(rownum, 1).Value = adoRec!tran_start_date
      xlSheet.Cells(rownum, 2).Value = adoRec!tran_end_date
      xlSheet.Cells(rownum, 3).Value = adoRec!total_transactions
      xlSheet.Cells(rownum, 4).Value = adoRec!orig_less_curr
      xlSheet.Cells(rownum, 5).Value = adoRec!percent_orig_less_curr
      xlSheet.Cells(rownum, 6).Value = adoRec!orig_less_sell
      xlSheet.Cells(rownum, 7).Value = adoRec!percent_orig_less_sell
      xlSheet.Cells(rownum, 8).Value = adoRec!curr_less_sell
      xlSheet.Cells(rownum, 9).Value = adoRec!percent_curr_less_sell
      adoRec.MoveNext
      rownum = rownum + 1
Loop


strfilename = "C:\TESTING2.xls"
If Len(Dir(strfilename)) Then
    Kill strfilename
End If

adoConn.Close

End Sub