ODBC connection / excel gurus needed

[Freefaller]

Hi all,

I have an ODBC connection to an SQL database on excel. All the data is there, and updates nicely without issue.

Sheet1 = ODBC data
Sheet2 = Area to extract relevant date from Sheet1

In sheet 2, I want to extract only certainly columns and not the whole table as I want to be able to present the data a certain way rather than have the whole data listed (over 40 columns of data).

Now I guess I could do it with a Pivot Table which would be the simplest way (however, that limits my ability to tweak things a little (like apply certain formulas to represent the data certain way) – but I have to have more of a play). The advantage is that the data is refreshed automatically.

The current way I’m doing is doing a VLOOKUP – but the issue with that is I have to copy and paste the data from the column in database in sheet1 to have something to extract from the vlookup function – and whilst I could create a macro for it, I still have to manually add the entries to the bottom of the column. i.e. if the list is currently 1-140. Tomorrow the list may be 1-145. I could just fill the rest of the cells down to the bottom with prepopulated fields, and let the vlookup just give an #NA for the data that isn’t there, which can be mitigated with an IF(ISERROR) function. However this feels clunky and clumsy.

I guess what I’m asking, is there a way I can just extract a specific column of data from the ODBC connection, whilst keeping sheet2 dynamic (i.e. automatically updating as the data is updated). Or is a pivot chart the one and only way of doing it?

I realise this sounds garbled, but thought I'd ask anyway!

 

[daver]

You can edit the connection and write an sql query to return the columns you want from the table. Go to Data>Connections select the connection and go to properties then on the definition tab you can change the command text to what you want. Eg at the moment it probably just returns the whole table. You can replace that with SELECT...FROM...WHERE etc .

 

[Freefaller]

Great I'll give that a go, thanks for pointing me in the right direction.

 

[mattx2]

I've got a few spreadsheets that do this I can upload some example code when I get home later if you haven't worked it out by then

 

[Freefaller]

Always happy for more help and see other examples. feel free to send me an email if you'd rather not share your code publicly.

 

[tom_e]

I've never really used it in an Excel environment but the SQL query stuff is a major part of my day job so give me a shout if you get any issues in that department.

 

[dfarrall]

Exactly as above, SQL it.

 

[mattx2]

This is the basic vba macro code i used it should work for your needs


Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
"Initial Catalog=MyDatabaseName;" & _
"Integrated Security=SSPI;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute("SELECT * FROM Table1;")

' Check we have data.
If Not rs.EOF Then
' Transfer result.
Sheets(1).Range("A1").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

 

[Freefaller]

That's exactly what I wanted. Thank you.

I was trying to do it with dynamic ranged names, but you cannot create a dynamic range with the ODBC table data.