PDA

View Full Version : [SOLVED] Change SQL Query Syntax



jo15765
03-13-2015, 12:46 PM
I have a workbook (.xlsx format) that has about 50 worksheets in it. Each with their own connection to a different SQL Server Database and a different SQL String being executed. Rather than me having to manually modify 50 Connections from the Data---Connections Tab is their a way in VBA to in one go - round change the where clause for ALL connections listed?

jo15765
03-15-2015, 01:22 PM
I found this syntax, but I get a subscript out of range error?


Function ChangeWhereClause()
Sheet1.QueryTables(1).CommandText = Replace(Sheet1.QueryTables(1).CommandText, _
"WHERE City = 'Norfolk'", _
"WHERE City = "Highland'")
End Function

Aflatoon
03-16-2015, 03:49 AM
In 2010 your querytables will be present as Tables so you have to access them through the parent Listobject:


Function ChangeWhereClause()Sheet1.Listobjects(1).QueryTable.CommandText = Replace(Sheet1.Listobjects(1).QueryTable.CommandText, _
"WHERE City = 'Norfolk'", _
"WHERE City = 'Highland'")
End Function