Here's possibly the single most useful function that I use. It simply takes an ADO record set and dumps the column names and row values into an Excel worksheet. To use it make sure you have Excel marked in your project references.
Printing an ADO recordset into Excel
'*******************************************************************************
' excelPrintRecordSet(Sub)
'
' PARAMETERS:
'
'
' RETURN VALUE:
'
'
' DESCRIPTION:
' Test function that will print out all the records from a recordset in Excel.
'*******************************************************************************
Public Sub excelPrintRecordSet(rstTmp As ADODB.Recordset)
Dim appExcel As Excel.Application
Dim wbkReport As Excel.Workbook
Dim wksReport As Excel.Worksheet
Dim intField As Integer, intRow As Integer
Const PROCEDURE_NAME As String = "excelPrintRecordSet"
On Error GoTo errorHandler
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbkReport = appExcel.Workbooks.Add
Set wksReport = wbkReport.Worksheets(1)
If rstTmp.EOF <> True Then
rstTmp.MoveFirst
intRow = 1
Do
For intField = 0 To rstTmp.Fields.Count - 1
wksReport.Cells(intRow, intField + 1) = rstTmp.Fields(intField).Name & "=" & rstTmp.Fields(intField).Value
Next intField
rstTmp.MoveNext
intRow = intRow + 1
Loop Until rstTmp.EOF = True
End If
Exit Sub
errorHandler:
frmErrorHandler.errorForm MODULE_NAME, PROCEDURE_NAME
Err.Clear
End Sub
chad0809 0 Newbie Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.