Possible to read RecordCount for last exececution of a querydef?

Question

I have a saved querydef that I am exporting, with a filter, like so:

DoCmd.OutputTo acOutputQuery, tmpQueryName, acSpreadsheetTypeExcel9, appDirectory & "\" & tmpQueryName & ".xls", True

I'd like to be able to store in an execution history table the queryname, timestamp, filter, and recordcount of the executed querydef. It would be easy to re-execute in code, but it can be a very long running query, so I don't want to re-execute it. Is there any way of reading the recordcount of the DoCmd.OutputTo, or else reading the LastExecRecordCount from the querydef itself, or some other way I could accomplish this?

Answer 1

In this example, since the full path to the exported excel file is known, one can do:

Dim xl As Excel.Workbook
    Set xl = GetObject(appDirectory & "\" & tmpQueryName & ".xls")
    Dim lastRow As Integer
    lastRow = xl.ActiveSheet.Cells(xl.ActiveSheet.Rows.Count, 1).End(xlUp).Row ' the spreadsheet row number of the last row that contains data
    rowcount = lastRow - 1  'account for header row