Workbook not visible by VBA

[Tur3n]

Hi Guys,
I gathered SAP script together with VBA codes.
Basically, what problem I have.. when SAP script finishing own job, there is auto open of excel file.

I want to continue work on this open file, but VBA doesn't see this workbook and returns this error:

I have tried to rename file, I tried to open this workbook by commend related to path (but then i had error system has to re-open same workbook)..

Here is my VBA code, do you have some idea my experts how I could handle it in different way.. ?
I have heard that auto open excel file by some program in background may be not picked up by VBA code..

Sub GR_Check()

If Not IsObject(SAPApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SAPApp.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If

 Dim todayDate As Date
    Dim dateMinus7Days As Date
   
    ' Dzisiejsza data
    todayDate = Date
   
    ' Dzisiejsza data -7 dni
    dateMinus7Days = DateAdd("d", -7, todayDate)

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nZRLEI50040"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtP_BUKRS").Text = "C4B0"
session.findById("wnd[0]/usr/ctxtP_SNDPRN").Text = "ELSC4B0024"
session.findById("wnd[0]/usr/txtS_STATUS-LOW").Text = "51"
session.findById("wnd[0]/usr/ctxtS_CREDAT-LOW").Text = "2024-03-01"
session.findById("wnd[0]/usr/ctxtS_CREDAT-HIGH").Text = "2024-03-07"
session.findById("wnd[0]/usr/ctxtS_CREDAT-HIGH").SetFocus
session.findById("wnd[0]/usr/ctxtS_CREDAT-HIGH").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlCC_0100A/shellcont/shell").currentCellColumn = "DESCRP"
session.findById("wnd[0]/usr/cntlCC_0100A/shellcont/shell").selectedRows = "0"
session.findById("wnd[0]/usr/cntlCC_0100A/shellcont/shell").doubleClickCurrentCell
session.findById("wnd[0]/usr/cntlCC_0100B/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCC_0100B/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\a.forycki\Desktop\iDOC Tool\Logs"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "GR_CHECK.XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 13
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[15]").press

Workbooks("GR_CHECK.XLSX").Activate

n = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
Workbooks("iDOC TOOL.XLSM").Worksheets("Tool").Activate
Range("E5").Select
ActiveCell.FormulaR1C1 = n

End Sub

Here this step doesn't work.. -> Workbooks("GR_CHECK.XLSX").Activate

Thanks a lot !

 

[RoryA]

Most likely cause would be that the workbook is open in a different instance of Excel.

 

[Tur3n]

Hi RoryA,
is there some option or backup option to manage it and continue actions on this workbook ?

 

[Tur3n]

Hi All,
Does someone have some solution here for me ?

I still wonder how to solve this problem

Thanks!

 

[JEC]

Although this is not the most ideal method of sap data extractiok, you could try and add some waiting time in your code, before you activatr the workbook. If you step through the code, does it work then?

 

[Artik]

There are probably two problems:
1. once the commands are sent to SAP, further code executes asynchronously. That is, SAP takes care of creating the file, while the code execution goes on without waiting for a new file.
2. the new file opens another instance of Excel.

Take a look here (in Polish) to learn a little more.

Assumptions for the operation of the following macro:
1. the path and file name are constants, defined in the variables strPath and strFileName.
2. If an old file exists in this location, it will be deleted.
3. After issuing commands to SAP, we will wait in a loop until a file appears in the specified location. Only then will the rest of the code be executed.

Option Explicit

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    'For 64-Bit versions of Excel
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    'For 32-Bit versions of Excel
#End If


Sub GR_Check()
    Dim SAPApp As Object
    Dim SapGuiAuto As Object
    Dim Connection As Object
    Dim session As Object
    Dim WScript As Object

    Dim todayDate As Date
    Dim dateMinus7Days As Date
    Dim n As Long
    Dim strPath As String
    Dim strFileName As String
    Dim strFullName As String
    Dim xlApp As Excel.Application
    Dim objFSO As Object

    strPath = "C:\Users\a.forycki\Desktop\iDOC Tool\Logs"
    strFileName = "GR_CHECK.XLSX"
    strFullName = strPath & strFileName

    If Right(strPath, 1) <> Application.PathSeparator Then
        strPath = strPath & Application.PathSeparator
    End If

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'delete old file if it exists
    If objFSO.FileExists(strFullName) Then
        objFSO.GetFile(strFullName).Delete
    End If


    If Not IsObject(SAPApp) Then
        Set SapGuiAuto = GetObject("SAPGUI")
        Set SAPApp = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Connection) Then
        Set Connection = SAPApp.Children(0)
    End If
    If Not IsObject(session) Then
        Set session = Connection.Children(0)
    End If

    If Not IsObject(WScript) Then
        'Set WScript = ???? '<-- Something is missing here
    Else
        WScript.ConnectObject session, "on"
        WScript.ConnectObject Application, "on"
    End If

    ' Dzisiejsza data
    todayDate = Date

    ' Dzisiejsza data -7 dni
    dateMinus7Days = DateAdd("d", -7, todayDate)

    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nZRLEI50040"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/ctxtP_BUKRS").Text = "C4B0"
    session.findById("wnd[0]/usr/ctxtP_SNDPRN").Text = "ELSC4B0024"
    session.findById("wnd[0]/usr/txtS_STATUS-LOW").Text = "51"
    session.findById("wnd[0]/usr/ctxtS_CREDAT-LOW").Text = "2024-03-01"
    session.findById("wnd[0]/usr/ctxtS_CREDAT-HIGH").Text = "2024-03-07"
    session.findById("wnd[0]/usr/ctxtS_CREDAT-HIGH").SetFocus
    session.findById("wnd[0]/usr/ctxtS_CREDAT-HIGH").caretPosition = 10
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/usr/cntlCC_0100A/shellcont/shell").currentCellColumn = "DESCRP"
    session.findById("wnd[0]/usr/cntlCC_0100A/shellcont/shell").selectedRows = "0"
    session.findById("wnd[0]/usr/cntlCC_0100A/shellcont/shell").doubleClickCurrentCell
    session.findById("wnd[0]/usr/cntlCC_0100B/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
    session.findById("wnd[0]/usr/cntlCC_0100B/shellcont/shell").selectContextMenuItem "&XXL"
    session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\a.forycki\Desktop\iDOC Tool\Logs"
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "GR_CHECK.XLSX"
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 13
    session.findById("wnd[1]/tbar[0]/btn[11]").press
    session.findById("wnd[0]/tbar[0]/btn[15]").press

    'wait until the file appears in the specified location
    Do
        If objFSO.FileExists(strFullName) Then
            Exit Do
        End If
        Sleep 500
        DoEvents
    Loop

    Set xlApp = GetObject(strFullName).Application

    If Not xlApp Is Nothing Then
        n = xlApp.Workbooks(1).Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1

        xlApp.Workbooks(1).Close True
        xlApp.Quit
        Set xlApp = Nothing

        Workbooks("iDOC TOOL.XLSM").Worksheets("Tool").Range("E5").Value = n
        'or, if this code was run from the 'iDOC TOOL.XLSM' file
        'ThisWorkbook.Worksheets("Tool").Range("E5").Value = n
    Else
        'Failed to create a reference to a workbook opened in another instance
        MsgBox "Something went wrong!", vbExclamation
    End If
End Sub

Artik