Private Function RsToExcel(ByVal Rs As Object, ByVal vFilename As String, ByRef lSumRecord As Long) As Long
'把记录集的内容保存到excel文件中
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Sheets
Dim j As Long, i As Long Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'#########方法一######################
lSumRecord = 0
' On Error Resume Next
While Not Rs.EOF
j = j + 1
pgbRead.Value = j
For i = 0 To Rs.Fields.Count - 2
oSheet.Range(Trim(Chr(97 + i)) & CLng(j)) = Trim(Rs.Fields(i + 1))
DoEvents
Next
lSumRecord = lSumRecord + 1
Rs.MoveNext
DoEvents
Wend
pgbRead.Value = 1
'#########方法二#######################
oSheet.Range("A").CopyFromRecordset Rs
If chkHavePassword.Value Then
oBook.SaveAs vFilename, , sRPassword, sWPassword
Else
oBook.SaveAs vFilename
End If
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
End Function
'把记录集的内容保存到excel文件中
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Sheets
Dim j As Long, i As Long Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'#########方法一######################
lSumRecord = 0
' On Error Resume Next
While Not Rs.EOF
j = j + 1
pgbRead.Value = j
For i = 0 To Rs.Fields.Count - 2
oSheet.Range(Trim(Chr(97 + i)) & CLng(j)) = Trim(Rs.Fields(i + 1))
DoEvents
Next
lSumRecord = lSumRecord + 1
Rs.MoveNext
DoEvents
Wend
pgbRead.Value = 1
'#########方法二#######################
oSheet.Range("A").CopyFromRecordset Rs
If chkHavePassword.Value Then
oBook.SaveAs vFilename, , sRPassword, sWPassword
Else
oBook.SaveAs vFilename
End If
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
End Function
我不想要调用excel对象,
使用ODBC不是可以用ADO打开Excel表吗?
而且我记得DAO还是ADO是可以把表保存到文本的,
我想应该有导出到Excel的方法。很感谢icnetcn(yoyo之无双) 的回复,
有没有别的方法?
DAO的DIm sql as string
Dim DaoT As dao.Database
Set DaoT = Workspaces(0).OpenDatabase("DB")
Sql = "SELECT * INTO [Excel 8.0;DATABASE="Name"].[表名] FROM [DB表名] "
DaoT.Execute sql
我试试同样的方法ADO里面可不可以,
就这样吧。实在没有ADO的DAO也将就了,
因为调用Excel9.0对象会比这个方法慢很多~呵呵