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

jh1234

New Member
Joined
Apr 4, 2011
Messages
19
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jh1234

New Member
Joined
Apr 4, 2011
Messages
19
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

New Member
Joined
Apr 4, 2011
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,082
Messages
5,526,749
Members
409,717
Latest member
Oscarsalone

This Week's Hot Topics

Top