请做适当修改Private Sub mnuFileExport_Click() '导出
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
rs.Open "Select * From mydata", conn
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rs.Fields.count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
xlWs.Cells(2, 1).CopyFromRecordset rs
Else
recArray = rs.GetRows
recCount = UBound(recArray, 2) + 1
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 iCol
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit rs.Close
Set rs = Nothing
Set xlWs = Nothing
Set xlWb = Nothing Set xlApp = Nothing
End Sub
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
rs.Open "Select * From mydata", conn
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rs.Fields.count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
xlWs.Cells(2, 1).CopyFromRecordset rs
Else
recArray = rs.GetRows
recCount = UBound(recArray, 2) + 1
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 iCol
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit rs.Close
Set rs = Nothing
Set xlWs = Nothing
Set xlWb = Nothing Set xlApp = Nothing
End Sub
如果用DAO时我知道怎么做,但如果用ADO时,不同数据源之间的数据如何用SQL互相传递呢?
http://support.microsoft.com/support/kb/articles/Q295/6/46.ASP?LN=EN-US&SD=gn&FR=0&qry=&rnk=3&src=DHCS_MSPSS_gn_SRCH&SPR=VBBHOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/support/kb/articles/Q257/8/19.ASP?LN=EN-US&SD=gn&FR=0&qry=&rnk=3&src=DHCS_MSPSS_gn_SRCH&SPR=VBBHOWTO: Transfer Data from an ADO Recordset to Excel with Automation
http://support.microsoft.com/support/kb/articles/Q246/3/35.ASP?LN=EN-US&SD=gn&FR=0&qry=&rnk=3&src=DHCS_MSPSS_gn_SRCH&SPR=VBB