MSProject and Excel question. Broken loopage!

[queenb2005]

I'm trying to open a folder of mpp files in sequence, copy some data, paste it in an Excel sheet and fully automate the whole procedure. Here it is so far.

Dim temp As Object
    Dim path As String
    Dim currfile As String
    Dim Path1 As String
    Dim xl3 As Object
    Dim Temp1 As String
    Dim appXL As Object
    currfile = Dir("\\filesvr\department\pe\sch_control\IMSApr\*.mpp")
    path = "\\filesvr\department\pe\sch_control\IMSApr\"
    Path1 = "\\filesvr4\department\pe\sch_control\downloads\beth\Update_Template.xls"
    
    Do While currfile <> ""
        Set temp = GetObject(path & currfile)
        WindowNewWindow Projects:=currfile, ShowDialog:=False
        '***********************
        chDir ("\\P989-filesvr4\department\pe\sch_control\IMSApr\")
        Temp1 = ActiveWindow
        Parent.WindowActivate (Temp1)
        OutlineShowAllTasks
        ViewApply Name:=".SLRS Export for Update Sheet"
        SelectTaskColumn Column:="ID", Additional:=19, Extend:=True
        EditCopy
        Set xl3 = GetObject(Path1)

Then the excel sheet opens and the auto_open calls this macro. (The 3 macros in the midst are just formatting and populating some fields, nothing major)

Dim mon As String
mon = Format(Now, "mmmm_yy")
Dim tempxl As String
Dim path As String
Application.ScreenUpdating = False
path = "\\filesvr\department\pe\sch_control\Downloads\Beth\Originals\"
    Range("A17").Select
    ActiveSheet.Paste
    If Range("E18").Text <> "" Then
        Columns("A:A").Select
        Selection.Delete Shift:=xlToLeft
        Macro2
        Macro3
        Macro6
        tempxl = Range("E18").Text
        Range("A11").Select
        ActiveWorkbook.SaveAs path & tempxl & "_" & mon & "_Update_", _
            FileFormat:=xlNormal
    End If

That part works fine and saves it correctly. This is the ending of the macro code in Project

xl3.Parent.Windows(1).Close
        FileClose pjDoNotSave
        '************************
        currfile = Dir
    Loop
 
    MsgBox "Status Sheets completed."
End Sub

It finishes the last portion and closes the open Project file without saving but will not loop, it just ends. I'm pretty sure it has to do with the possiblity that the macro running in Excel takes longer to complete and is running at the same time as the macro in Project. It's hard to control one office application from another. Is it possible to make this loop work or am I trying in vain? Any help offered would be appreciated.

~QueenB

[Br1an_g]

i think what you are trying to do is fine.

what you would be better doing, is using a single app..e.g. Excel to do all of the work for you.

instead of passing control between open apps the way you are doing.

so put all of your code into Excel/Access and alter your routine to do all of the work without worrying about which app is doing what code.

hope that makes sense.

[EDIT] forgot to add, i think the reason it is not looping is because you are closing the controlling app?