VBA does not see Exported File

[dchaney]

Hello all,

I am driving myself crazy trying to figure this out, I have an Excel program that goes into SAP, runs some functions then exports the data into a default folder. The issue I am running into is how to close the export file after export? Once the VBA program completes the file remains open and has to be closed manually. I have tried the following thus far:

Private Sub CommandButton1_Click()

    Call SAPLoginOne
    
    For Each wb In Application.Workbooks
        Debug.Print wb.Name
        If wb.Name = "export.XLSX" Then wb.Close
    Next

End Sub

However it is not working, after the code runs it then pops up the file. I did the debug.print wb.name within the code to see what workbooks were listed, and it only brought back my main file and personal.xlsb. I assume that Excel does not see this new file until after the VBA code completes, is there anyway to force this to refresh to see this file?

Here is the SAPLogin macro incase it is needed to understand anything:

Sub SAPLoginOne()

Dim SapguiApp As Object, connection As Object, session As Object

    '****************************************************************************************
    'ESTABLISH CONNECTION TO SAP                                                            *
    '****************************************************************************************
On Error GoTo errFailed
    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("SAP", True)
    Set session = connection.Children(0)
On Error GoTo 0


    With session
    '****************************************************************************************
    'LOGON TO SAP                                                                           *
    '****************************************************************************************
        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = username
        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = password
        .findById("wnd[0]").sendVKey 0


    '****************************************************************************************
    'PROCESSES THE POPUP IF MULTIPLE LOGINS DETECTED                                        *
    '****************************************************************************************
        If .Children.Count > 1 Then
            .findById("wnd[1]/usr/radMULTI_LOGON_OPT2").Select
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End If


    '****************************************************************************************
    'NAVIGATES TO THE COOIS TCODE                                                                                             *
    '****************************************************************************************
        .findById("wnd[0]").resizeWorkingPane 105, 31, False
        .findById("wnd[0]/tbar[0]/okcd").Text = "/nCOOIS"
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/ctxtPPIO_ENTRY_" & _
        "SC1100-ALV_VARIANT").Text = layout_name
        .findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:" & _
        "PPIO_ENTRY:1200/ctxtP_SELID").Text setup_code


    '****************************************************************************************
    'USE THE ABOVE WC VARIABLES TO POPULATE THE WORK CENTERS WITHIN COOIS                   *
    '****************************************************************************************
        .findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:" & _
        "PPIO_ENTRY:1200/btn%_S_ARBPL_%_APP_%-VALU_PUSH").press
        .findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/" & _
        "tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = "84"
        .findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/" & _
        "tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").Text = "86"
        .findById("wnd[1]/tbar[0]/btn[8]").press
        .findById("wnd[0]/tbar[1]/btn[8]").press
        
    '****************************************************************************************
    'USED IF SAP DISPLAYS POPUP FOR SAVING FILE                                             *
    '****************************************************************************************
        .findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell") _
        .pressToolbarButton "&NAVIGATION_PROFILE_TOOLBAR_EXPAND"
        .findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell") _
        .pressToolbarContextButton "&MB_EXPORT"
        .findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell") _
        .selectContextMenuItem "&XXL"
        .findById("wnd[1]").sendVKey 0
   End With


    '****************************************************************************************
    'CLEAR ALL SET VARIABLES                                                                *
    '****************************************************************************************
    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing


Exit Sub


errFailed:
    MsgBox "The connection to SAP has been halted by the user."
    End
    
End Sub

 

[dchaney]

Found a workaround

Sub CommandButton1_Click()

    Call SAPLoginOne

    WasteTime (1)
        
    Call ImportData
    
End Sub


Sub WasteTime(Finish As Long)
 
    Dim NowTick As Long
    Dim EndTick As Long
 
    EndTick = GetTickCount + (Finish * 1000)
     
    Do
        NowTick = GetTickCount
        DoEvents
        
        For Each wb In Application.Workbooks
           If wb.Name = "export.XLSX" Then wb.Close
           NowTick = EndTick
        Next
    
    Loop Until NowTick >= EndTick
 
End Sub