Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel SQL VBA always inserts colum 1

Status
Not open for further replies.

bjornagin

Programmer
Dec 28, 2005
9
US
Hi. I'm new to VBA so bear with me.

I'm trying to write VBA that updates a specific cell in Excel with data that I've queried from SQL Server. So far I have the following..

Sub Test1()
Dim qt As QueryTable

sqlstring = "Select ValueX From Data1 Where blah='blah'"
connstring = "My ODBC connection string"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("D7"), Sql:=sqlstring)
ActiveSheet.QueryTables(1).FieldNames = False
.Refresh
End With
End Sub

The query I'm using returns a single value. The problem I'm having is that any data in cell D7 (and all of column D) gets shoved to the right (to column E) each time the macro has run. I'd like it to just overwrite the cell instead of creating a whole new column "D".
 



Hi, once your query table has been ADDED, all you need do is Data/Refresh Data. You never need to run the ActiveSheet.QueryTables.Add code again.

Skip,

[glasses] [red][/red]
[tongue]
 
amend:
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("D7"), Sql:=sqlstring)
ActiveSheet.QueryTables(1).FieldNames = False
.Refresh

to

ActiveSheet.QueryTables(1).Refresh


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 


And, with yet another twist...

when dealing with code, I avoid the Activate & Select methods as well as ActiveSheet and ActiveCell.

I like to use the worksheet's CodeName, rather than the worksheet's Name property since the user can change the Name property at will.

So if I were to have coded the sheet CodeName as wsQuery, then...
Code:
wsQuery.QueryTables(1).Refresh


Skip,

[glasses] [red][/red]
[tongue]
 
Thanks guys!

One other quesion. I want the values to be refreshed when the user opens the spreadsheet. I've seen mention on the net about putting code in the workbook_open event to accomplish this. Would I just put a "ActiveSheet(or whatever my sheet name is).QueryTables(1).Refresh" in that event?
 



Yes, but then it is even MORE imperitive that you EXPLICITLY reference the Sheet containing the QueryTable Object.

Skip,

[glasses] [red][/red]
[tongue]
 
Got it. So I would put Sheet1.QueryTable(1).Refresh in that event.

Silly question. If I want a few queries to update different cells, would I add them within the same vba module? And do the subsequent queries become QueryTable(2).. (3) etc.?
 


When you ADD QueryTables to a Sheet, the INDEX numbers are NOT as you expect.

I would EXPLICITLY name each QT in Data/Data Range Parameters. Then use that name in the code...
Code:
With Sheet1
  .QueryTable("MyFirstQT").Refresh 
  .QueryTable("MySecondQT").Refresh 
  .QueryTable("MyThirdQT").Refresh 
End With
but I'd make the names significant.


Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top