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

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!

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

Dim rownum As Long
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
      rownum = rownum + 1

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


End Sub