lkpmystic.blogg.se

The reason we need to do this is because the formula string contains some extra information about our database connection and not just a sql query itself. Set oledb = ThisWorkbook.Connections("Query - Query1").OLEDBConnectionįirst we get the query's formula using: Debug.Print ("Query1").Formula

Just assign to that property: Dim oledb As OLEDBConnection You can get the command text for that OLEDBConnection object through its CommandText property - try typing this in the immediate pane (Ctrl+G) and see if you get the same output: ?ThisWorkbook.Connections("Query - Query1").OLEDBConnection.CommandText try typing that dot and see what that object has to offer. Refresh is a method of the OLEDB connection you want to modify: you already have a reference to the connection you want to modify the command text for - all you need is to invoke a method other than. ActiveWorkbook.Connections("Query - Query1").OLEDBConnection.Refreshįirst, replace ActiveWorkbook with ThisWorkbook, since you mean to be working with that specific workbook (the one hosting your VBA code), not whatever workbook happens to be currently active.