Hi All, I have trouble with changing commandtext with VBA. I am using Excel2007 - one of my excel files have connection to SQL database. I've created query table using excel connection to SQL. SQL Statement is very long - it has length above 50000 signs. I want have an option to replace some code in WHERE clause of this statement. So I've tried that: With ActiveWorkbook.Connections(1).OLEDBConnection .BackgroundQuery = False .CommandText = Replace(.CommandText, "WHERE test = 1", "WHERE test = 2") .BackgroundQuery = True End With Unfortunately, when macro is trying change commandtext, Excel shows me Run-time error 1004 and doesn't changing SQL Statement. Could you help me with this?

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Change Connection.OLEDBConnection.CommandText

Thread starter michlimes
Start date May 12, 2011

michlimes

Board Regular

May 12, 2011

Hi All,

I have trouble with changing commandtext with VBA.

I am using Excel2007 - one of my excel files have connection to SQL database. I've created query table using excel connection to SQL.

SQL Statement is very long - it has length above 50000 signs. I want have an option to replace some code in WHERE clause of this statement.

So I've tried that:

Code:

With ActiveWorkbook.Connections(1).OLEDBConnection
  .BackgroundQuery = False
  .CommandText = Replace(.CommandText, "WHERE test = 1", "WHERE test = 2")
  .BackgroundQuery = True
End With

Unfortunately, when macro is trying change commandtext, Excel shows me Run-time error 1004 and doesn't changing SQL Statement.

Could you help me with this?