PDA

View Full Version : Sleeper: Excel SQL Query



jo15765
12-17-2014, 02:32 PM
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
12-17-2014, 03:47 PM
What are you querying, SQL Server, Access, or an Excel workbook?

jo15765
12-17-2014, 04:08 PM
SQL table from sql server I am querying

Bob Phillips
12-17-2014, 04:22 PM
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
12-17-2014, 04:50 PM
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
12-17-2014, 07:14 PM
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
12-18-2014, 02:14 AM
Have you tried right-clicking the resulting table, choosing Table - External data properties, then unchecking the 'Preserve column sort/filter/layout' option?