Here's the code:
' Declare the Excel Object
Dim appExcel As Object
Dim wbExcel As Object
Dim aSheet As Object
Set AppExcel = CreateObject("Excel.Application")
Set wbExcel = AppExcel.WorkBooks.Open("p:/CM_Members.xls")
Set aSheet = wbExcel.Sheets("Sheet1")
' Decalre the variables
Dim MemIdNum As String
Dim MemDigit As Integer
Dim MemExist As String
Dim MemberCounter As Integer
MemberCounter = 0
' Ask for starting member name number
MemDigit = InputBox("Please enter starting number for member name:")
' Check if the user canceled.
If MemDigit = 0 Then
GoTo TheEnd
Else
' Begin the work
Sess0.Screen.Sendkeys("<Tab>10msmc<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
For x = 1 to aSheet.UsedRange.Rows.Count
MemIdNum = aSheet.Cells(x,1).text
Sess0.Screen.Sendkeys("<EraseEOF>")
Sess0.Screen.PutString MemIdNum
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Check for existing member.
MemExist = Sess0.Screen.GetString(06,19,32) <= this is the variable I'm using to check for error. If the MemIdNum from above is already assigned to a member, the member's name will be written on the screen starting at these coords. But I have no idea what the name might be beforehand.
'THIS IS NOT WORKING!
If MemExist <> "" Then
MsgBox "This HRN is already in use!"
Sess0.Screen.Sendkeys("<Pf5>") <= PF5 will clear the screen of all member data retrieved.
MsgBox MemExist <= I'm using this as a debug step. If the member number is not in use, it will return an empty box. If the member number is in use, it will return the member's name as expected.
MemExist = ""
MsgBox MemExist <= I'm using this as a debug step to show me the value of MemExist before continuing.
Essentially, the error messeage "This HRN is already in use" is displayed evern if the member number is not in use.
Else
Sess0.Screen.WaitForString "MBRS1030A -- No matches found.",21,02
Sess0.Screen.Sendkeys("<BackTab>a<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Pf5>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForString "MBRS1992A -- This is a manual HRN.",21,02
Sess0.Screen.Sendkeys("nwets,nwmbr")
Sess0.Screen.PutString MemDigit
Sess0.Screen.Sendkeys("<Tab><Tab><Tab>06061935<Tab><Tab><Tab>m4900 sw meadows rd<Tab><Tab><Tab>lake oswego<Tab>or97035<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForString "MBRS0011I -- Update successfully completed.",21,02
Sess0.Screen.Sendkeys("<Pf5>")
MemberCounter = MemberCounter + 1
MemDigit = MemDigit + 1
End If
Next
Sess0.Screen.Sendkeys("<Home>")
Sess0.Screen.Sendkeys("msmn<Enter>")
MsgBox "Number of members created: " & MemberCounter
End If
TheEnd:
appExcel.Quit
End Sub