Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
-
Een recordset snel naar excel exporteren
Posted on September 24th, 2008 No commentsMet visual basic kan je natuurlijk door een ganse recordset loopen en record per record naar excel gaan wegschrijven.
Gemakkelijker gaat het als je de recordset in één keer, in zijn geheel exporteert naar excel.
Dim RS As New ADODB.Recordset
CREATERECORDSETONSERVER RS, dbOCMW ' function to create a recordset on the serversideRS.Open "select * from MyQuery where jaar = " & cmbJaar.Text
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.WorksheetSet oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)'Add the field names in row 1
Dim i As Integer
Dim iNumCols As IntegeriNumCols = RS.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = RS.Fields(i - 1).Name
Next'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset RS'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End WithoApp.Visible = True
oApp.UserControl = True'Close the Database and Recordset
RS.CloseIn a module we have
Sub CREATERECORDSETONSERVER(rs)
Set rs = New ADODB.Recordset
rs.ActiveConnection = db ' is the active connection with the database
rs.CursorLocation = adUseServer
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
End Sub
[tags]visual basic, excell, microsoft office[/tags]
Leave a reply