StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First of all, I cannot see how you assigned your sheet to object "Sheet15" - maybe this is part of the problem. If you have a named range, you don't need a Sheet object before that.</p> <p>Secondly, .Autofilter with no arguments just toggles the Autofilter arrow on/off, and you loose your criteria.</p> <p>Try this code, it worked for me (Office 2003) ... I have a header in A1, followed by A2..A13 named as DRCTable, followed by a =SUM(DRCTable) in A14</p> <p><strong>edit 02-Jan-2010</strong></p> <p><em>(sorry I can't test ODBC from my holiday domicile so this part is tested only syntactically)</em> </p> <pre><code>Sub fillTable() Dim MySheet As Worksheet, MyRange As Range Dim MyQRY As QueryTable, MyCONNECT As String, MySELECT As String ' added 02-Jan-2010 'initialize Set MySheet = Sheets("Sheet1") Set MyRange = Range("DRCTable") MyCONNECT = "ODBC;..." ' added 02-Jan-2010 MySELECT = "SELECT * FROM DUAL" ' added 02-Jan-2010 .. noticed my Oracle past :) ?? Set MyQRY = ActiveSheet.QueryTables.Add(Connection:=MyCONNECT, _ Destination:=MyRange, _ Sql:=MySELECT) ' clean up MySheet.AutoFilterMode = False MyRange.ClearContents ' simulate Query removed 02-Jan-2010 ' MyRange(1, 1) = 1 removed 02-Jan-2010 ' MyRange(2, 1) = 2 removed 02-Jan-2010 ' MyRange(3, 1) = 3 removed 02-Jan-2010 ' MyRange(4, 1) = 4 removed 02-Jan-2010 ' MyRange(5, 1) = 5 removed 02-Jan-2010 ' added 02-Jan-2010 ' BackgroundQuery = FALSE waits until query is executed ' = TRUE continues with VBA once connection to DB is made MyQRY.BackgroundQuery = False ' set this to affect all subsequet refresh ' and don't use the bool in refresh except ' to override the setting MyQRY.Refresh False ' the false sets BackgroundQuery for the individual refresh; ' there is no default on this param within the refresh method. ' If unspecified here the QueryTable.BackgroundQuery property ' is examined ' select data range plus header and filter Range(MyRange.Offset(-1, 0), MyRange(12, 1)).AutoFilter _ Field:=1, Criteria1:="<>" End Sub </code></pre> <p>Hope that helps Good luck MikeD</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload