Link ms-access Query with OLE excel sheet?

I have a table and a query:


tblExcel


qryData





In "tblExcel" I have an OLE object field called oleSummary. I have saved the template excel file (Analysis.xls) in this OLE field. There are two sheets in this excel file: one is RawDataSheet and other one "Analysis_Sheet", The


Analysis_Sheet have charts and formula based on data saved in RawDataSheet.


Right now I have to manually copy my data from qryData and paste it on the Analysis.xls in OLE field so that I can view its summary/analysis in "Analysis_Sheet".


I have a command button on frmMain that opens my excel file through oleSummary bound object on the form.


Now what I want is to automate this process. I want automatically update RawDataSheet in my OLE excel file with my qryData when I open the excel file through frmMain (command click). I have seen some codes but that only work if you know path of the excel file. I do not want to use paths like c:\...path..\ .... because I want to keep everything together in one database.

Link ms-access Query with OLE excel sheet?
I don't know what version of access you're using, but you would have better luck linking your excel spreadsheet and access table if you use linked tables in microsoft access.