trickshot1126
Technical User
Hello,
Thank you for all the knowledge on this site... It is beyond helpful.
I have a good understanding of Excel VBA coding but am trying to move some things over to Access for historical reporting purposes. Currently in excel i have coding that scrapes from attachmate and pastes to cells in excel.
What i am trying to do is scrape the same info but rather than going to excel cells place the date into a data table in Access.
The following is my excel scripting:
Private Sub Cancelform()
Dim extra As Object
Dim PMS As Object
Dim sInput As String, Agent As String, Name As String, Adress1 As String, Adress2 As String
Dim iWait As Integer, I As Integer, finalRow As Integer, fail As Integer
Dim polNums As String, Cell As String, Zip As String, Effdate As String, Expdate As String, Homeprem As String, Autoprem As String, acode As String
'Verify that the user chose the right macro
iWait = MsgBox("Please be patient and do not touch PMS or Excel after hitting OK.", vbOKCancel)
If iWait = 2 Then
MsgBox (" Process Cancelled!")
Exit Sub
End If
iWait = 30 'milliseconds to wait for server to stop sending data
'Identify the last cell that contains a policy number
finalRow = ActiveSheet.Range("A65536").End(xlUp).Row
finalcolumn = Range("IV1").End(xlToLeft).Column
Range("A2:A" & finalRow).Select
Set polNums = Selection
Set extra = CreateObject("Extra.System")
Set PMS = extra.activesession.screen
'Set our number of failed policies equal to zero
I = 0
'Grab the info
For Each Cell In polNums
'If the policy number starts with a zero, the system will remove the leading zeros.
'Thus we "recast" the value to a string and ensure the policy number is seven
'characters long. It would only ever be shorter because it missed leading zeros.
If Cell = "" Then GoTo NextPolicy
If Cell.Offset(, 10).Value = "Pass" Then GoTo NextPolicy
sInput = Cell.Value
Do While Len(sInput) < 7
sInput = "0" & sInput
Loop
PMS.SendKeys ("<Clear>EINQ " & sInput & "<Enter>")
Do Until PMS.getstring(3, 2, 1) = "S" Or PMS.getstring(1, 69, 3) = "NOT" Or PMS.getstring(1, 63, 7) = "INVALID"
PMS.waithostquiet (iWait)
Loop
'If there is an entry pending on the EINQ screen, we will skip this policy
'If our policy number is invalid, add the word "Fail" in the color red to our results page
If PMS.getstring(1, 63, 7) = "INVALID" Then
Cell.Offset(, 11).Value = "Invalid Policy Number"
GoTo fail
End If
'**************************************************************************
'Go to PBBC Screen to get Insured Name, address, and dates
PMS.SendKeys ("<Clear>PBBC " & sInput & "<Enter>")
Do Until PMS.getstring(3, 2, 3) = "UND"
PMS.waithostquiet (iWait * 10)
Loop
Name = PMS.getstring(5, 10, 29)
Cell.Offset(, 1).Value = Name
Adress1 = PMS.getstring(6, 10, 29)
Cell.Offset(, 2).Value = Adress1
Adress2 = PMS.getstring(6, 41, 29)
Cell.Offset(, 3).Value = Adress2
Zip = PMS.getstring(6, 74, 5)
Cell.Offset(, 4).Value = Zip
Effdate = PMS.getstring(9, 14, 6)
Cell.Offset(, 5).Value = Effdate
Expdate = PMS.getstring(9, 34, 6)
Cell.Offset(, 6).Value = Expdate
acode = PMS.getstring(8, 33, 7)
Cell.Offset(, 8).Value = acode
'Go to PBOI Screen to get Agent
PMS.SendKeys ("<Clear>PBOI " & sInput & "<Enter>")
Do Until PMS.getstring(3, 2, 3) = "LOB"
PMS.waithostquiet (iWait * 10)
Loop
Agent = PMS.getstring(3, 48, 30)
Cell.Offset(, 7).Value = Agent
'Go to PBPR Screen to get Premium
PMS.SendKeys ("<Clear>PBPR " & sInput & "<Enter>")
PMS.waithostquiet (iWait * 10)
If PMS.getstring(1, 7, 1) = "A" Then
Autoprem = PMS.getstring(2, 24, 10)
Cell.Offset(, 10).Value = Autoprem
GoTo Pass
End If
Homeprem = PMS.getstring(16, 39, 12)
Cell.Offset(, 9).Value = Homeprem
Pass:
'Add the word "Pass" in the color green to our results if the PUUC is successful
Cell.Offset(, 11).Value = "Pass"
Cell.Offset(, 11).Interior.ColorIndex = 4
GoTo NextPolicy
fail:
'Add the word "Fail" in the color red to our results if we cannot change tier
Cell.Offset(, 11).Value = "Fail"
Cell.Offset(, 11).Interior.ColorIndex = 3
fail = fail + 1
'Move on to the next policy
NextPolicy:
Cell.Offset(1, 0).Activate
Next Cell
Range(Cells(1, 1), Cells(finalRow, 5)).Borders.Weight = xlThin
ActiveSheet.Range("A2").Activate
ActiveWindow.FreezePanes = True
If fail > 0 Then
MsgBox "There were a total of " & fail & " policie(s) that failed during processing. Use the filter to identify these failures."
Else
MsgBox "Process 100% Successful."
End If
Exit Sub
As you can see it travels through different screens, collects the data and pastes it to excel.
Any help would be awesome.
Thank you,
Rob
Thank you for all the knowledge on this site... It is beyond helpful.
I have a good understanding of Excel VBA coding but am trying to move some things over to Access for historical reporting purposes. Currently in excel i have coding that scrapes from attachmate and pastes to cells in excel.
What i am trying to do is scrape the same info but rather than going to excel cells place the date into a data table in Access.
The following is my excel scripting:
Private Sub Cancelform()
Dim extra As Object
Dim PMS As Object
Dim sInput As String, Agent As String, Name As String, Adress1 As String, Adress2 As String
Dim iWait As Integer, I As Integer, finalRow As Integer, fail As Integer
Dim polNums As String, Cell As String, Zip As String, Effdate As String, Expdate As String, Homeprem As String, Autoprem As String, acode As String
'Verify that the user chose the right macro
iWait = MsgBox("Please be patient and do not touch PMS or Excel after hitting OK.", vbOKCancel)
If iWait = 2 Then
MsgBox (" Process Cancelled!")
Exit Sub
End If
iWait = 30 'milliseconds to wait for server to stop sending data
'Identify the last cell that contains a policy number
finalRow = ActiveSheet.Range("A65536").End(xlUp).Row
finalcolumn = Range("IV1").End(xlToLeft).Column
Range("A2:A" & finalRow).Select
Set polNums = Selection
Set extra = CreateObject("Extra.System")
Set PMS = extra.activesession.screen
'Set our number of failed policies equal to zero
I = 0
'Grab the info
For Each Cell In polNums
'If the policy number starts with a zero, the system will remove the leading zeros.
'Thus we "recast" the value to a string and ensure the policy number is seven
'characters long. It would only ever be shorter because it missed leading zeros.
If Cell = "" Then GoTo NextPolicy
If Cell.Offset(, 10).Value = "Pass" Then GoTo NextPolicy
sInput = Cell.Value
Do While Len(sInput) < 7
sInput = "0" & sInput
Loop
PMS.SendKeys ("<Clear>EINQ " & sInput & "<Enter>")
Do Until PMS.getstring(3, 2, 1) = "S" Or PMS.getstring(1, 69, 3) = "NOT" Or PMS.getstring(1, 63, 7) = "INVALID"
PMS.waithostquiet (iWait)
Loop
'If there is an entry pending on the EINQ screen, we will skip this policy
'If our policy number is invalid, add the word "Fail" in the color red to our results page
If PMS.getstring(1, 63, 7) = "INVALID" Then
Cell.Offset(, 11).Value = "Invalid Policy Number"
GoTo fail
End If
'**************************************************************************
'Go to PBBC Screen to get Insured Name, address, and dates
PMS.SendKeys ("<Clear>PBBC " & sInput & "<Enter>")
Do Until PMS.getstring(3, 2, 3) = "UND"
PMS.waithostquiet (iWait * 10)
Loop
Name = PMS.getstring(5, 10, 29)
Cell.Offset(, 1).Value = Name
Adress1 = PMS.getstring(6, 10, 29)
Cell.Offset(, 2).Value = Adress1
Adress2 = PMS.getstring(6, 41, 29)
Cell.Offset(, 3).Value = Adress2
Zip = PMS.getstring(6, 74, 5)
Cell.Offset(, 4).Value = Zip
Effdate = PMS.getstring(9, 14, 6)
Cell.Offset(, 5).Value = Effdate
Expdate = PMS.getstring(9, 34, 6)
Cell.Offset(, 6).Value = Expdate
acode = PMS.getstring(8, 33, 7)
Cell.Offset(, 8).Value = acode
'Go to PBOI Screen to get Agent
PMS.SendKeys ("<Clear>PBOI " & sInput & "<Enter>")
Do Until PMS.getstring(3, 2, 3) = "LOB"
PMS.waithostquiet (iWait * 10)
Loop
Agent = PMS.getstring(3, 48, 30)
Cell.Offset(, 7).Value = Agent
'Go to PBPR Screen to get Premium
PMS.SendKeys ("<Clear>PBPR " & sInput & "<Enter>")
PMS.waithostquiet (iWait * 10)
If PMS.getstring(1, 7, 1) = "A" Then
Autoprem = PMS.getstring(2, 24, 10)
Cell.Offset(, 10).Value = Autoprem
GoTo Pass
End If
Homeprem = PMS.getstring(16, 39, 12)
Cell.Offset(, 9).Value = Homeprem
Pass:
'Add the word "Pass" in the color green to our results if the PUUC is successful
Cell.Offset(, 11).Value = "Pass"
Cell.Offset(, 11).Interior.ColorIndex = 4
GoTo NextPolicy
fail:
'Add the word "Fail" in the color red to our results if we cannot change tier
Cell.Offset(, 11).Value = "Fail"
Cell.Offset(, 11).Interior.ColorIndex = 3
fail = fail + 1
'Move on to the next policy
NextPolicy:
Cell.Offset(1, 0).Activate
Next Cell
Range(Cells(1, 1), Cells(finalRow, 5)).Borders.Weight = xlThin
ActiveSheet.Range("A2").Activate
ActiveWindow.FreezePanes = True
If fail > 0 Then
MsgBox "There were a total of " & fail & " policie(s) that failed during processing. Use the filter to identify these failures."
Else
MsgBox "Process 100% Successful."
End If
Exit Sub
As you can see it travels through different screens, collects the data and pastes it to excel.
Any help would be awesome.
Thank you,
Rob