Export to Excel, Import to Access Automatically 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!!!!!

 

[SkipVought]

I am so sorry. I meant to suggest CreateObject rather that GetObject.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[djrickel]

To other people like me who have the same question, here is how I solved the problem. In a nutshell, it deletes a file, runs a macro that exports data to Excel, then opens/closes/saves the two Excel files involved, then finally imports the data back to Access and previews a report. Hope this helps. Best of luck to you!

Private Sub Command515_Click()

DoCmd.DeleteObject acTable, "tblPlan1"
On Error GoTo 0

DoCmd.RunMacro "mcrExportToExcel"

Dim xlsApp As Excel.Application
Dim xlsBook1 As Excel.workBook
Set xlsBook1 = Workbooks.Open("C:\FS\DataFromAccess.xls")

Set xlsApp = xlsBook1.Parent
xlsApp.Visible = False

Dim xlsBook2 As Excel.workBook
Set xlsBook2 = Workbooks.Open("C:\FS\Plan1.xls")

Set xlsApp = xlsBook2.Parent
xlsApp.Visible = False


Workbooks("Plan1.xls").Close SaveChanges:=True
Workbooks("DataFromAccess.xls").Close SaveChanges:=True


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblPlan1", "C:\FS\Plan1.xls", , "Plan 1!A1:AE71"
DoCmd.OpenReport "rptPlan1", acViewPreview, , , acWindowNormal

End Sub