在WIN7+OFFICE2003下导出数据到EXCEL文件时失败,如何解决具体导出代码如下:
Public Function ExporToExcel(strOpen As String)
On Error GoTo Errc
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
With Rs_Data
If .State = adStateOpen Then
.Close
End If
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = strOpen
.Open
End With
With Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If
'记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count
End With
Set xlApp = CreateObject("Excel.Application") ‘运行到此处时 直接提示“ActiveX 部件不能创建对象”
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Name = "黑体"
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 10
'设标题为黑体字
' .Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
With xlSheet.PageSetup
.Orientation = xlLandscape
End With
xlApp.Application.Visible = True
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
Exit Function
Errc:
MsgBox ERR.Description
End Function运行到 Set xlApp = CreateObject("Excel.Application") ‘运行到此处时 直接提示“ActiveX 部件不能创建对象”
在XP+OFFICE2003的环境下,同一程序,同样操作,导出数据到EXCEL文件,无异常。请问如何解决。
Public Function ExporToExcel(strOpen As String)
On Error GoTo Errc
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
With Rs_Data
If .State = adStateOpen Then
.Close
End If
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = strOpen
.Open
End With
With Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If
'记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count
End With
Set xlApp = CreateObject("Excel.Application") ‘运行到此处时 直接提示“ActiveX 部件不能创建对象”
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Name = "黑体"
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 10
'设标题为黑体字
' .Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
With xlSheet.PageSetup
.Orientation = xlLandscape
End With
xlApp.Application.Visible = True
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
Exit Function
Errc:
MsgBox ERR.Description
End Function运行到 Set xlApp = CreateObject("Excel.Application") ‘运行到此处时 直接提示“ActiveX 部件不能创建对象”
在XP+OFFICE2003的环境下,同一程序,同样操作,导出数据到EXCEL文件,无异常。请问如何解决。
解决方案 »
- 兔年顶呱呱之无理由200散粉贴,来者靠前均得分!!!
- 关于调用视频影片的问题
- 菜鸟入门,关于指定Sstab控件面版
- 请问:哪位有IIS 5的单独安装版的下载地址? (我系统是专业版XP。)
- 在msflexgrid中动态的显示字段
- 在modem通信编程中,两台计算机分别装一外猫,内猫,拨号后出现“connect 9600 ”信息,但两台计算机间不能互传数据!
- 怎么给SSTab控件动态添加其它控件,大家帮我看看!!
- 我发现CSDN的分可以作弊,大家讨论
- 请问vb怎样连接BD2数据库?????ji急!!!!
- Winsock控件一问:
- DataGridView输出的表格中,如何做到点击左边,就能选中行,并对行中数据进行操作?
- VB如何释放EXCEL
在你的 exe 文件上右键菜单,用管理员运行,试试看。