Edit Question - KnowledgeHub

Title Body

All I am trying to do is take a standard range on an excel sheet (i.e. a named range, or even A1:F100), and run some sql queries on it, and return a recordset that I can either step through in VBA code, or even just paste into some other sheet in the same workbook. Using ADODB was one thought, but how could I setup the connectionstring to point to some range within the current workbook? I know before I have made use of the Microsoft query wizard, which was not ideal, but would work. I can't seem to get this to refer to a range within the sheet, only other excel files. Here is the function I am left with. When I run it a few times my excel crashes with the usual out of resources error message. I have removed this function from my spreadsheet, and everything runs seamlessly multiple times, thus it is definitely caused by the code here. I have cleaned up all the objects (correctly?). Does anyone have any ideas what could be going wrong? Could there be something in the connection string that could be tweaked, or could it be something to do with the variant that is returned from the GetRows method? I am using MS ADO 2.8, and have also tried 2.5 with the same behaviour. Function getTimeBuckets() As Collection Dim strFile As String Dim strCon As String Dim strSQL As String Dim dateRows As Variant Dim i As Integer Dim today As Date Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Set getTimeBuckets = New Collection strFile = ThisWorkbook.FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" cn.Open strCon strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] where [Instrument Type] = 'LSTOPT'" rs.Open strSQL, cn dateRows = rs.GetRows rs.Close 'today = Date today = "6-may-2009" For i = 1 To UBound(dateRows, 2)

Tags (comma-separated) Cancel