here is my sample code. i reall cant figure out how to copy a rumba string/data to excel
'CONNECT TO RUMBA
Public Declare Function WD_ConnectPS Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal NameSpace As String) As Integer
'SEND KEY OR TYPE IN RUMBA
Public Declare Function WD_SendKey Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal KeyData As String) As Integer
Public Declare Function WD_CopyStringToField Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Integer, ByVal Position As Integer, ByVal Buffer As String) As Integer
'COPY THE STRING FROM RUMBA SCREEN
Public Declare Function WD_CopyPSToString Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal Position As Integer, ByVal HoldString As String, ByVal Length As Integer) As Integer
Sub ConnectRumba()
connectr = WD_ConnectPS(1, "B")
If connectr = 0 Then
Else
Call ConnectRumba
End If
End Sub
Sub PressRumbaEnter()
RV = WD_SendKey(1, "@E")
End Sub
Sub GetDate()
Dim GetString, PutString, LB, POL As String
Dim X As Integer
Dim CCCC As String
Set Wb = ThisWorkbook.Sheets("Policy").Cells
LR = ThisWorkbook.Sheets("Policy").Range("b" & Rows.Count).End(xlUp).Row
For X = 2 To LR
LB = Wb(X, 1).Value
POL = Wb(X, 2).Value
Call ConnectRumba
If Len(POL) = 4 Then
POL = "00000" & POL
ElseIf Len(POL) = 5 Then
POL = "0000" & POL
ElseIf Len(POL) = 6 Then
POL = "000" & POL
ElseIf Len(POL) = 7 Then
POL = "00" & POL
ElseIf Len(POL) = 8 Then
POL = "0" & POL
End If
PutString = WD_CopyStringToField(1, 5, LB)
PutString = WD_CopyStringToField(1, 14, POL)
PutString = WD_CopyStringToField(1, 28, "Payment")
Call PressRumbaEnter
Variable = WD_CopyPSToString(1, RowCol(3, 30), CD, 4)
ThisWorkbook.Sheets("Policy").Cells(X, 3).Value = CD
Next X
End Sub
Function RowCol(RowIndex As Long, ColumnIndex As Long) As Long
RowCol = ((RowIndex - 1) * 80) ColumnIndex
End Function
#RumbaHi Dumskie,
the following demonstrates how to scrape a string from a Rumba screen using the WD_CopyPSToString function.
'CONNECT TO RUMBA
Private Declare Function WD_ConnectPS Lib "C:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal NameSpace As String) As Integer
'SEND KEY OR TYPE IN RUMBA
Private Declare Function WD_SendKey Lib "C:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal KeyData As String) As Integer
Private Declare Function WD_CopyStringToField Lib "C:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Integer, ByVal Position As Integer, ByVal Buffer As String) As Integer
'COPY THE STRING FROM RUMBA SCREEN
Private Declare Function WD_CopyPSToString Lib "C:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal Position As Integer, ByVal HoldString As String, ByVal Length As Integer) As Integer
'DISCONNECT FROM THE RUMBA SCREEN
Private Declare Function WD_DisconnectPS Lib "EhlApi32.dll" (ByVal hInstance As Integer) As Integer
Sub GetRowTop5Rows()
Dim RetC As Integer, i As Integer
Dim myScreen As String
RetC = WD_ConnectPS(1, "D")
For i = 1 To 5
If RetC = 0 Then
Dim myPSstring As String
myPSstring = Space(80)
RetC = WD_CopyPSToString(1, ((i - 1) * 80) 1, myPSstring, 80)
myScreen = myScreen & myPSstring & Chr$(13) & Chr$(10)
End If
Next i
MsgBox myScreen
RetC = WD_DisconnectPS(1)
End Sub
Hope that helps,
Tom
here is my sample code. i reall cant figure out how to copy a rumba string/data to excel
'CONNECT TO RUMBA
Public Declare Function WD_ConnectPS Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal NameSpace As String) As Integer
'SEND KEY OR TYPE IN RUMBA
Public Declare Function WD_SendKey Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal KeyData As String) As Integer
Public Declare Function WD_CopyStringToField Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Integer, ByVal Position As Integer, ByVal Buffer As String) As Integer
'COPY THE STRING FROM RUMBA SCREEN
Public Declare Function WD_CopyPSToString Lib "D:\\Program Files (x86)\\Micro Focus\\Rumba\\System\\Ehlapi32.DLL" _
(ByVal hInstance As Long, ByVal Position As Integer, ByVal HoldString As String, ByVal Length As Integer) As Integer
Sub ConnectRumba()
connectr = WD_ConnectPS(1, "B")
If connectr = 0 Then
Else
Call ConnectRumba
End If
End Sub
Sub PressRumbaEnter()
RV = WD_SendKey(1, "@E")
End Sub
Sub GetDate()
Dim GetString, PutString, LB, POL As String
Dim X As Integer
Dim CCCC As String
Set Wb = ThisWorkbook.Sheets("Policy").Cells
LR = ThisWorkbook.Sheets("Policy").Range("b" & Rows.Count).End(xlUp).Row
For X = 2 To LR
LB = Wb(X, 1).Value
POL = Wb(X, 2).Value
Call ConnectRumba
If Len(POL) = 4 Then
POL = "00000" & POL
ElseIf Len(POL) = 5 Then
POL = "0000" & POL
ElseIf Len(POL) = 6 Then
POL = "000" & POL
ElseIf Len(POL) = 7 Then
POL = "00" & POL
ElseIf Len(POL) = 8 Then
POL = "0" & POL
End If
PutString = WD_CopyStringToField(1, 5, LB)
PutString = WD_CopyStringToField(1, 14, POL)
PutString = WD_CopyStringToField(1, 28, "Payment")
Call PressRumbaEnter
Variable = WD_CopyPSToString(1, RowCol(3, 30), CD, 4)
ThisWorkbook.Sheets("Policy").Cells(X, 3).Value = CD
Next X
End Sub
Function RowCol(RowIndex As Long, ColumnIndex As Long) As Long
RowCol = ((RowIndex - 1) * 80) ColumnIndex
End Function
#RumbaHI, You could simply create an ODBC connection (via control panel/administrative tools/Data Sources (ODBC)
And then in Excel on the Data tab, From Other Sources, select from Microsoft Query, from Microsoft Query, select your ODBC connection, then you can select a table or write some sql to retrieve what you like
Adrian
