Macro to show SQL result based on users cell choice
Hi all i have the following code: Now what this code does is it gets the results from an SQL query and inserts them in a predefined cell which i specify in the code...what i want it to do,.,,is to insert the result of the query in a cell that the user chooses when he clicks on a button which i assign the macro below. So the flow would be
- User clicks on the button
- User is asked to select a cell Macro displays results in cell selected by user..
how do i do that?
Sub Stats1()
Workbooks("2006_2007_2008.xls").Sheets("Sheet1").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=*****;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 sz开发者_如何转开发connect
strSQL = "select name from user"
objConn.CommandTimeout =
Set rsData = objConn.Execute(strSQL)
For iCols = 0 To rsData.Fields.Count - 1
ActiveSheet.Range().Select
ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + iCols).Value = rsData.Fields (iCols).Name
ActiveSheet.Cells.Font.Name = "Arial"
ActiveSheet.Cells.Font.Size = 8
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
ActiveSheet.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
What is it you don't know how to do?
The vba help tells you how to add your macro onto a button.
To get the user to enter a range the simplest is like this
dim my_cell as string
my_cell = InputBox("Which cell?")
Or you could make a user form and put a RefEdit control onto the form.
This is one way
Dim my_cell As Range
Set my_cell = Application.InputBox(prompt:= _
"Click in a cell to select a destination range", Type:=8)
my_cell.Select
精彩评论