Prev: Creating New Workbook from Sheet
Next: Autofilter Array
From: Evergreen435 on 18 Jan 2010 17:40
Hi,

I have searched the web extensively for a solution here but cannot find one.
I am using the following code to refresh data from an external data source in
excel.

With Selection.QueryTable.Connection = Array(Array( _
"ODBC;Description=Database;DRIVER=SQL
Server;SERVER=SERVER1\SQLEXPRESS;UID=username;APP=Microsoft Office
2003;WSID=HQD002;Netw" _
), Array("ork=DBNMPNTW;Trusted_Connection=Yes"))
.CommandType = xlCmdSql
.Sql = "SELECT * FROM vwDetailsReporting"
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

If I have deleted a couple of columns from the QueryTable and moved a few
columns around, then the above code updates the only columns in the Query
table that are still shown, which is as expected.

If I want to retreive some of the deleted columns then I can set the
..PreserveColumnInfo to False. This also works, and gives me all the columns
in the original query, but in the original order (overwriting the current
order of columns).

What I would like to do is be able recover the columns that I deleted
without reordering the existing columns. That is, just add the missing
columns to the end of the Querytable.

I can do this if I goto MS Query and then select File-Return Data to
Microsoft Office Excel. It gives me all the columns that I have previously
deleted at the end, but did not move any of the existing columns (ie.
preserved the existing column info). I would like to do this using code. Is
this possible?

Thanks

 | 
Pages: 1
Prev: Creating New Workbook from Sheet
Next: Autofilter Array