Private Sub command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"sql = "select * from 各部门设备采购流水账"
rs.Source = sql
Set rs.ActiveConnection = cn'在运行的时候这一句有问题,不知道错在那里啊。
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open sql, cn
If rs.RecordCount < 1 Then
MsgBox "没有数据导出 ", vbOKOnly + vbCritical, "错误提示 "
Else
If Dir("C:\Excel ", vbDirectory) = " " Then
MkDir ("C:\Excel ")
End If
If Dir("C:\Excel\gift.xls ") <> " " Then
Kill "C:\Excel\gift.xls "
End If
End IfDim i As Integer
Dim j As Integer
Dim xlExcel As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Set xlExcel = New Excel.Application
Set xlBook = xlExcel.Workbooks.Add
Set xlSheet = xlBook.Worksheets.AddxlSheet.cells.Columns(14).ColumnWidth = 20
xlSheet.cells(1, 1) = "序号"
xlSheet.cells(1, 2) = "经手人"
xlSheet.cells(1, 4) = "购置日期"
xlSheet.cells(1, 6) = "购置内容 "
xlSheet.cells(1, 8) = "总价"
xlSheet.cells(1, 10) = "分类"
xlSheet.cells(1, 12) = "经费来源"
xlSheet.cells(1, 14) = "备注"
xlSheet.cells(1, 12) = "部门"
xlSheet.cells(1, 14) = "部门类别"For i = 2 To rs.RecordCount + 1
For j = 1 To rs.Fields.Count
xlSheet.cells(i, j) = rs.Fields.Item(j - 1).Value
Next j
rs.MoveNext
Next i
xlBook.SaveAs FileFormat:=xlExcel9795
xlBook.SaveAs FileName:="C:\Excel\gift.xls "rs.Close
cn.CloseSet xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlExcel.Quit
Set xlExcel = Nothing
End Sub
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"sql = "select * from 各部门设备采购流水账"
rs.Source = sql
Set rs.ActiveConnection = cn'在运行的时候这一句有问题,不知道错在那里啊。
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open sql, cn
If rs.RecordCount < 1 Then
MsgBox "没有数据导出 ", vbOKOnly + vbCritical, "错误提示 "
Else
If Dir("C:\Excel ", vbDirectory) = " " Then
MkDir ("C:\Excel ")
End If
If Dir("C:\Excel\gift.xls ") <> " " Then
Kill "C:\Excel\gift.xls "
End If
End IfDim i As Integer
Dim j As Integer
Dim xlExcel As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Set xlExcel = New Excel.Application
Set xlBook = xlExcel.Workbooks.Add
Set xlSheet = xlBook.Worksheets.AddxlSheet.cells.Columns(14).ColumnWidth = 20
xlSheet.cells(1, 1) = "序号"
xlSheet.cells(1, 2) = "经手人"
xlSheet.cells(1, 4) = "购置日期"
xlSheet.cells(1, 6) = "购置内容 "
xlSheet.cells(1, 8) = "总价"
xlSheet.cells(1, 10) = "分类"
xlSheet.cells(1, 12) = "经费来源"
xlSheet.cells(1, 14) = "备注"
xlSheet.cells(1, 12) = "部门"
xlSheet.cells(1, 14) = "部门类别"For i = 2 To rs.RecordCount + 1
For j = 1 To rs.Fields.Count
xlSheet.cells(i, j) = rs.Fields.Item(j - 1).Value
Next j
rs.MoveNext
Next i
xlBook.SaveAs FileFormat:=xlExcel9795
xlBook.SaveAs FileName:="C:\Excel\gift.xls "rs.Close
cn.CloseSet xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlExcel.Quit
Set xlExcel = Nothing
End Sub
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"
’请注意此处
你定义了cn对象的连接字符串,但是没有OPEN,在后面给记录集对象付连接对象值的时候,就提示你,此连接没有打开
cn.opensql = "select * from 各部门设备采购流水账"
rs.Source = sql
Set rs.ActiveConnection = cn'在运行的时候这一句有问题,不知道错在那里啊。
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"
cn.open ’这一句话必须要,就是打开这个连接sql = "select * from 各部门设备采购流水账"
rs.Source = sql
Set rs.ActiveConnection = cn'在运行的时候这一句有问题,不知道错在那里啊。 这样就可以了