macro working when do Step by Step & not working when run it at one shot
macro working when do Step by Step & not working when run it at one shot
(OP)
Hi,
I have a macro built in a way that copies data from excel and pastes in attachmate screen and then when press Enter it populates some data and the same data is taken back to excel.
this is working fine when i was testing that is doing a step by step ( Pressing F8) everything is fine, but when i run at once, some of the lines are not copying from excel correctly, or some values says incorrect even if they are right.
when I did step by step its 100% correct
can someone plz help me why do we get these kind problem? not sure if that is because of system time
' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$
Declare Function QuitExcel
Dim objExcel as Object,xlFS as Object
Dim fs As Object
Dim TPNB as String
Dim Rw as Integer
Dim RVal as Currency
Dim Cid as Variant,Cim as Variant, Ciy as Variant
Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions
If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 3000 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If
System.TimeoutValue = 3000 ' milliseconds
' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
'--------------------------------------------------------------------------------
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
' Declare variables to contain the OLE objects
Dim objExcel As Object
Dim objWorkBook As Object
Dim gs as String
Dim r as Integer
Dim j as Integer
Dim Depot as string
Dim Er as string
r=2
On Error Resume Next
' Attempt to get a reference to an open instance of Excel
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
'If GetObject failed, open a new instance of Excel
Set objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If
' Make Excel visible on the screen
objExcel.Visible = True
' Create a new Workbook
Set objWorkBook = objExcel.Workbooks.Open("P:\Excel Worksheets (Macro)\POCR.xls")
Sess0.Screen.Sendkeys("2")
Sess0.Screen.Sendkeys("pocr")
Depot = objWorkBook.WorkSheets("Sheet1").Cells(2,7).Value 'Gets Depot Number
Sess0.Screen.PutString depot,03, 013 'Enters depot number
Sess0.Screen.Sendkeys("")
Supplier = objWorkBook.WorkSheets("Sheet1").Cells(4,7).Value 'Gets Supplier Number
Sess0.Screen.PutString supplier,03, 035 'Enters supplier number
Sess0.Screen.Sendkeys("")
Dat = Format(objWorkBook.WorkSheets("Sheet1").Cells(6,7).Value,"DD/MM/YYYY") 'Gets Date Number
' Date = Today()+2
Sess0.Screen.PutString dat ,05, 013 'Enters Required Date
Sess0.Screen.Sendkeys("")
Do
Set Sess0 = System.ActiveSession
for r1= 8 to 25
TPND= Format(objWorkBook.WorkSheets("Sheet1").Cells(r,2).Value,"000000000") 'Gets TPND
Cse = objWorkBook.WorkSheets("Sheet1").Cells(r,3).Value 'Gets case
Set Sess0 = System.ActiveSession
Sess0.Screen.PutString TPND, r1, 011 'Enters TPND
Sess0.Screen.PutString Cse, r1, 056 'Enters Case
'Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Sendkeys("")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Er = Trim(Sess0.Screen.GetString(27,2,100))
PF = LEFT (Er,4)
If PF <> "" then
Sess0.Screen.Sendkeys("")
elseif PF <> "PF12" then
objWorkBook.WorkSheets("Sheet1").Cells(r,4).Value = Er
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
'Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.MoveTo r1,11
Sess0.Screen.Sendkeys("")
Sess0.Screen.Sendkeys("")
Sess0.Screen.MoveTo r1,56
Sess0.Screen.Sendkeys("")
Sess0.Screen.Sendkeys("")
Sess0.Screen.Sendkeys("")
else
End If
r=r+1
next r1
Loop Until objWorkBook.WorkSheets("Sheet1").Cells(r,2).Value = "End"
' Excel will remain open after this Sub ends.
' To close out Excel, unremark the following 4 lines of code. .
'objWorkBook.Close
'objExcel.Quit
'set objWorkBook = Nothing
'set objExcel = Nothing
End sub
'-----------------------------------------------------------------------------------------------------------------------
Function QuitExcel
objExcel.ActiveWorkbook.Close(True)
objExcel.Quit
Set objExcel = Nothing
Set xlFS = Nothing
Set fs = Nothing
End Function
I have a macro built in a way that copies data from excel and pastes in attachmate screen and then when press Enter it populates some data and the same data is taken back to excel.
this is working fine when i was testing that is doing a step by step ( Pressing F8) everything is fine, but when i run at once, some of the lines are not copying from excel correctly, or some values says incorrect even if they are right.
when I did step by step its 100% correct
can someone plz help me why do we get these kind problem? not sure if that is because of system time
' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$
Declare Function QuitExcel
Dim objExcel as Object,xlFS as Object
Dim fs As Object
Dim TPNB as String
Dim Rw as Integer
Dim RVal as Currency
Dim Cid as Variant,Cim as Variant, Ciy as Variant
Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions
If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 3000 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If
System.TimeoutValue = 3000 ' milliseconds
' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
'--------------------------------------------------------------------------------
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
' Declare variables to contain the OLE objects
Dim objExcel As Object
Dim objWorkBook As Object
Dim gs as String
Dim r as Integer
Dim j as Integer
Dim Depot as string
Dim Er as string
r=2
On Error Resume Next
' Attempt to get a reference to an open instance of Excel
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
'If GetObject failed, open a new instance of Excel
Set objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If
' Make Excel visible on the screen
objExcel.Visible = True
' Create a new Workbook
Set objWorkBook = objExcel.Workbooks.Open("P:\Excel Worksheets (Macro)\POCR.xls")
Sess0.Screen.Sendkeys("2
Sess0.Screen.Sendkeys("pocr
Depot = objWorkBook.WorkSheets("Sheet1").Cells(2,7).Value 'Gets Depot Number
Sess0.Screen.PutString depot,03, 013 'Enters depot number
Sess0.Screen.Sendkeys("
Supplier = objWorkBook.WorkSheets("Sheet1").Cells(4,7).Value 'Gets Supplier Number
Sess0.Screen.PutString supplier,03, 035 'Enters supplier number
Sess0.Screen.Sendkeys("
Dat = Format(objWorkBook.WorkSheets("Sheet1").Cells(6,7).Value,"DD/MM/YYYY") 'Gets Date Number
' Date = Today()+2
Sess0.Screen.PutString dat ,05, 013 'Enters Required Date
Sess0.Screen.Sendkeys("
Do
Set Sess0 = System.ActiveSession
for r1= 8 to 25
TPND= Format(objWorkBook.WorkSheets("Sheet1").Cells(r,2).Value,"000000000") 'Gets TPND
Cse = objWorkBook.WorkSheets("Sheet1").Cells(r,3).Value 'Gets case
Set Sess0 = System.ActiveSession
Sess0.Screen.PutString TPND, r1, 011 'Enters TPND
Sess0.Screen.PutString Cse, r1, 056 'Enters Case
'Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Sendkeys("
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Er = Trim(Sess0.Screen.GetString(27,2,100))
PF = LEFT (Er,4)
If PF <> "" then
Sess0.Screen.Sendkeys("
elseif PF <> "PF12" then
objWorkBook.WorkSheets("Sheet1").Cells(r,4).Value = Er
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
'Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.MoveTo r1,11
Sess0.Screen.Sendkeys("
Sess0.Screen.Sendkeys("
Sess0.Screen.MoveTo r1,56
Sess0.Screen.Sendkeys("
Sess0.Screen.Sendkeys("
Sess0.Screen.Sendkeys("
else
End If
r=r+1
next r1
Loop Until objWorkBook.WorkSheets("Sheet1").Cells(r,2).Value = "End"
' Excel will remain open after this Sub ends.
' To close out Excel, unremark the following 4 lines of code. .
'objWorkBook.Close
'objExcel.Quit
'set objWorkBook = Nothing
'set objExcel = Nothing
End sub
'-----------------------------------------------------------------------------------------------------------------------
Function QuitExcel
objExcel.ActiveWorkbook.Close(True)
objExcel.Quit
Set objExcel = Nothing
Set xlFS = Nothing
Set fs = Nothing
End Function
Talk To Other Members
RE: macro working when do Step by Step & not working when run it at one shot
Your Attachmate terminal emulator is your I/O interface to your mainframe system.
When you SendKeys to your mainframe, its like mailing a letter: you have no control over when you'll receive a reply. So you have to wait until the reply is received before doing anything else related to the contents of the letter.
Likewise with your code. You SendKeys and the reply could be virtually immediately, or NOT. Your code has to WAIT for the reply. As you STEP thru the code manually, you have built in a delay between each STEP. When you simply RUN, there is no delay, so your code blunders on without waiting for a reply.
I like to have a loop after each SendKeys. You have an odd system where you send a zero length string ("")???
CODE
RE: macro working when do Step by Step & not working when run it at one shot
Thank you so much for the explanation.
the Send keys with "" strinng is by mistake, when i copy and pasted my code it ignored all the strings after Send keys.
like this
Sess0.Screen.Sendkeys("2<Enter>")
Sess0.Screen.Sendkeys("pocr<Enter>")
it has taken as
Sess0.Screen.Sendkeys("2")
Sess0.Screen.Sendkeys("pocr")
with line as an exampple, can you tell me how do i use the loop that you have mentioned?
thanks for your help:)
Regards
Arvind
RE: macro working when do Step by Step & not working when run it at one shot
The loop simply cycles until the cursor appears at the coordinates you have provided. If the coordinates are incorrect, it will loop indefinitely. You should such a loop after each SendKeys.