开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