Copying text from SmarTerm into Excel with VBA

[ashburton88]

Hi,

I'm working on a macro to replace something we have been using for many years running in WRQ Reflection, but now have to run through SmarTerm instead.

I've got the majority of the code worked out, and I'm able to copy using VBA into the relevant excel cells, however I'm currently using exact column/row references to select the data which isn't going to work for all of the needs of the report, as the position of the data in SmarTerm can change based on the results of the query.

I'm using Session.StringWait.MatchString to find specific text to allow input, but for some reason I can't get this to work to then copy the entire row on which the found text is on, is this something that's possible with VBA & SmarTerm? I can't locate this as an option in the help manual they provide.

Here's the code I've got currently for the search and select, currently the macro just hangs as it seems it can't find the specified text

Session.StringWait.MatchString "SEARCH TEXT"    
    Session.StringWait.Start
    If Session.StringWait.Status = 1 Then
        SearchText = Session.SelectScreenAtCoords(Session.FoundTextRow, 20, Session.FoundTextRow, 45)
        If SearchText Then
             SearchText$ = Session.ScreenText(Session.FoundTextRow, 20, 1, 25)
             rng_Range_Constants_Col_A.Offset(0, 4).Value = "SEARCH TEXT: " & SearchText$
        Else
             rng_Range_Constants_Col_A.Offset(0, 4).Value = "NOTHING FOUND"
        End If
    End If

FoundTextRow was something we used in the old Macro, I don't know if this will continue to work with SmarTerm or not, so if not if there's an alternative I need to use I'd welcome support/suggestions on this.

[SamT]

I have no clue what the code is doing, but this may help :

Session.StringWait.MatchString "SEARCH TEXT" 
Session.StringWait.Start 
Const Secs As Double  = 0.000011574   'Seconds = 1 / 24 * 60 * 60
Do while Session.StringWait.Status = [Not ready for Primetime] '???
    Application.Wait (Now + 2 * Secs)
    DoEvents
Loop
If Session.StringWait.Status = 1 Then 
    SearchText = Session.SelectScreenAtCoords(Session.FoundTextRow, 20, Session.FoundTextRow, 45) 
    If SearchText Then 
        SearchText$ = Session.ScreenText(Session.FoundTextRow, 20, 1, 25) 
        rng_Range_Constants_Col_A.Offset(0, 4).Value = "SEARCH TEXT: " & SearchText$ 
    Else 
        rng_Range_Constants_Col_A.Offset(0, 4).Value = "NOTHING FOUND" 
    End If 
End If

[ry94080]

Did you get this figured out?

[Aussiebear]

Maybe this as a basic concept

Sub CopySmartermDataToExcel()
    Dim objSmarterm As Object
    Dim objSession As Object
    Dim strData As String
    Dim arrLines() As String
    Dim arrFields() As String
    Dim i As Long, j As Long, rowNum As Long, colNum As Long
    ' Attempt to create an Smarterm object. Adjust the ProgID if needed.
    On Error Resume Next
    Set objSmarterm = CreateObject("EXTRA.System") 
    ' Example ProgID, adjust as needed
    If objSmarterm Is Nothing Then
        MsgBox "Smarterm is not installed or the ProgID is incorrect.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
        ' Get the active session.
        Set objSession = objSmarterm.ActiveSession
        If objSession Is Nothing Then
            MsgBox "No active Smarterm session found.", vbExclamation
            Exit Sub
        End If
        ' Copy the screen data. This often depends on how the data is structured.
        ' This is a VERY basic example, you'll need to adapt it.
        strData = objSession.Screen.GetString(1, 1, objSession.Screen.Rows * objSession.Screen.Cols) 
        ' Get everything.
        ' Split the data into lines.
        arrLines = Split(strData, vbCrLf) 
        ' Or vbLf, check your Smarterm data.
        ' Start writing to Excel from row 1, column 1.
        rowNum = 1    colNum = 1
        ' Loop through each line and split into fields (if necessary).
        For i = LBound(arrLines) To UBound(arrLines)
            ' Example: Split by spaces or tabs. Adjust as needed.
            arrFields = Split(arrLines(i), vbTab) 
            ' or " " or another delimiter.
            ' Write each field to a cell.
            colNum = 1 
            ' Reset column for each line.
            For j = LBound(arrFields) To UBound(arrFields)
                Cells(rowNum, colNum).Value = arrFields(j)
                colNum = colNum + 1
            Next j
            rowNum = rowNum + 1
        Next i
        ' Clean up objects.
        Set objSession = Nothing
        Set objSmarterm = Nothing
        MsgBox "Data copied successfully!", vbInformation
End Sub