Dumping data to new worksheets - 90% there

[imodan]

Hi all. First of all I don't know VBA. I understand what it's doing but as far as the syntax is concerned for putting new stuff in, it's all greek to me I'm afraid.

Below is the code I have right now that works beautifully at:

1. Connecting to the db
2. Running some SQL
3. Dumping the sql to an excel spreadsheet.

Now all I want to do is to run about another 5 queries and output each to a new worksheet within the same workbook.

Could anyone give me a hand with what's required here?


Thankyou so much

Code below.


Private Sub cmdGetQuery_Click()
' Open Database connection
DataAccess.OpenDatabaseConnection

' Get Recordset
Dim rs As ADODB.Recordset
Set rs = DataAccess.GetResultSet("SELECT * FROM wrap_tst2.dbo.amd_fee_payee_and_freq")

' Populate Sheet with the data
Dim dbSheet As Worksheet
Set dbSheet = Sheets("db")
Call CopyRecordSetToSheet(rs, dbSheet.Range("A5").Address)


End Sub

Sub CopyRecordSetToSheet(ByVal dataRs As Recordset, ByVal startCellAddress As String)
Dim lp As Integer
For lp = 0 To rowsToCopy - 1
Rows(templateRowAddress & ":" & templateRowAddress).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next

Dim fldCount As Integer
fldCount = dataRs.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(4, iCol).Value = dataRs.Fields(iCol - 1).Name
Next

' Check version of Excel
If 1 = 1 Then
'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
ActiveSheet.Range(startCellAddress).Select
' Copy the recordset to the worksheet, starting in cell A2
Selection.CopyFromRecordset dataRs
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

End If


End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X

TransposeDim = tempArray

End Function