Sleeper: Excel SQL Query

[jo15765]

In Excel 2007 if I go to data, from other sources, and choose sql server, I can follow the prompts and run a sql query directly within my Excel workbook. Well that works great until the syntax needs to be changed, and when I add a field to my query from within Excel it pushes it to the end of the page, and if I try to cut it and paste it in the right location it tells me it can not merge cells, blah blah blah. So the only solution I have been able to find to "change" the sql query once it has been saved is to delete the query, then re-import it.

Am I missing the obvious here on this step?

[Bob Phillips]

What are you querying, SQL Server, Access, or an Excel workbook?

[jo15765]

SQL table from sql server I am querying

[Bob Phillips]

Is the query a SQL Server stored procedure?

I would use ADO and use the command object to execute the stored procedure, return it to a recordset, and drop that into a worksheet.

[jo15765]

No. It is running the SQLstatement directly from Excel.

These are the steps I follow to connect to SQL Server and save the query within the workbook
Data
From Other Sources
SQL Server
---Input Server Name
---Choose Table
---Click Finish
---Click Yes
---Click Properties
Name Connection
Click Definition Tab
Change command type to SQL
Change command text to your sql query

[Blade Hunter]

uncomment depending on your system, change the Provider if needed:

Sub Data_Via_ADO()
    Dim cnDB As New ADODB.Connection
    Dim RS As New ADODB.Recordset, sqlText As String
    Dim Cmd As New ADODB.Command
    Columns("A:XFD").ClearContents
    sqlText = "Select something from somewhere where something = something_else"
    
    'SQL Server use PROVIDER = SQLOLEDB for MS SQL and PROVIDER = MSDAORA for Oracle
    'Server = "YourServerName"
    
    'Non-trusted
    'DatabaseUserName = "Username"
    'DatabasePassword = "Password"
    'cnDB.Open "PROVIDER=SQLOLEDB;DATA SOURCE=" & Server & ";USER ID=" & DatabaseUserName & ";PASSWORD=" & DatabasePassword
    
    'Trusted
'    cnDB.Open "PROVIDER=SQLOLEDB;DATA SOURCE=" & Server & ";Trusted_connection=yes"
    
    'ODBC
'    cnDB.Open "ODBCName"


    Cmd.ActiveConnection = cnDB
    Cmd.CommandType = adCmdText
    Cmd.CommandText = sqlText
    RS.Open sqlText, cnDB
    Set RS = Cmd.Execute
    For X = 0 To RS.Fields.Count - 1
        Cells(1, X + 1) = RS.Fields(X).Name
    Next
    Range("A2").CopyFromRecordset RS
    RS.Close
    Set RS = Nothing
    cnDB.Close
    Set cnDB = Nothing
End Sub

Nadgers I almost forgot. Make sure you add the reference to Microsoft ActiveX Data Objects X.X Library where X.X is the highest version in your list of references (tools / references in the VBE)

[Aflatoon]

Have you tried right-clicking the resulting table, choosing Table - External data properties, then unchecking the 'Preserve column sort/filter/layout' option?