Excel 2007 - Data Connections - VBA to Loop through sheets amending SQL

[jh1234]

Hello,

I regularly find myself manually changing the SQL in many different worksheets owing to a minor change in the criteria. For each sheet, the SQL is the same other than for the filtered field, Specialty. So: one sheet for Cardiology, one for Ophthalmology etc.

I think this must be possible using VBA but I'm not good with VBA so I need some help please.

What I want is some code that will:

1. Loop through all of my worksheets where the worksheet name contains "filter"
2. I need it to updating the SQL to the standard SQL I have (select * from A where specialty = '<SPECIALTY>')...
3. ...where the <SPECIALTY> is fed by the first six letters of the worksheet name.

I can't figure out how to get at connections without specifying their names. There is only one data connection per worksheet and each is connected to a SQL Server 2008 database via Microsoft Query.

In a couple of years of using VBA pilfered from forums such as this, this is the first time I've had to post! Thanks in advance for your help and apologies if I've missed anything I should have included in this post.

John

 

[jh1234]

Couldn't find an 'edit' button.

SQL script should read: select * from A where specialty like 'SPECIALTY%'
(where SPECIALTY is a left 6 on the worksheet name)

Apologies for any confusion.<SPECIALTY>

 

[jh1234]

Bump

 

[jh1234]

Solved as below with the help of an old colleage.

Sub ChangeSQL()
Const SName As String = "PartialTabName"
Const SQLNew As String = "select * from a where 1=1 and texttochange like REPLACETHIS%'"

Dim Ws As Worksheet, lo As ListObject

For Each Ws In ThisWorkbook.Worksheets
If InStr(1, Ws.Name, SName) > 0 Then
Ws.Select
Set lo = Nothing
On Error Resume Next
Set lo = Ws.ListObjects(1)
On Error GoTo 0
If Not lo Is Nothing Then
lo.QueryTable.CommandText = Replace(SQLNew, "REPLACETHIS", Left$(Ws.Name, 6))
lo.QueryTable.Refresh
End If
End If
Next

End Sub