If ConnectionString = "" Then ConnectionString =. ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC ", "OLEDB ", 1, 1, vbTextCompare)ĮlseIf. Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "") this just works for me.Ĭreate a new module and insert the following code: Option Explicit
Excel query table vba update#
If you temporarily switch to an OLEDB connection, update your CommandText property and then switch back to ODBC it does not create the new connection. However you have to be aware that there is a bug when updating the CommandText property of an ODBC connection. Instead of adding another query table with the add method, you can simply update the CommandText Property of the connection. The basics are the same, you just may need to learn about the ListObject object if you're using 2007 or later. I know that doesn't answer your question directly, but I think determining whether you really need to add the QueryTable each time is the first step.įor more on Parameters, see It's for 2003, so there are few inconsistencies with later versions. If it's a different database, you'll need a new connection, but that's pretty rare. You can select different columns or even different tables by changing CommandText. Sheet1.QueryTables(1).CommandText = "Select * FROM.
Excel query table vba code#
Another option for changing the SQL is changing it in code for the existing QueryTable. You could set up Parameters that prompt for a value or get it from a cell. If you need to change the underlying SQL statement, you have some options. That is, you create your QueryTable once (through code or the UI) and the you Refresh the QueryTable to get updated data. QueryTables are more typically design-time objects. There are reasons to do it, but it usually isn't necessary. You might ask yourself why you're creating a QueryTable every time in your code.
0 kommentar(er)
