export total number of results to excel

[xopherira]

Does anyone have any suggestions on how to export the total number of rows returned from a query to a specific cell in excel? I am running an access Query and would like the total number returned populate a cell in an excel worksheet. I have tried the Importexportspreadsheet but it does not allow enough flexability for specific cell and data.

[ranman256]

you have to take control of a new instance of excel , then go directly to the cell....
NOTE: YOU MUST ADD EXCEL TO THE REFERENCES. vbe menu, tools, references, checkmark Excel

Public Sub ControlXL(byval pvXLfile)
Dim r As Long
Dim xl As Excel.Application


Set xl = CreateObject("excel.application")
With xl
    .Visible = True
    .Workbooks.Open pvXLfile
    .Range("C21").value = "abc"
    .activeworkbook.save
End With
Set xl = Nothing
End Sub

[xopherira]

That looks pretty close but where does the query fit in?

[ranman256]

i thought you'd know. this code is for 1 value.
if you want to paste the query

use COPYFROMRECORDSET

set rst = currentdb.openrecordset("qsMyQuery")


With xl
    .Visible = True
    .Workbooks.Open pvXLfile
    .Range("C21").value = "abc"
   
    .Activesheet.Range("j21").CopyFromRecordset rst
end with