locked
Displaying SQL results RRS feed

  • Question

  • This may be very simple but I am more of a SQL report writer than anything else so please humor me and keep it fairly simple.  

    I have a everything set up so that I am running a SQL report and returning specific parts of that report to the spreadsheet that I am using.  I want to just dump all the results of the report into the spreadsheet.  Below is what I have right now (minus login info of course).  Any help would be greatly appreciated.  

     

     

    Private Sub cmdGetData_Click()

        If Sheet1.Cells(1, 2) = "" Then

            MsgBox ("Enter a 'JOB #' first")

            Exit Sub

        End If

     

        Dim c As ADODB.Connection

        Dim r As ADODB.Recordset

        Dim sq As String

        Set c = New ADODB.Connection

        Set r = New ADODB.Recordset

     

        c.Open "Provider=SQLOLEDB;Server=XXXXXXX;Database=XXXX;Uid=XXXXXX;Pwd=XXXXXX;"

     

        sq = "select * from vmfg..EMPLOYEE"

        Set r = c.Execute(sq)

     

        If Not r.EOF Then

            Sheet1.Unprotect

            Sheet1.Cells(2, 2) = r!FIRST_NAME

            Sheet1.Cells(3, 2) = r!LAST_NAME

            Sheet1.Cells(4, 2) = r!DEPARTMENT_ID

            Sheet1.Cells(5, 2) = r!Active

            Sheet1.Cells(1, 5) = r!BASE_PAY_RATE

            Sheet1.Protect

        End If

     

    End Sub

     

    • Moved by Kee Poppy Thursday, September 29, 2011 4:23 AM (From:Visual Basic General)
    Tuesday, September 27, 2011 7:41 PM

Answers

  • Hi Holanes,

    You can accomplish this using the .CopyFromRecordset method, see below article on how to use it:

    http://support.microsoft.com/kb/306125

     

    Now, to adapt this in your code, it will be something like this:

    Private Sub cmdGetData_Click()
    
        If Sheet1.Cells(1, 2) = "" Then
    
            MsgBox ("Enter a 'JOB #' first")
    
            Exit Sub
    
        End If
    
        Dim c As ADODB.Connection
    
        Dim r As ADODB.Recordset
    
        Dim sq As String
    
        Set c = New ADODB.Connection
    
        Set r = New ADODB.Recordset
    
     
    
        c.Open "Provider=SQLOLEDB;Server=XXXXXXX;Database=XXXX;Uid=XXXXXX;Pwd=XXXXXX;"
    
     
    
        sq = "select * from vmfg..EMPLOYEE"
    
    With r
    	' Assign the Connection object.
    	.ActiveConnection = c
    	' Extract the required records.
    	.Open sq
    	' Copy the records into cell A1 on Sheet1.
    	Sheet1.Range("A1").CopyFromRecordset r
    	
    	' Tidy up
    	.Close
    End With
    
    c.Close
    Set r = Nothing
    Set c = Nothing
    
    End Sub
    

    Hope this helps,

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Tuesday, January 31, 2012 5:46 AM
    • Marked as answer by danishani Tuesday, February 7, 2012 7:57 PM
    Tuesday, January 31, 2012 5:46 AM

All replies

  • You will get better responses if you post your query in a forum where people specialise in Excel macros., such as

    http://social.msdn.microsoft.com/forums/en/isvvba/threads

    Please be sure to state what your problem is, in addition to providing the code you are using.

    Tuesday, September 27, 2011 9:38 PM
  • Hi Holanes,

    You can accomplish this using the .CopyFromRecordset method, see below article on how to use it:

    http://support.microsoft.com/kb/306125

     

    Now, to adapt this in your code, it will be something like this:

    Private Sub cmdGetData_Click()
    
        If Sheet1.Cells(1, 2) = "" Then
    
            MsgBox ("Enter a 'JOB #' first")
    
            Exit Sub
    
        End If
    
        Dim c As ADODB.Connection
    
        Dim r As ADODB.Recordset
    
        Dim sq As String
    
        Set c = New ADODB.Connection
    
        Set r = New ADODB.Recordset
    
     
    
        c.Open "Provider=SQLOLEDB;Server=XXXXXXX;Database=XXXX;Uid=XXXXXX;Pwd=XXXXXX;"
    
     
    
        sq = "select * from vmfg..EMPLOYEE"
    
    With r
    	' Assign the Connection object.
    	.ActiveConnection = c
    	' Extract the required records.
    	.Open sq
    	' Copy the records into cell A1 on Sheet1.
    	Sheet1.Range("A1").CopyFromRecordset r
    	
    	' Tidy up
    	.Close
    End With
    
    c.Close
    Set r = Nothing
    Set c = Nothing
    
    End Sub
    

    Hope this helps,

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Tuesday, January 31, 2012 5:46 AM
    • Marked as answer by danishani Tuesday, February 7, 2012 7:57 PM
    Tuesday, January 31, 2012 5:46 AM