General discussion

Locked

Create worksheets based on SQL results

By jbeales ·
How do I dynamically create a worksheet for each record in a recordset?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Create worksheets based on SQL results

by kevin.riggs In reply to Create worksheets based o ...

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---------------

Collapse -

Create worksheets based on SQL results

by jbeales In reply to Create worksheets based o ...

Poster rated this answer

Collapse -

Create worksheets based on SQL results

by kevin.riggs In reply to Create worksheets based o ...

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.

Collapse -

Create worksheets based on SQL results

by jbeales In reply to Create worksheets based o ...

Poster rated this answer

Collapse -

Create worksheets based on SQL results

by Limbo In reply to Create worksheets based o ...

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.

Collapse -

Create worksheets based on SQL results

by jbeales In reply to Create worksheets based o ...

Poster rated this answer

Collapse -

Create worksheets based on SQL results

by Limbo In reply to Create worksheets based o ...

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.

Collapse -

Create worksheets based on SQL results

by jbeales In reply to Create worksheets based o ...

Poster rated this answer

Collapse -

Create worksheets based on SQL results

by jbeales In reply to Create worksheets based o ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums