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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.