VBA excel macro
I have this VBA macro in Excel. When a user clicks on a button on the sheet the macro returns the results to the sheet. What i would like to ask,is how can i run more than one query (that returns different results) in the same sheet,using the below code?
Sub Stats2()
Workbooks("macro.xls").Sheets("Sheet3").Select
ActiveSheet.Range("A1").Select
Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim strSQL As String
szconnect = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=*******88;Data Source=****"
''#Create the Connection and Recordset objects.
Set objConn = New ADODB.Connection
Set rsData = New ADODB.Recordset
On Error GoTo errHandler
''#Open the Connection and execute the stored procedure
objConn.Open szconnect
strSQL = "select * from CATEGORY_TYPE "
objConn.CommandTimeout = 0
Set rsData = objConn.Execute(strSQL)
For iCols = 0 To rsData.Fields.Count - 1
ActiveSheet.Range("A3").Select
ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + iCols).Value = rsData.Fields(iCols).Name
ActiveSheet.Cells.Font.Name = "Arial"
ActiveSheet.Cells.Font.Size = 8
ActiveSheet.Cells.EntireColumn.AutoFit
Next
ActiveSheet.Range(ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column), ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + rsData.Fields.Count)).Font.Bold = True
j = 2
If Not rsData.EOF Then
''#Dump the contents of the recordset onto the worksheet
On Error GoTo errHandler
Activ开发者_StackOverflow社区eSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).CopyFromRecordset rsData
If Not rsData.EOF Then
MsgBox "Data set too large for a worksheet!"
End If
rsData.Close
End If
Unload frmSQLQueryADO
Exit Sub
errHandler:
MsgBox Err.Description, vbCritical, "Error No: " & Err.Number
''#Unload frmSQLQueryADO
End Sub
You should call exactly this procedure from a superior one, pass the query and the starting cell as parameters and you can run it several times filling the sheet like you want.
little example:
sub stats2()
dim lastrow as string
lastrow = "A1"
lastrow = Query1(lastrow,"select * from foo")
lastrow = Query1(lastrow,"select * from other")
end sub
sub query1(startingrow as string, sqlquery as string) as string
'your code here. Take in mind that you can have a connection opened outside of here
'The rest could be the same
'just use the two parameters, one for the query, the other for the range you
'start filling the columns name.
[code here]
'return the las used row.
end sub
IF you can set the queries up in a stored procedure on the SQL server the queries will be executed an seperated result sets returned, something like:
Dim rsData As ADODB.Recordset
Dim oCmd As ADODB.Command
With oCmd
.ActiveConnection = objConn
.CommandType = adCmdStoredProc
.CommandText = "SprocName"
End With
Set rsData = oCmd.Execute()
'do stuff with the first results
rsData.NextRecordset
'rsData will now be the next set of results
rsData.NextRecordset
'rsData will now be the third set of results
Alternatively you may be able to specify multiple queries with
.CommandType = adCmdText
.CommandText = "Select * FROM Foo; Select * FROM Bar;"
having the ; between each query, I'm not sure about this, I usually set up a sproc
精彩评论