Assign value of currently selected cell in SAP to variable in VBA-CodePudding

I'm having difficulty assigning the value of a cell I've selected in SAP to a variable using Visual Basic so that I can use this value later in the script. I'm able to select the cell I want with the following code:

session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell 6, "DEBIT"

But I haven't yet figured out how to assign the value of this SAP cell to a variable or to copy the value to a cell in my excel workbook. I'm trying to pull the value from SAP TCode FAGLB03 'Balance Display: G/L Accounts For the Ledger 0L'.

Screenshot of TCode trying to pull value from

Also, I would prefer to not use sendkeys if copying the value as I've found it can be unreliable.

I've looked through various forums already and attempted the following lines to no avail:

session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").copy
session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(6, "DEBIT") = amt
session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(6, 1) = amt

as well as a few others I'm unable to recall presently. Any help with this is greatly appreciated, this is my first post so apologies for any mistakes!

Full Code Below:

Sub SAPAuto()

Dim sh As Worksheet, wb As Workbook, tbl As ListObject, numrows As Integer, numcolmns As Integer, FiscalYear As Integer, _
    FiscalMonth As Integer, GLAccounts As Range, acct As Integer, co As String, CoCode As Range, amt As Long
Set wb = ThisWorkbook
FiscalYear = wb.Worksheets("Driver").Range("B15").Value
FiscalMonth = wb.Worksheets("Driver").Range("B16").Value
Set GLAccounts = wb.Worksheets("Reference Tables").[GL_Accounts]
Set CoCode = wb.Worksheets("Reference Tables").[Company_Code]


If Not IsObject(SAPGuiApp) Then
    Set SapGuiAuto = GetObject("SAPGUI")
    Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
    Set Connection = SAPGuiApp.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 SAPGuiApp, "on"
End If
    
For Each sh In wb.Worksheets
    co = sh.Range("A1")
    For Each tbl In sh.ListObjects
        sh.Activate
        numrows = tbl.DataBodyRange.Rows.Count
        numcolms = tbl.DataBodyRange.Columns.Count
        session.findById("wnd[0]").maximize
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/nFAGLB03"
        session.findById("wnd[0]").sendvkey 0
        session.findById("wnd[0]/usr/ctxtRACCT-LOW").Text = Right(tbl.Name, 5)
        session.findById("wnd[0]/usr/ctxtRBUKRS-LOW").Text = co
        session.findById("wnd[0]/usr/txtRYEAR").Text = FiscalYear    'This is varaible
        session.findById("wnd[0]").sendvkey 8
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "DEBIT"
        'Need to add a line in here where I can get currentcell value and assign it to variable amt
        tbl.DataBodyRange.Cells(numrows, 4).Value = amt
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "CREDIT" = amt
        tbl.DataBodyRange.Cells(numrows, 5).Value = amt
        'Need to add a line in here where I can get currentcell value and assign it to variable amt
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "BALANCE" = amt
        tbl.DataBodyRange.Cells(numrows, 6).Value = amt
        'Need to add a line in here where I can get currentcell value and assign it to variable amt
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "CUMULATIVE BALANCE" = amt
        tbl.DataBodyRange.Cells(numrows, 7).Value = amt
        'Need to add a line in here where I can get currentcell value and assign it to variable amt
    Next tbl
Next sh

MsgBox "Success"
End Sub

CodePudding user response：

If it is a GRID, the syntax you are looking for should look like this:

amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getcellvalue (6, "DEBIT")

Regards, ScriptMan