-------------------------------------------------- ------------------------------
Excel is a very excellent report production software that controls its generation of excellent reports with VBA. This article uses the method of adding query statements, that is, the function of obtaining external data in Excel will quickly capture data from a query statement. In Excel, the method of writing data to each Cell is raised many times. Quote Microsoft Excel 9.0 Object Library in the program, add below to a module, and the form is called the following ExportoExcel ("SELECT * from Table"). It is quickly exported to Excel.
PUBLIC FUNCTION EXPORTOEXCEL (Stropen As String) '****************************************** **************** * Name: Exportoexcel '* Function: Export Data to Excel' * Usage: ExportoExcel (SQL Query Strings) '******** ****************************************************** DIM RS_DATA As New ADODB.RecordsetDim Irowcount As IntegerDim 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 'Number of records Irowcount = .RecordCount' Field Total Icolcount = .fields.count end with set xlapp = createObject ("excel.application") SE t xlBook = Nothing Set xlSheet = Nothing Set xlBook = xlApp.Workbooks (). Add Set xlSheet = xlBook.Worksheets ( "sheet1") xlApp.Visible = True 'Add query, data import EXCEL 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 'name display field xlQuery.Refresh With xlSheet .Range (.Cells (1, 1), .Cells (1, Icolcount)). Font .Name = "black body" 'set the title as a black body. Range (.cells (1, 1), .cells (1, Icolcount)). Font.bold = true' title font bold .range (.cells (1, 1), .cells (irowcount 1, icolcount)). Borders.LineStyle = Xlcontinuous' Sets Form Border Pattern End with Xlsheet.pagesetup .leftheader = "& Chr (10) &" & "体 _GB2312, General "& 10 Company Name:" '& Gsmc .Centerheader = "&" "体 _GB2312, convention" "Company Personnel Situation" "" Song, General "" & Chr (10) & "&" "体 _GB2312 , General "" & 10 Date: ".rightheader =" & chr (10) & "&" "_gb2312, general" "& 10 units:" .leftfooter = "&" "_gb2312, general" & 10 system Phenomenon: ".Centerfooter =" & "" 体 _GB2312, regular "" & 10 Table Date: ".rightfooter =" "体 _gb2312, general" "& 10" End with XLAPP. Application.visible = True Set XLAPP = Nothing '"Turning Control to Excel Set XLBOOK = Nothing set xlsheet = NothingEnd Function Note: At the program references 'Microsoft Excel 9.0 Object Library' and ADO objects, machinery Excel 2000 This program runs in Windows 98/2000, VB 6.
