怎样把一个VB6.0中的结果集,绑定到Excel中?
给个例子看看,谢谢
给个例子看看,谢谢
解决方案 »
- treeview与数据库连接显示问题,有还在用VB6的同仁吗?急……
- 如何把图片保存到sql数据库中
- VB中如何引用.net开发的DLL?
- 不用开、关文件就能回到文件头,有什么方法???
- 请教:如何给另一个窗口的combo box发送一个CBN_SELCHANGE 消息
- FileSystemObject 对象 创建错误的问题
- VB怎么找不到工作阿!!
- 我想实现像纸牌中拖动纸牌它可以跟着鼠标走的效果,帮我!
- 求助高手~~~~~~~INI文件内容解读
- 请问用C语言写的DLL怎么在VB中调用(有指针的)????
- 【讨论】 DES3 加密 各种语言写的文字或文本 是否还有它的价值
- 用VB6作的系统,打包运行后,系统提示进行Office的配置,请问如何解决?
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 参数:rs 记录集 strDestination文件名
'* 返回值:
'* 时间:20031015
'*********************************************************Public Function ExporToExcel(rs As Recordset, strDestination As String) As Integer' 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
Dim fso As New FileSystemObject
If fso.FileExists(App.Path + "\" + strDestination) Then
End If
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
' Set xlBook = xlApp.Workbooks.Open(App.Path + "\" + "temp.xls")
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = False
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(rs, 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
' xlApp.Application.Visible = True
xlSheet.SaveAs (App.Path + "\" + strDestination)
xlQuery.Delete
' xlBook.Close
Set xlQuery = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Function
Sub getSQL(ByVal strSQL As String)
Dim rst As New adodb.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
Set rst = CreateObject("ADODB.Recordset")
rst.Open strSQL, cn, 3, 1
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("sheet1")
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
xlWs.Cells(2, 1).CopyFromRecordset rst
Else
recArray = rst.GetRows
recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = TransposeDim(recArray)
End If
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit
rst.Close
End SubFunction TransposeDim(v As Variant) As Variant
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X
TransposeDim = tempArrayEnd Function