accessVBA编程出错：类不支持自动化或不支持期望的接口

ming78261499a 2013-01-30 09:04:51

下面代码出错

Private Sub 导出到excel_Click()

On Error GoTo errit



Dim oExcel As Object

Dim oBook As Object

Dim oSheet As Object

Dim i As Integer



Set oExcel = CreateObject("Excel.Application")

Set oBook = oExcel.Workbooks.Add





Me.主查询子窗体.Form.Recordset.MoveFirst



For i = 0 To Me.主查询子窗体.Form.Recordset.Fields.Count - 1

oBook.worksheets(1).Cells(1, i + 1).Value = Me.主查询子窗体.Form.Recordset.Fields(i).Name

Next







'oBook.worksheets(1).colums(1).ColumnWidth = 15

'oBook.worksheets(1).row(1).RowHeight = 13.5

'oBook.worksheets(1).range.ColumnWidth = 15

'oBook.worksheets(1).range.RowHeight = 13.5

oBook.worksheets(1).Range("A2:P2").ColumnWidth = 9

oBook.worksheets(1).Range("Q2:U2").ColumnWidth = 18

oBook.worksheets(1).Range("A2:A1000").RowHeight = 13.5

oBook.worksheets(1).Range("A2").CopyFromRecordset Me.主查询子窗体.Form.Recordset

'oBook.ShowDlgOpenFile = ("d:\人才资料查询.xls")





oBook.SaveAs FileName:="c:\人才资料查询.xls"



MsgBox "查询成功导出为c:\人才资料查询.xls"



errexit:

oBook.Close False

oExcel.Quit

Set oBook = Nothing

Set oExcel = Nothing

Exit Sub



errit:

MsgBox "错误号为" & Err.Number & " 错误说明：" & Err.Description

Resume errexit



End Sub

上面代码中“oBook.worksheets(1).Range("A2").CopyFromRecordset Me.主查询子窗体.Form.Recordset”在win7 32位系统上access2007与2010都没问题，但是在有些32位的win7和xp上都出现错误430：类不支持自动化或不支持期望的接口问题比较急，N个论坛上的帖子我都参考过了，转成mdb，引入dll引不了，请高手指点，谢谢

...全文

473 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

WWWWA 2013-02-17

打赏
举报

将生成子窗体的SQL语句提出，生成表，再导出XLS，用OLE方式打开XLS，设置格式

ming78261499a 2013-02-17

打赏
举报

谢谢楼上两位，我用了笨笨的办法……一个个生成数据项，速度很慢，不过还好解决了问题~楼上的办法我马上试试，真心谢谢两位，大过年的新年快乐

dong_y888 2013-02-01

打赏
举报

不使用那个方法，用代码解决

Private Sub 导出到excel_Click()
On Error GoTo errit

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim i As Integer,j as integer

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add


Me.主查询子窗体.Form.Recordset.MoveFirst

For i = 0 To Me.主查询子窗体.Form.Recordset.Fields.Count - 1
oBook.worksheets(1).Cells(1, i + 1).Value = Me.主查询子窗体.Form.Recordset.Fields(i).Name
Next



'oBook.worksheets(1).colums(1).ColumnWidth = 15
'oBook.worksheets(1).row(1).RowHeight = 13.5
'oBook.worksheets(1).range.ColumnWidth = 15
'oBook.worksheets(1).range.RowHeight = 13.5
oBook.worksheets(1).Range("A2:P2").ColumnWidth = 9
oBook.worksheets(1).Range("Q2:U2").ColumnWidth = 18
oBook.worksheets(1).Range("A2:A1000").RowHeight = 13.5
'oBook.worksheets(1).Range("A2").CopyFromRecordset Me.主查询子窗体.Form.Recordset
'oBook.ShowDlgOpenFile = ("d:\人才资料查询.xls")
j=2
Me.主查询子窗体.Form.Recordset.movefirst
do while not Me.主查询子窗体.Form.Recordset.eof
    For i = 0 To Me.主查询子窗体.Form.Recordset.Fields.Count - 1
       oBook.worksheets(1).Cells(j, i + 1).Value = Me.主查询子窗体.Form.Recordset.Fields(i).value
    Next
    j=j+1
    Me.主查询子窗体.Form.Recordset.movenext
loop

oBook.SaveAs FileName:="c:\人才资料查询.xls"

MsgBox "查询成功导出为c:\人才资料查询.xls"

errexit:
oBook.Close False
oExcel.Quit
Set oBook = Nothing
Set oExcel = Nothing
Exit Sub

errit:
MsgBox "错误号为" & Err.Number & " 错误说明：" & Err.Description
Resume errexit

End Sub

WWWWA 2013-02-01