Different SQL commands for different Excel sheets

Question

With what VBA macro commands can I switch between sheets, in order to execute specific Workbook Connecton SQL commands?

What I have tried is, to select a sheet, write the sheet specific sql command store that command in the specific connection's SQL command and then refresh the workbook to get the result. The code executes only on the sheet that it originally was executed on, not the sheet I selected.

Sheets("Alex").Select

' The SQL command text
Dim comText As String
comText = ... // the SQL command text

' Changes the workbook's SQL command text to the text in the code above
With ActiveWorkbook.Connections("conCRM").ODBCConnection // conCRM - the name of the conection
    .commandText = comText
End With

' Refreshes data from the database
ActiveWorkbook.Connections("conCRM").Refresh

The thing that, in my opinion, is wrong is that the last line in the code targets the whole workbook and not the specific sheet I need.

Are you trying to store a SQL command on each page, execute that SQL against an external database, and get the results on a different sheet? — transistor1
– transistor1, Commented Mar 26, 2012 at 18:10
Yes, that is my goal. However, this seems very complex. Is there a better solution? — ositra
– ositra, Commented Mar 27, 2012 at 7:21

transistor1 · Accepted Answer · 2012-03-26 19:02:14Z

1

See if this is close to what you are trying to do. It takes 3 different SQL statements, adds a sheet for each one, executes it and puts each result on a new sheet.

Sub addSQLResultsOnto3Sheets()
    Dim oConn As WorkbookConnection
    Dim oQt As QueryTable
    Dim oSh As Worksheet
    Dim i, SQL

    'Get the existing connection's connection string
    Set oConn = ActiveWorkbook.Connections("conCRM")

    'Three different SQL statements, to be output onto 3 different worksheets.
    SQL = Array("SELECT TOP 1 * FROM MyTable", "SELECT TOP 2 * FROM MyTable", "SELECT TOP 3 * FROM MyTable")

    For i = 0 To 2
        'Add a new sheet "Results N"
        Set oSh = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        oSh.Name = "Results " & (i + 1)

        'Add a new sheet with SQL
        Set oQt = oSh.ListObjects.Add(xlSrcExternal, oConn.ODBCConnection.Connection, Destination:=oSh.Range("A1")).QueryTable
        oQt.CommandText = SQL(i)
        oQt.Refresh
    Next

End Sub

edited Mar 26, 2012 at 19:02

answered Mar 26, 2012 at 18:03

transistor1

2,93528 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ositra Over a year ago

I will have to modify this, but it seems that it will do the trick! Thank you very much, transistor1! :)

transistor1 Over a year ago

any time! i'm glad to help when i can! :-)

Collectives™ on Stack Overflow

Different SQL commands for different Excel sheets

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related