Dynamically adding columns to MS Query in Excel

[cowinsm]

I am trying to pull data from a database. I have a really large table on SQL Server that I query using MS Query. I would like to pull from columns based on a list from excel that changes based on another field.

Here is an example. The table in SQL Server contains columns A through Z. I would have an input range of the columns I would pull down from SQL Server and only these columns.

For example cells A1:A4 in Excel would have the following (the range for the pull would be A2:A10. A5:A10 would be blank for this example):

Columns
Column_B
Column_D
Column_G

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The query would only pull these 3 columns. However, if I change the list and refresh, it would pull the new list of columns.

<o></o>
I currently pull down the entire table through MS Query but the problem is the table has about 300 columns and I really only need a third of them at any given time. I do not think I can do it dynamically through MS Query but I could be wrong.

<o></o>
I know about using parameters in criteria to limit the observations pulled dynamically using “[]”. However, I am not sure how to dynamically pull columns.

<o></o>
Please let me know if you have any ideas. I would be happy to use VBA, just not sure how to do it.

<o></o>
Thank you in advance for your help.

Scott

 

[ChrisM]

You need VBA for this. Your MS Query has a SQL string behind it, accessible via VBA, like this:

Worksheets("Sheet1").QueryTables(1).CommandText = "Select * From Table"

You need to loop through your sheet range, and parse all the desired columns into a text string, then put that text string together with the rest of your sql statement.

Do you have any VBA experience?

 

[cowinsm]

Hey Chris,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Thank you for such a quick response. I do have some VBA experience. I would classify myself as an intermediate VBA user. However, I have not ever used VBA to query tables.

<o></o>
I actually recorded a macro so I have the code to connect to the server and the select statement. I named my list so I will have a name range to call in the VBA. I will call it “ColumnList”. Here is the macro I am working on:
<o></o>

Sub Macro1()
ColList = Range("ColumnList")

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=DC-SQL-SRVR;UID=XXXXX;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=DCA-PC17" _

, Destination:=Range("$ZV$4")).QueryTable
.CommandText = Array( _
"SELECT TABLE1.COLUMNA, TABLE1.COLUMNB" & Chr(13) & "" & Chr(10) & "FROM database.db_owner.TABLE1 TABLE1 " & Chr(13) & "" & Chr(10) )

.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_SQL_13"
.Refresh BackgroundQuery:=False
End With

End Sub
<o></o>

However, I am not sure how to call “ColList” in the above select statement?

I think there is a problem with the quotes when I call the function.
<o></o>

Thank you again for your help.

<o></o>
Scott

 

[ChrisM]

You need a whole seperate bit of code to prep your column list first.

Dim rCell as Range
dim strSql as String

For Each rCell in Range("ColumnList")
    If rCell <> "" Then
           strSql = StrSql & rCell & ","
    End If    
Next rCell

strSql = left(StrSql,Len(StrSql)-1)

Now you have your comma delimited list of columns in variable strSQL, now throw that into your command SQL line.

 

[cowinsm]

Hey Chris,

Thank you so much!! That worked. I appreciate your help.

Scott

 

[cowinsm]

Hey Chris,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks again for your help earlier. I have another question about the same code.
<o></o>
I ran into a little bit of a problem pulling in my columns because the column names were numbers (for example “001”) instead of characters, but I manage to figure out a work around. It was just figuring out the right amount of quotes to add.
<o></o>
I managed to get the code working for a small sample of columns. I thought I had it all worked out until I expanded to a larger list of columns. For some unforeseen reason my code works for 19 columns (date column + 18 macro called columns) without issue. However, when I add the 20<SUP>th</SUP> column in my macro I get an error:
<o></o>
“Run-time error ‘13’:
Type mismatch”
<o></o>
It crashes at the select statement (“.CommandText = Array( “Select….). I have no idea why the code would work for 1 to 19 columns but then crash if the 20<SUP>th</SUP> column is added. I tried a couple things:
<o></o>
· I thought I might have to many characters in my string so I tried to split the macro variable in two, but got the same error after the 19<SUP>th</SUP> column
· I hard coded the 20 columns and surprisingly, it worked (It is quoted out in my code).
· I hard coded the extra column both before and after the macro (strSql). It worked when I had 19 columns but failed when I had 20 columns on both tries.
<o></o>
I am not sure what is going on. Please let me know if you have any ideas.
<o></o>
Scott
<o></o>
<o></o>
<o></o>
<o></o>
Sub Macro1()
'
' Macro1 Macro
'
Dim rCell As Range
Dim strSql As String
<o></o>
For Each rCell In Range("ColumnList")
If rCell <> "" Then
strSql = strSql & "" & """" & rCell & "" & """" & ", "
' Range("fill") = strSql
End If
Next rCell
<o></o>
strSql = "" & " " & Right(Left(strSql, Len(strSql) - 2), Len(strSql) - 2) & " " & ""
Range("fill") = strSql

With Range("try_table").ListObject.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=DC-SQL-SRVR;UID=scowin;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=DCA-PC17"

' .CommandText = Array( _
' "SELECT date, " & " ""001"", ""006"", ""007"", ""012"", ""014"", ""020"", ""030"", ""032"", ""041"", ""042"", ""043"", ""043"", ""043"", ""043"", ""043"", ""043"", ""043"", ""043"", ""043"" " & Chr(13) & "" & Chr(10) & _
' "FROM database.table1 table 1 " _
' & Chr(13) & "" & Chr(10) & "ORDER BY table1.date" _
' )
<o></o>
<o></o>
.CommandText = Array( _
"SELECT date, " & strSql _
& Chr(13) & "" & Chr(10) & _
"FROM database.table1 table1 " _
& Chr(13) & "" & Chr(10) & "ORDER BY table1.date" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Try_table"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[ChrisM]

I'm not sure why you are using Array() for your command text. It's just a string. Get rid of the array and all the double quotes and just assign a string.

.commandtext = "select * from table"

see if that works.

 

[cowinsm]

Perfect! You nailed it. I was only using an array because that is how it recorded the macro when I pulled the data.

Thank you again for your help.