The method of Visual Basic export to Excel

Excel|visual Excel is a very good report making software, with VBA can control its generation of excellent reports, this article by adding a query statement, that is, the ability to get external data in Excel to quickly capture data from a query statement to Excel, It's a lot more than a way to write data in each cell.

Add the following to a module, and the screen calls the following Exportoexcel ("Select * FROM table") to export it to excel

Public Function Exportoexcel (Stropen as String)
'*********************************************************
' * Name: Exportoexcel
' * Function: Export data to Excel
' * Usage: exportoexcel (SQL query string)
'*********************************************************
Dim Rs_data as New ADODB. Recordset
Dim Irowcount as Integer
Dim Icolcount as Integer

Dim xlapp as New excel.application
Dim Xlbook as Excel.Workbook
Dim Xlsheet as Excel.Worksheet
Dim XLQuery as Excel.querytable

With Rs_data
If. State = adStateOpen Then
. Close
End If
. ActiveConnection = Cn
. CursorLocation = adUseClient
. CursorType = adOpenStatic
. LockType = adLockReadOnly
. Source = Stropen
. Open
End With
With Rs_data
If. RecordCount < 1 Then
MsgBox ("no record!")
Exit Function
End If
' Total records
Irowcount =. RecordCount
' Total fields
Icolcount =. Fields.Count
End With

Set xlapp = CreateObject ("Excel.Application")
Set xlbook = Nothing
Set xlsheet = Nothing
Set xlbook = Xlapp.workbooks (). Add
Set xlsheet = xlbook.worksheets ("Sheet1")
Xlapp.visible = True

' Add query statements, import Excel data
Set xlquery = XlSheet.QueryTables.Add (Rs_data, Xlsheet.range ("A1"))

With XLQuery
. FieldNames = True
. Rownumbers = False
. FillAdjacentFormulas = False
. PreserveFormatting = True
. RefreshOnFileOpen = False
. BackgroundQuery = True
. RefreshStyle = Xlinsertdeletecells
. Savepassword = True
. SaveData = True
. Adjustcolumnwidth = True
. RefreshPeriod = 0
. Preservecolumninfo = True
End With

Xlquery.fieldnames = True ' Displays field names
Xlquery.refresh

With Xlsheet
. Range (. Cells (1, 1),. Cells (1, Icolcount)). Font.Name = "Blackbody"
' Set title to bold text
. Range (. Cells (1, 1),. Cells (1, Icolcount)). Font.Bold = True
' Title font Bold
. Range (. Cells (1, 1),. Cells (Irowcount + 1, icolcount)). Borders.LineStyle = xlcontinuous
' Set table border style
End With

With Xlsheet.pagesetup
. LeftHeader = "" & Chr (Ten) & "&" "_gb2312 in italics, General" "&AMP;10 Company Name:" ' & GSMC
. CenterHeader = "&" "italics _gb2312, General" "Company Personnel Table &" "Song Body, General" "" & Chr (Ten) & "&" "italics _gb2312, General" "&10 Date:"
. RightHeader = "" & Chr (Ten) & "&" "_gb2312 in italics, general" "&10 Unit:"
. LeftFooter = "&" "italics _gb2312, General" "&10 Watchmaker:"
. CenterFooter = "&" "italics _gb2312, General" "&10 Tabulation Date:"
. RightFooter = "&" "italics _gb2312, General" "&10 &p Page Total &n page"
End With

XlApp.Application.Visible = True
Set xlapp = Nothing ' "Return control to Excel
Set xlbook = Nothing
Set xlsheet = Nothing

End Function


Note: The ' Microsoft Excel 9.0 Object Library ' and ADO object must be referenced in the program, and the machine must be installed in Excel 2000

This program runs through Windows 98/2000,VB 6.