VB数据库连接和导入EXCEL

VB数据库连接和导入EXCEL
Dim strDBFile As String
Dim gstrFilePath As String
Dim gstrConn As String
Dim gDatabase As ADODB.Connection
Dim isConnect As Boolean
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
'连接数据库
Adodc1.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" + App.Path
+ "\DATA\ysdzltddata.mdb;Persist Security Info=False" 'conn.ConnectionTimeout = 30
conn.Open (Adodc1.ConnectionString)'打开数据库
'gDatabase = New ADODB.Connection
'gDatabase.Open (gstrConn)
isConnect = True
sql = "select * from qpxx"
Adodc1.RecordSource = sql
Adodc1.Refresh
'向数据库插入数据
sql = "Insert into QPXX(windowid,
ywid,NWP,DWP,NWT,DWLT,DQP,DAPT,DTIMER,TTIMER)"
sql = sql & " values("
sql = sql & "'" & CStr(WINID) & "'" '窗口
sql = sql & ",'" & YWID & "'" '业务号
sql = sql & ",'" & CStr(ALLW AI) & "'" '等待人数
sql = sql & ",'" & CStr(MALLW AI) & "'" '当天等候最大人数
sql = sql & ",'" & CStr(NW_TIME) & "'" '当前等候办理业务最长时间（秒）
sql = sql & ",'" & CStr(W_TIME) & "'" '当天等候最长时间（秒） sql = sql & ",'" & CStr(W AITP) & "'" '今天已取票数
sql = sql & ",'" & CStr(A V_TIME) & "'" '今天平均排队时间
sql = sql & ",'" & CStr(Format(Date, "yyyymmdd")) & "'" '号号日期 sql = sql & ",'" & CStr(Format(TIME, "hhmmss")) & "')" '叫号时间 conn.Execute (sql)
'module模块
'把数据导入EXCEL中
Dim ExcelApp As Object 'Excel.Application
Dim ExcelWorkBook As Object 'Excel.workbook
Dim ExcelWorkSheet As Object 'Excel.worksheet
Public Sub printzh()
Dim ExcelSheet As Object
Dim strSource, strDestination As String
On Error Resume Next
strSource = App.Path & "\DATA\bb.xls"
'strDestination = .DirectoryPath & "\DATA\Temp.xls"
strDestination = App.Path & "\DATA\Temp.xls"
'FileCopy(strSource, strDestination)
FileCopy strSource, strDestination
'Try
Set ExcelApp = CreateObject("Excel.Application")
'Set ExcelApp = CreateObject("Excel.Sheet")
'打开工作薄
Set ExcelWorkBook = ExcelApp.Workbooks.Open(strDestination)
'选择工作簿
Set ExcelWorkSheet = ExcelWorkBook.Sheets("Sheet1")
' Dim i As Long, j As Long, k As Long
' For i = 0 To Adodc1.Recordset.RecordCount - 1
' If k = DataGrid1.VisibleRows Then
' DataGrid1.Scroll(0, DataGrid1.VisibleRows)
' k = 0
' End If
ExcelApp.Application.Visible = True '设置Application 对象使Excel 可见
Dim j As Integer
'在表格的第一个单元中写些文本
ExcelSheet.cells(1, 1).Value = "This is column A, row 1"
ExcelWorkSheet.cells(1, 1) = "取号信息汇总表"
ExcelWorkSheet.cells(1, 10) = Format(Date, "yyyymmdd") ' & Space(2)
ExcelWorkSheet.cells(2, 1) = "窗口号"
ExcelWorkSheet.cells(2, 2) = "业务号"
ExcelWorkSheet.cells(2, 3) = "当前总等待人数"
ExcelWorkSheet.cells(2, 4) = "当天总等候最大人数"
ExcelWorkSheet.cells(2, 5) = "当前等候办理业务最长时间"
ExcelWorkSheet.cells(2, 6) = "当天总等候最长时间"
ExcelWorkSheet.cells(2, 7) = "今天已取票数"
ExcelWorkSheet.cells(2, 8) = "今天平均排队时长"
ExcelWorkSheet.cells(2, 9) = "叫号日期"
ExcelWorkSheet.cells(2, 10) = "叫号时间"
For j = 1 To Form1.AdodcD.Recordset.RecordCount
' Form1.Adodc1.Recordset.Move(j)
' Adodc1.Recordset.MoveFirst()
'PrintDB(DataGrid1.Columns(0).Value,
DataGrid1.Columns(1).Text, DataGrid1.Columns(2).Text)
ExcelWorkSheet.cells(j + 3, 1) = Form1.DataGrid1.Columns(0).Text
ExcelWorkSheet.cells(j + 3, 2) = Form1.DataGrid1.Columns(1).Text
ExcelWorkSheet.cells(j + 3, 3) = Form1.DataGrid1.Columns(2).Text
ExcelWorkSheet.cells(j + 3, 4) = Form1.DataGrid1.Columns(3).Text
ExcelWorkSheet.cells(j + 3, 5) = Form1.DataGrid1.Columns(4).Text
ExcelWorkSheet.cells(j + 3, 6) = Form1.DataGrid1.Columns(5).Text
ExcelWorkSheet.cells(j + 3, 7) = Form1.DataGrid1.Columns(6).Text
ExcelWorkSheet.cells(j + 3, 8) = Form1.DataGrid1.Columns(7).Text
ExcelWorkSheet.cells(j + 3, 9) = Form1.DataGrid1.Columns(8).Text
ExcelWorkSheet.cells(j + 3, 10) = Form1.DataGrid1.Columns(9).Text
ExcelWorkSheet.cells(j + 3, 11) = Form1.DataGrid1.Columns(10).Text
Form1.AdodcD.Recordset.MoveNext
' Form1.Adodc1.Recordset.Move(j + 1)
'i = i + 1
Next j
' While Form1.Adodc1.Recordset.EOF = False
' sum = sum + CInt(CStr(Form1.Adodc1.Recordset.Fields("一般次数")))
' Form1.Adodc1.Recordset.MoveNext()
' End While
' ExcelWorkSheet.Cells(32, 7) = sum
' ExcelWorkSheet.Cells(Form1.Adodc1.Recordset.RecordCount + 1, 5)
' ExcelWorkSheet.Cells(Form1.Adodc1.Recordset.RecordCount + 1, 6)
' ExcelWorkSheet.Cells(Form1.Adodc1.Recordset.RecordCount + 1, 7)
' k = k + 1
' Next i
'向VBA 传数据
'PrintDB("1", "2", "3") '可以是DataRow
'显示Excel
Form1.AdodcD.Recordset.MoveFirst
ExcelApp.Visible = True
ExcelWorkBook.SaveAs App.Path & "\DATA\排队信息表.xls" '保存文件
'ExcelSheet.SaveAs "C:\ TEST.DOC"
' ExcelWorkSheet.PrintOut() '执行打印
' ExcelApp.Quit() '退出EXCEL
'删除Excel引用
ExcelWorkSheet = Nothing
ExcelWorkBook = Nothing
ExcelApp = Nothing
'垃圾回收
GC.Collect
' Catch ex As Exception
' MsgBox (ex)
' End Try
End Sub。