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 :dunno:


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