VB 从SQL数据库中把数据导出到Excel表格中，怎么写？

Sub dataToExcel()

Dim conn As String

Dim rs As New ADODB.Recordset

Dim excel As Object

Dim workbook As Object

Dim sheet As Object

Dim i&, j&

    'SQL数据库的连接字串

    conn = "Provider=SQLOLEDB.1Data source=服务器地址initial catalog=数据库名称User Id=数据库登录账号Password=数据库登录账号"

    '打开到sql的连接，读取xxx表数据到rs

    rs.Open "select * from xxx", conn, adOpenKeyset, adLockReadOnly

    'excel对象、工作簿、工作表

    Set excel = CreateObject("Excel.Application")

    Set workbook = excel.workbooks().Add()

    Set sheet = workbook.sheets(1)

    excel.Visible = True

    '循环读取rs并写入到excel

    For i = 1 To rs.Fields.Count

        j = 1

        rs.MoveFirst

        Do While Not rs.EOF

            sheet.cells(j, i) = rs.Fields(i - 1).Value

            rs.MoveNext

            j = j + 1

        Loop

    Next

    rs.Close

    Set rs = Nothing

    

End Sub

Private Sub Command3_Click()

On Error Resume Next

Dim irow, icol, count, i As Integer

Dim irowcount, icolcount As Integer

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim bl As Boolean

Dim key As Integer

Dim RsUserTemp As Recordset

Dim RsOrderTemp As Recordset

Dim a, b

Dim aa As String

aa = Trim(Now)

Set xlApp = CreateObject("excel.application")

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets(1)

'If rs.RecordCount <1 Then

'MsgBox ("Error 没有记录")

'Exit Sub

'End If

irowcount = rs.RecordCount

icolcount = 18

count = 0

rs.MoveFirst

For icol = 0 To 2

'xlSheet.Cells(1, 1).Value = "查询数据" '加标头；

Next icol

xlSheet.Cells(1, 1).Value = "时间" '加标头；

xlSheet.Cells(1, 2).Value = "药开度" '加标头；

xlSheet.Cells(1, 3).Value = "药瞬时流量" '加标头；

xlSheet.Cells(1, 4).Value = "药累计流量" '加标头；

xlSheet.Cells(1, 5).Value = "矿浆浓度" '加标头；

xlSheet.Cells(1, 6).Value = "矿浆流量" '加标头

xlSheet.Cells(1, 7).Value = "酸1开度" '加标头；

xlSheet.Cells(1, 8).Value = "酸1瞬时流量" '加标头；

xlSheet.Cells(1, 9).Value = "酸1累计流量" '加标头

xlSheet.Cells(1, 10).Value = "酸2开度" '加标头；

xlSheet.Cells(1, 11).Value = "酸2瞬时流量" '加标头；

xlSheet.Cells(1, 12).Value = "酸2累计流量" '加标头

xlSheet.Cells(1, 13).Value = "酸3开度" '加标头；

xlSheet.Cells(1, 14).Value = "酸3瞬时流量" '加标头；

xlSheet.Cells(1, 15).Value = "酸3累计流量" '加标头

xlSheet.Cells(1, 16).Value = "酸4开度" '加标头；

xlSheet.Cells(1, 17).Value = "酸4瞬时流量" '加标头；

xlSheet.Cells(1, 18).Value = "酸4累计流量"

xlSheet.Cells(1, 19).Value = "酸5开度"

xlSheet.Cells(1, 20).Value = "酸5瞬时流量"

xlSheet.Cells(1, 21).Value = "酸5累计流量"

Adodc1.Recordset.MoveFirst

For a = 2 To 200

b = 1

If Not Adodc1.Recordset.EOF Then

xlSheet.Cells(a, b) = Adodc1.Recordset("时间")

xlSheet.Cells(a, b + 1) = Adodc1.Recordset("药开度")

xlSheet.Cells(a, b + 2) = Adodc1.Recordset("药瞬时流量")

xlSheet.Cells(a, b + 3) = Adodc1.Recordset("药累计流量")

xlSheet.Cells(a, b + 4) = Adodc1.Recordset("酸1开度")

xlSheet.Cells(a, b + 5) = Adodc1.Recordset("酸1瞬时流量")

xlSheet.Cells(a, b + 6) = Adodc1.Recordset("酸1累计流量")

xlSheet.Cells(a, b + 7) = Adodc1.Recordset("酸2开度")

xlSheet.Cells(a, b + 8) = Adodc1.Recordset("酸2瞬时流量")

