1 Replies - 8522 Views - Last Post: 15 June 2010 - 12:14 AM Rate Topic: -----

#1 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 155
  • Joined: 20-July 09

How to export data from sql to excel?

Posted 14 June 2010 - 10:40 PM

I having problem in importing data from sql to excel.
here my code:
Private Sub CausticSodaExcel()
Dim adoRs As New ADODB.Recordset
Dim exl, xlbook, xlsht
Dim strSQl As String, dteFrom As String, dteTo As String

dteFrom = Format(dtFrom.Value, "mm/dd/yyyy 00:00:00")
dteTo = Format(dtTo.Value, "mm/dd/yyyy 23:59:59")

strSQl = "Select TestDate,Shift,Analyzedby,Reviewedby,DelNo,Quantity,resultpurity,resultSG,passfailpurity,passfailsg" & _
            "from qa_wl_CausticSoda where TestDate BETWEEN '" & dteFrom & "' and '" & dteTo & "' order by causticsodaid asc"
            
OpenRecSet adoRs, strSQl

If Not adoRs.EOF Then
    Set exl = CreateObject("Excel.application")
    Set xlbook = exl.Workbooks.Open(App.Path & "\Reports\CausticSoda.xlt")
    Set xlsht = xlbook.Sheets("data")

    xlsht.Activate
    xlsht.Cells(9, 1).CopyFromRecordset adoRs
    xlsht.Name = "Phase " & phase
    xlsht.range("A9:A65536").Select
    exl.Selection.NumberFormat = "mmm-dd-yy ;@"
    xlsht.Cells(6, 1).Select
    exl.Application.Visible = True
Else
    MsgBox "No Record Found!", vbInformation
End If
adoRs.Close
End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: How to export data from sql to excel?

#2 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 155
  • Joined: 20-July 09

Re: How to export data from sql to excel?

Posted 15 June 2010 - 12:14 AM

I figure out the problem it is caused by the sql command which a table name is too long:

strSQl = "Select TestDate,Shift,Analyzedby,Reviewedby,DelNo,Quantity,resultpurity,resultSG,passfailpurity,passfailsg" & _ 
"from qa_wl_CausticSoda where TestDate BETWEEN '" & dteFrom & "' and '" & dteTo 


i changed it to:

strSQl = "Select * from qa_wl_CausticSoda where TestDate BETWEEN '" & dteFrom & "' and '" & dteTo 

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1