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.
Add below to a module, call the following ExportoExcel ("Select * from Table") to export it into 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: The 'Microsoft Excel 9.0 Object Library' and ADO objects must be referenced in the program, and the machine must install EXCEL 2000
This program runs in Windows 98/2000, VB 6.
