• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Export Recordset to EXCEL file

Export Recordset to EXCEL file

Export Recordset to EXCEL file

Rate Export Recordset to EXCEL file

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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