by Angsuman Banerji (23 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 4th January 2008
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Export Recordset to EXCEL file
VBcoders
On Error GoTo Err:
Dim i, j As Integer
Dim str1 As String
i = 0
j = 0
With CommDial1
.CancelError = True
.ShowSave
str1 = .FileName
End With
Dim createExcel As New Excel.Application
Dim Wbook As Excel.Workbook
Dim Wsheet As Excel.Worksheet
Set Wbook = createExcel.Workbooks.Add
Set Wsheet = Wbook.Worksheets.Add
For i = 0 To Recordset1.Fields.Count - 1
Wsheet.Cells(1, i + 1).Value = Recordset1.Fields(i).Name
Next i
If (Recordset1.RecordCount > 0) Then
Recordset1.MoveFirst
For i = 0 To Recordset1.RecordCount - 1
For j = 0 To Recordset1.Fields.Count - 1
Wsheet.Cells(i + 2, j + 1).Value = Recordset1(j).Value
Next j
Recordset1.MoveNext
Next i
End If
Wbook.SaveAs str1
Wbook.Close True
Set createExcel = Nothing
Set Wbook = Nothing
Set Wsheet = Nothing
Set Wbook = createExcel.Workbooks.Open(str1)
createExcel.Visible = True
Exit Sub
Err:
Select Case Err.Number
Case 32755
MsgBox "Press Cacel button"
Case 1004
MsgBox "OverWrite Cancel"
Wbook.Close False
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub
