Run Excel Macro From Access 2003 Conversion Problem

[Kevin Kilpatrick]

My 2000 access application shelled to Excel, ran a macro that prepared Oracle
output for importation to Access 2000. I used the Microsoft "178116; ACC:
How to Determine When a Shelled Process Ends" logic and it worked perfectly.
When I upgraded to Access 2003, the Excell macros now runs twice more AFTER
the shelled routine has finished. I tried substituting the "129796 How To
Use a 32-Bit Application to Determine When a Shelled Process Ends" and
"209876 HOW TO: Determine When a Shelled Process Ends in Access 2000".
Neither corrected the problem. I inserted several debug print statements in
both the Access function and the Excel macro and determine that yes, the
module completed all statements correctly, but, before exiting the sub, the
Excell macro runs twice more. If I run just the Excel macro, it runs just
one time, as it should.

I address this to the Access newsgroup since my base application is in Access.

Any thoughts will be appreciated.

Kevin

 

[Douglas J. Steele]

Without knowing the actual code you're using, it's difficult to say.
However, I'd recommend using Automation, rather than shelling to Excel.

 

[Kevin Kilpatrick]

Looking at automation, I don't think this is the solution for my headache.
I need to run the Excel macro within a Access routine as the remaining code
totally relies on the Excel macro's results. Code segment follows:

strexcel = "Excel.exe " & varxlspath
ExecCmd strexcel

'Clear Tables
DoCmd.OpenQuery "q_Delinitiated", acViewNormal, acEdit 'Clear
t_initiated Table
DoCmd.OpenQuery "q_Delmaindata", acViewNormal, acEdit 'Clear
t_maindata Table
DoCmd.OpenQuery "q_Delhudm", acViewNormal, acEdit 'Clear
t_hudm Table
DoCmd.OpenQuery "q_Delicmdata", acViewNormal, acEdit 'Clear
t_icmdata
DoCmd.OpenQuery "q_Delcdsdata", acViewNormal, acEdit 'Clear
t_cdstartend
DoCmd.OpenQuery "q_Deldkrpt", acViewNormal, acEdit 'Clear
t_dkrpt
DoCmd.OpenQuery "q_Delstaffasgn", acViewNormal, acEdit 'Clear
t_staffasgn
DoCmd.OpenQuery "q_Delcasesbyhours", acViewNormal, acEdit 'Clear
t_casesbyhours
DoCmd.OpenQuery "q_Delcaserank", acViewNormal, acEdit 'Clear
t_caserank

DoCmd.TransferSpreadsheet acImport, 8, "t_initiated", varcmipath, True
DoCmd.TransferSpreadsheet acImport, 8, "t_hudm", varhudpath, True
DoCmd.TransferSpreadsheet acImport, 8, "t_icmdata", varicmpath, True
DoCmd.TransferSpreadsheet acImport, 8, "t_cdstartend", varcdspath, True
DoCmd.TransferSpreadsheet acImport, 8, "t_dkrpt", vardkrpath, True
DoCmd.TransferSpreadsheet acImport, 8, "t_casesbyhours", varcbhpath,
True

The routine continues to the Exit Sub (checked this using debug.print).
ExecCmd function is pulled directly from KB article 178116 "ACC: How to
Determine When a Shelled Process Ends"" with no code changes. I tracked
Excel in Task Manager. After each run, the Excel process dropped from the
task manager. Which means something was starting Excel outside of the
routine. But what?