Automate Export/Import without linking?

[djrickel]

I need to automate a process of exporting data to Excel, allowing a spreadsheet no-so-affectionately called The Monster, to do its magic, then import the data back to Access to be used in an already-formatted report. I'm smart enough to recognize that this requires VBA but not at all in order to code it out so that it functions. Tried and wasted over a week so I'm begging some kind-hearted whiz to help me.




NO WE ARE NOT INTERESTED IN LINKING ACCESS TO THE MONSTER. Tried and it crashes both Access and Excel every time. It's not called The Monster for nothing. This is what I've done and what I need help with...(don't laugh)...


1. In Access, have a query that creates a recordset of variables.
2. Have a macro called mcrExportToExcel that uses the query to export those variables to C:\FS\DataFromAccess.xls
2a.(killing the existing file first, since it will always exist).
2b.I suppose I should instead run DoCmd.TransferSpreadsheet to DataFromAccess.xls in A1:K1...I'm open to your expertise!


3. Linked those variables to The Monster in C:\FS\Plan1.xls.


* Need a way to quietly first open C:\FS\DataFromAccess.xls, then C:\FS\Plan1.xls, without the end-user knowing it, so that the links can update and Plan1.xls can do it's beautiful magic.


* In Plan1.xls, need to automatically clear then reapply a filter that hides "." in A1:A70. The need to clear & refilter will need to occur each time the file is opened.


* Need to quietly save and close both Excel files without any messages -- remember the user doesn't know this is all happening.


4. Run DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblPlan1", "C:\FS\Plan1.xls", , "Plan 1!A1:AE71" to import data back to Access into a table called tblPlan1.


5. Use an already-formatted Access report named rptPlan1 to preview or print..


Is there a kind soul who will have pity on someone who is trying very hard to self-learn and is exhausted from wasting a week on this already? Please don't tell me this needs VBA--I know that. I need code as I have tried and failed and have little hair left to pull out. PLEASE DON'T LET ME GO BALD!!!!!

[June7]

Yes, Access can open Excel objects in VBA and manipulate unbeknownst to the user.

There is tons of code out there on this. Can't give specifics because just don't know your setup well enough. Here is one reference for a start:

http://forums.aspfree.com/microsoft-...el-414974.html

Also, Excel can link to Access.

Suggest baby steps. Develop and test with workbook other than the Monster.