Create an Excel file in vb and save the data to Excel

Create an Excel file and save the data to an Excel file
Private Sub ComExport_Click ()
Dim xlApp As New Excel. Application
Dim xlBook As New Excel. Workbook 'define an Excel Workbook object
Dim xlSheet As New Excel. Worksheet 'define the Worksheet object in Excel

Dim line As Integer, M As Integer, n As Integer

Dim savepath As String 'define the stored path

CommonDialog1.CancelError = True 'sets cancelError to True.

On Error GoTo errhandler
CommonDialog1.Flags = cdlOFNHideReadOnly


CommonDialog1.FileName = "Report"

CommonDialog1.DefaultExt = ". xls"

CommonDialog1.Filter = "Excel (*. xls) | *. xls | Text (*. txt) | *. txt"

CommonDialog1.FilterIndex = 1

CommonDialog1.Flags = & H2

CommonDialog1.ShowSave

If ERR. Number = cdlCancel Then

Exit Sub

End If

Savepath = CommonDialog1.FileName

''' ####################### The following section describes how to import a sheet to an excel file.

Set xlApp = CreateObject ("Excel. Application ")
'Xlapp. Visible = true' can be set to True or FALSE based on whether the operator needs to see Excel.
XlApp. Visible = False

Set xlBook = xlApp. Workbooks. add
On Error Resume Next
Set xlSheet = xlBook. Worksheets (1)
If k = 2 then' by host Then
Str_eqid = ""
N = 0
M = 1 '. The str_eqid is used with the excel file.
For M = 0 To ListSbbh. ListCount-1
If ListSbbh. Selected (M) = True Then
Str_eqid = str_eqid & Trim (ListSbbh. List (M ))
If n <ListSbbh. SelCount Then
Str_eqid = str_eqid
End If
N = n + 1
End If
Next M
XlSheet. Cells (1, 4) = "EQ Down Top10 Report"
XlSheet. Cells (2, 1) = "Date :"
XlSheet. Cells (2, 2) = Format (DTPickerStart. Value, "yyyy-mm-dd") & "07:30:00"
XlSheet. Cells (2, 3) = ""
XlSheet. Cells (2, 4) = Format (DTPickerEnd. Value + 1, "yyyy-mm-dd") & "07:30:00"
XlSheet. Cells (3, 1) = "Eqid :"
XlSheet. Cells (3, 2) = str_eqid

XlSheet. Cells (4, 1) = "Bug Poenomenon"
XlSheet. Cells (5, 1) = "Quantity"

Rsgzxx. MoveFirst

Line = 4
Do While Not rsgzxx. EOF
XlSheet. Cells (4, line). Value = rsgzxx ("poenomenon"). Value
XlSheet. Cells (5, line). Value = rsgzxx ("quantity"). Value

Line = line + 1
Rsgzxx. MoveNext
Loop
End If

XlBook. SaveAs FileName: = savepath, FileFormat: = xlNormal ,_
PassWord: = "", WriteResPassword: = "", ReadOnlyRecommended: = False ,_
CreateBackup: = False
XlBook. Saved = true' save to Excel
MsgBox "saved successfully! ", VbOKOnly," information"
'End the EXcel Process
XlApp. Quit'
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Errhandler:

Exit Sub

End Sub