sorry so dumb - I got so excited when Skip helped me fix the last macro, that I thought I could expand on it on my own. HAHA! big mistake.
the intent is to collect 2 cells from Excel, send to extra, enter and wait for an update screen, then paste another cell and enter. this should be able to loop thru the spreadsheet until it finds a blank cell (right now limited to 20 rows). columns won't change, and rows start at 4 and end at 25.
this code sorta works, but I can't get the loop language right. I can get it to work fine for the first row, but that's it.
I've read several postings on looping from excel to extra, and I just don't understand enough to make it work for me. I've read the Help files, but they seem to require some existing knowledge that I do not possess.
I think if someone can help me figure out the loop thing I can get this working. could someone please help me in the right direction? TIA!!
the intent is to collect 2 cells from Excel, send to extra, enter and wait for an update screen, then paste another cell and enter. this should be able to loop thru the spreadsheet until it finds a blank cell (right now limited to 20 rows). columns won't change, and rows start at 4 and end at 25.
this code sorta works, but I can't get the loop language right. I can get it to work fine for the first row, but that's it.
I've read several postings on looping from excel to extra, and I just don't understand enough to make it work for me. I've read the Help files, but they seem to require some existing knowledge that I do not possess.
I think if someone can help me figure out the loop thing I can get this working. could someone please help me in the right direction? TIA!!
Code:
' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$
'
'
' update narrative
'
'
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 = 100 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If
' 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
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'
'-------------------------------------------------------------------------------- starts here
'
Dim MyScreen As Object
Set myscreen = Sess0.Screen
'
' -------------------------------------------fetch and check for logged-on SMUP screen
'
'
Sess0.Screen.Sendkeys("<Clear>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Do While Sess0.Screen.OIA.XStatus <> 0 : Loop
Sess0.Screen.MoveTo 1, 1
Sess0.Screen.Sendkeys("SMUP<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
v1$ = MyScreen.GetString(1, 2, 4)
if v1$ <> "SMUP" then
'no -> error!!!
msgbox "oops! Log onto SMUP first."
exit sub
end if
'---------------------------------------------------------get info from excel file
'Declare the Excel Object
Dim obj as object
Dim xlApp As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
Set obj = Getobject("G:\Property Transfers\Programs\PT macros\MH\narr.xls")
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
Set xlSheet = obj.Worksheets("Sheet1")
xlApp.Visible = True
'-------------------------------------------------------------------------------- seems to go south here
With xlApp.ActiveSheet
Dim APN
Dim SessSM as object
Set myscreen = Sess0.Screen
'--------------------------------------------------------- get the new info
Set APN = xlsheet.Range("G4") 'get APN
Set yr = xlsheet.Range("A4") 'get year
Set nar = xlsheet.Range("H4") 'get narr
usr = "JUD"
'---------------------------------------------------------call up the record to change
Sess0.Screen.putstring(APN) 1, 7 'send APN
Sess0.Screen.putstring(yr) 2,24 'send roll year
Sess0.Screen.Sendkeys("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End With
Sess0.Screen.WaitForString "SMUP",1,2,4
'<---------------------------------------------------send update codes & initials
'
Sess0.Screen.putstring("u") 2, 65
Sess0.Screen.putstring(usr) 4, 52 '<------------------initials
Sess0.Screen.Sendkeys("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.WaitForString "ENTER",12,24
Sess0.Screen.putstring("adr") 2, 74 '------------------send reason code
'<--------------------------------------------------- blank field text and send new text to smup screen
Sess0.Screen.MoveTo 8,14
Sess0.Screen.Sendkeys("<EraseEOF>")
Sess0.Screen.putstring(L1) 5, 14
Sess0.Screen.putstring("*") 8, 74
'<--------------------------------------------------- enter changes
'
Sess0.Screen.Sendkeys("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.WaitForString "UPD", 24, 30
'<-------------------------------------------------- need loop to next XL line here
Set MyRange = xlSheet.Range("A:A")
dim Row As Long, rw as long
rw=5
do
Set MyRange = xlsheet.Range(xlsheet.[g4:h25])
For Rows = 4 To 25
if MyRange.Rows(Rows).Value = "" Then Exit Sub
Sess0.Screen.PutString MyRange.Rows(Rows).Value, rw, 18
if rw = 22 then
rw = 5
else
rw = rw + 1
end if
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Rows
loop
'
'<--------------------------------------------------- End Program
'
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
'
sess0.Screen.WaitHostQuiet(qt)
System.TimeoutValue = OldSystemTimeout
exit sub
end sub