xlSheet.Cells(a, b + 9) = Adodc1.Recordset("酸2累计流量")

xlSheet.Cells(a, b + 10) = Adodc1.Recordset("酸3开度")

xlSheet.Cells(a, b + 11) = Adodc1.Recordset("酸3瞬时流量")

xlSheet.Cells(a, b + 12) = Adodc1.Recordset("酸3累计流量")

xlSheet.Cells(a, b + 13) = Adodc1.Recordset("酸4开度")

xlSheet.Cells(a, b + 14) = Adodc1.Recordset("酸4瞬时流量")

xlSheet.Cells(a, b + 15) = Adodc1.Recordset("酸4累计流量")

xlSheet.Cells(a, b + 16) = Adodc1.Recordset("酸5开度")

xlSheet.Cells(a, b + 17) = Adodc1.Recordset("酸5瞬时流量")

xlSheet.Cells(a, b + 18) = Adodc1.Recordset("酸5累计流量")

Else

Exit For

End If

Adodc1.Recordset.Move 1

Next

rs.MoveFirst

xlSheet.Cells(2, 2).Value = Trim(Text1.Text) &Trim(Text2.Text)

For irow = 0 To irowcount - 1

Set RsUserTemp = New Recordset

RsUserTemp.CursorLocation = adUseClient

RsUserTemp.Open "select * from 状态数据 " _

&"where user0_id=" &rs!user0_id, Cn, adOpenStatic, adLockReadOnly

xlSheet.Cells(irow + 4, 1).Value = count + 1

xlSheet.Cells(irow + 4, 2).Value = RsUserTemp!user0_id

xlSheet.Cells(irow + 4, 3).Value = RsUserTemp!user0_name

xlSheet.Cells(irow + 4, 4).Value = RsUserTemp!Address

xlSheet.Cells(irow + 4, 5).Value = RsUserTemp!callno1

Set RsUserTemp = Nothing

Set RsOrderTemp = New Recordset

RsOrderTemp.CursorLocation = adUseClient

RsOrderTemp.Open "select * from 状态数据 where user0_id = " _

If RsOrderTemp.RecordCount = 0 Then

Else

RsOrderTemp.MoveFirst

Do While (Not RsOrderTemp.EOF)

key = 0

key = Val(Mid(str(RsOrderTemp!Order_Time), 6, 2))

Select Case key

Case 0

Exit Do

Case 1

xlSheet.Cells(irow + 4, 6).Value = RsOrderTemp!Order_Amount

Case 2

xlSheet.Cells(irow + 4, 7).Value = RsOrderTemp!Order_Amount

Case 3

xlSheet.Cells(irow + 4, 8).Value = RsOrderTemp!Order_Amount

Case 4

xlSheet.Cells(irow + 4, 9).Value = RsOrderTemp!Order_Amount

Case 5

xlSheet.Cells(irow + 4, 10).Value = RsOrderTemp!Order_Amount

Case 6

xlSheet.Cells(irow + 4, 11).Value = RsOrderTemp!Order_Amount

Case 7

xlSheet.Cells(irow + 4, 12).Value = RsOrderTemp!Order_Amount

Case 8

xlSheet.Cells(irow + 4, 13).Value = RsOrderTemp!Order_Amount

Case 9

xlSheet.Cells(irow + 4, 14).Value = RsOrderTemp!Order_Amount

Case 10

xlSheet.Cells(irow + 4, 15).Value = RsOrderTemp!Order_Amount

Case 11

xlSheet.Cells(irow + 4, 16).Value = RsOrderTemp!Order_Amount

Case 12

xlSheet.Cells(irow + 4, 17).Value = RsOrderTemp!Order_Amount

End Select

RsOrderTemp.MoveNext

Loop

End If

Set RsOrderTemp = Nothing

count = count + 1

rs.MoveNext

If bl Then '因为第一条记录还未导出所以让指针回滚；

rs.MovePrevious

End If

Next

xlApp.Visible = True

xlBook.Save

Set xlApp = Nothing

End Sub

这是我的一个代码，参考一下吧。。。导出到EXCEL的 '百度Hi群&飞度编程学社 1195277

strsourcepath = App.path &"\data\prtset.mdb"

strreportpath = "D:\test.xls"

你没有发现问题吗？

你看看上面的两句，你只是获取了prtset.mdb数据库的路径，然后你就给导出到

test,xls文件内了。

期间你没做任何处理，当然是全部都导进去了。

在那两句话中间加一句SQL语句，查询出你想要的内容，你在进行导入 *** 作不就可以了吗

---