我想实现将Access中的数据导出到excel表中,代码如下:
Set aa = CreateObject("Excel.sheet")
i = 0
Set rec3 = CreateObject("adodb.recordset")
Set conn3 = CreateObject("adodb.connection")
strcon3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\milacron\wy.mdb"
conn3.Open strcon3
strsql3 = "select * from 采购价格查询表"
rec3.Open strsql3, conn3
If Not rec3.EOF Then
Do While Not rec3.EOF
j = 0
aa.application.Cells(i, 1).value = rec3!partno
aa.application.Cells(i, 2).value = rec3!average
aa.application.Cells(i, 3).value = rec3!price1
aa.application.Cells(i, 4).value = rec3!Currency1
aa.application.Cells(i, 5).value = rec3!Quantity1
aa.application.Cells(i, 6).value = rec3!Date1
aa.application.Cells(i, 6).value = rec3!Supplier1
rec3.MoveNext
i = i + 1
Loop
Set rec3 = Nothing
Set conn3 = Nothing
Else
MsgBox ("要导出的数据库表为空,请进行数据提取后再进行导出操作!")
Exit Sub
Set rec3 = Nothing
Set conn3 = Nothing
End Ifaa.saves "d:\milacron\1.xls"aa.application.quit
Set aa = Nothing都是在aa.saves "d:\milacron\1.xls"这里报错,说对象不支持该属性或方法!!请问这是怎么回事?请赐教!(该程序在vb6.0中调试!)
Set aa = CreateObject("Excel.sheet")
i = 0
Set rec3 = CreateObject("adodb.recordset")
Set conn3 = CreateObject("adodb.connection")
strcon3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\milacron\wy.mdb"
conn3.Open strcon3
strsql3 = "select * from 采购价格查询表"
rec3.Open strsql3, conn3
If Not rec3.EOF Then
Do While Not rec3.EOF
j = 0
aa.application.Cells(i, 1).value = rec3!partno
aa.application.Cells(i, 2).value = rec3!average
aa.application.Cells(i, 3).value = rec3!price1
aa.application.Cells(i, 4).value = rec3!Currency1
aa.application.Cells(i, 5).value = rec3!Quantity1
aa.application.Cells(i, 6).value = rec3!Date1
aa.application.Cells(i, 6).value = rec3!Supplier1
rec3.MoveNext
i = i + 1
Loop
Set rec3 = Nothing
Set conn3 = Nothing
Else
MsgBox ("要导出的数据库表为空,请进行数据提取后再进行导出操作!")
Exit Sub
Set rec3 = Nothing
Set conn3 = Nothing
End Ifaa.saves "d:\milacron\1.xls"aa.application.quit
Set aa = Nothing都是在aa.saves "d:\milacron\1.xls"这里报错,说对象不支持该属性或方法!!请问这是怎么回事?请赐教!(该程序在vb6.0中调试!)
Set xlapp = CreateObject("excel.application")
Set xlBook = xlapp.Workbooks.Add
Set XLSHEET = xlBook.Worksheets(1)......XLSHEET.Cells(i, 1) = = rec3!partno
...xlBook.Save
Set XLSHEET = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
...
这样就行阿
使用CopyFromRecordset语句,可以直接把记录集写入Excel工作表。
使用DAO的用意是支持Excel 97;如果使用ADO记录集,则仅支持Excel 2000。
下面是完整的例子:Dim db As dao.Database
Dim rs As dao.Recordset
Dim fd As dao.Field
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim cellCnt As Integer' Open the destination Excel workbook.
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.add
Set xlSheet = xlBook.ActiveSheetxlBook.PrintPreview
' Open the recordset.
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\bmkq.mdb")
Set rs = db.OpenRecordset("SELECT ......")
' Title
xlSheet.Cells(1, 1).Value = "考勤汇总表"
' Tabel Heads
cellCnt = 1
For Each fd In rs.Fields
Select Case fd.Type
Case dbBinary, dbGUID, dbLongBinary, dbVarBinary
' This type of data can't export to excel
Case Else
xlSheet.Cells(2, cellCnt).Value = fd.Name
xlSheet.Cells(2, cellCnt).Interior.ColorIndex = 33
xlSheet.Cells(2, cellCnt).Font.Bold = True
xlSheet.Cells(2, cellCnt).BorderAround xlContinuous
cellCnt = cellCnt + 1
End Select
Next
' This is all it takes to copy the contents
' of the recordset into the first worksheet
' of Book1.xls.
xlBook.Worksheets(1).Range("A3").CopyFromRecordset rs
xlApp.ActiveWindow.DisplayZeros = False
xlBook.Worksheets(1).Range("A3").Select
xlApp.Visible = True
' Clean up everything.
'xlBook.Save
'xlBook.Close False
'xlApp.Quit
rs.Close
db.Close
'Set xlBook = Nothing
'Set xlApp = Nothing
Set rs = Nothing
Set db = Nothing
exitsub:如果仅仅是导入数据,却不用如此大动干戈,下面的就行:Set conn3 = CreateObject("adodb.connection")
strcon3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\milacron\wy.mdb"
conn3.Open strcon3
strsql3 = "select * into [sheet1] in ""d:\milacron\1.xls"" ""Excel 8.0;"" from 采购价格查询表"conn3。Execute strsql3