我在做数据导出到excel时,出现下面的错误,请问该如何解决?
Public Function ExporToExcel(strOpen As String)
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Connect As New ADODB.Connection
Dim Rs_Data As New ADODB.Recordset
Dim IrowCount As Integer
Dim IcolCount As Integer
Dim ConString As String
ConString = "DSN=student"
Connect.Open ConString
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
Set xlApp = CreateObject("Excel.Application")
'Set xlBook = Nothing
'Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
Rs_Data.Open strOpen, Connect, adOpenStatic, adLockOptimistic
With Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If
'记录总数
IrowCount = .RecordCount
'字段总数
IcolCount = .Fields.Count
End With
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range(a1))
With xlSheet
.Range(.Cells(1, 1), .Cells(1, IcolCount)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, IcolCount)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(IrowCount + 1, IcolCount)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
xlApp.Application.Visible = True
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = NothingEnd FunctionPrivate Sub Form_Load()
TrainSqlString = "select * from 学生成绩"
ExporToExcel (TrainSqlString)
End Sub
在运行过程中弹出一个错误信息: 指向Dim xlApp As New Excel.Application,编译错误:用户定义类型未定义,请问是什么原因啊,大家帮帮我吧!
Public Function ExporToExcel(strOpen As String)
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Connect As New ADODB.Connection
Dim Rs_Data As New ADODB.Recordset
Dim IrowCount As Integer
Dim IcolCount As Integer
Dim ConString As String
ConString = "DSN=student"
Connect.Open ConString
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
Set xlApp = CreateObject("Excel.Application")
'Set xlBook = Nothing
'Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
Rs_Data.Open strOpen, Connect, adOpenStatic, adLockOptimistic
With Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If
'记录总数
IrowCount = .RecordCount
'字段总数
IcolCount = .Fields.Count
End With
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range(a1))
With xlSheet
.Range(.Cells(1, 1), .Cells(1, IcolCount)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, IcolCount)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(IrowCount + 1, IcolCount)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
xlApp.Application.Visible = True
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = NothingEnd FunctionPrivate Sub Form_Load()
TrainSqlString = "select * from 学生成绩"
ExporToExcel (TrainSqlString)
End Sub
在运行过程中弹出一个错误信息: 指向Dim xlApp As New Excel.Application,编译错误:用户定义类型未定义,请问是什么原因啊,大家帮帮我吧!
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
For I = 0 To Adodc1.Recordset.RecordCount - 1
For j = 0 To Adodc1.Recordset.Fields.Count - 1
DataGrid1.Row = I
DataGrid1.Col = j
xlSheet.Cells(I + 1, j + 1) = DataGrid1.Text
Next j
Next I
End Sub