Discussion Locked

Create worksheets based on SQL results

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How do I dynamically create a worksheet for each record in a recordset?

9 replies

by kevin.riggs

The following code will work (at least it worked for me). You will have to have a reference to MS ActiveX Data Library (I used 2.5) and Microsoft Excel Object (I used 9.0 which is Office 2000).

------------CODE SNIPPET-------------

DimoXL As Excel.Application
Set oXL = New Excel.Application

Dim rs As New ADODB.Recordset

With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Fields.Append "Name", adVarChar, 100 .Fields.Append "Field2", adVarChar, 100

.Open

.AddNew
!Name = "Kevin"
!Field2 = "test"

.AddNew
!Name = "Cindee"
!Field2 = "test"
.UpdateBatch
End With

rs.MoveFirst

oXL.Visible = True

oXL.Workbooks.Add

Dim iLastWorksheet As Integer
Dim wsActive As Excel.Worksheet

iLastWorksheet = 3

WhileNot rs.EOF

oXL.Workbooks(1).Worksheets.Add After:=oXL.Workbooks(1).Worksheets(iLastWorksheet)
iLastWorksheet = iLastWorksheet + 1
Set wsActive = oXL.Workbooks(1).Worksheets(iLastWorksheet)
wsActive.Range("A1").Select
oXL.ActiveCell.Value = rs("Name")
wsActive.Range("B1").Select
oXL.ActiveCell.Value = rs("field2")

rs.MoveNext
Wend

oXL.DisplayAlerts = False

Dim iOldWorksheet As Integer

For iOldWorksheet = 1 To 3
oXL.Workbooks(1).Worksheets(1).Delete
Next

oXL.DisplayAlerts = True

Set oXL = Nothing

----------------End Snippet---------------

by kevin.riggs

In my answer above, I assume you know this but you would remove the lines like !Name=Kevin because you would already have the recordset layout and data. You would not need to construct a new recordset as you should already be working with a recordset (at least that's what I inferred from your question about converting a recordset to an Excel spreadsheet). The last section deletes the 3 initial sheets from the workbook.

by limbo

I'll send you I VB Project that demonstrates this with an access database.

All you should have to do is change the database environment settings, to match your own.

by limbo

I'll send you a VB Project that demonstrates this with an access database.

All you should have to do is change the database environment settings, to match your own.

This discussion is closed to new replies.