I hope someone can give me some insight on Interacting VBA Application

[NYFashionToGo]

Hello and thanks for looking at this post.

I have a workbook in excel that has some code that i am working on in it... Basically its a series of macros that leads to the code that i put below....

In this workbook I am processing information in excel and then pushing it into Access and then refreshing a query back in excel for the results.... The problem that I am having is when I run my code... by the way It works perfectly the first time around.... If I open the excel workbook again to process more information at a later time during the day.. It does not work....


I have a series of macros that run in sequence Its all controles by a button on a userform...... I just went through processing step 1 then step 2 . Then saving file after each instance to narrow down my problem.... And then looking at Windows Task manager to see if excel was still running in background. I have narrowed it down to this Code.... But I dont know what I am doing wrong here or how to correct it. Or even how to search for help on this on the board...

Again before the code below is a series of codes that process in excel, .. (THE CODEBELOW) is step6 this step pushes the information itto access where access pushes it back to excel .. Its at this point when I got to save excel and close it out completly.. that it still runs in the background somehow...someway.... I dont know what I am doing wrong here.. Hopefully someone can give me some insight on a solution to this annoying problem.. Thank you..

Sub Step6ImportToAccess()

Dim appAcc As Object
Set appAcc = CreateObject("Access.Application")
appAcc.Visible = False


appAcc.OpenCurrentDatabase ("C:\NewYorkFashionToGo\NYFOrderProcessingNewEdition.mdb")


appAcc.DoCmd.RunMacro "DeleteOrders"
appAcc.DoCmd.TransferSpreadsheet acImport, 8, "OrderSummary", "C:\NewYorkFashionToGo\ExcelTools\In A New York Minute Processing New Edition3.xls", True, "OrderSummary!A1:AE5000"
appAcc.DoCmd.TransferSpreadsheet acImport, 8, "Orders", "C:\NewYorkFashionToGo\ExcelTools\In A New York Minute Processing New Edition3.xls", True, "Orders!A1:O5000"
appAcc.DoCmd.TransferSpreadsheet acImport, 8, "PayPalDownload", "C:\NewYorkFashionToGo\ExcelTools\In A New York Minute Processing New Edition3.xls", True, "PPDownload!A1:AF5000"
appAcc.DoCmd.RunMacro "ShippingMacro"
appAcc.DoCmd.Quit

Windows("In A New York Minute Processing New Edition3.xls").Activate

ActiveWorkbook.RefreshAll
Sheets("MasterConsole").Select

 

[JerryKlmns]

NYFashionToGo

Probably you have an unqualified reference to excel object like

Sheets("MasterConsole").Select

It would be better to use
yourXLObject.yourXLWorkBook.Sheets("MasterConsole").Select
I would also add this line

Set appAcc = Nothing

when you are done with access

 

[xlbo]

Could also be that your Access commands are still processing when you call the "RefreshAll" line - the code will not necessarily wait for each action to complete before moving on to the next...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question