Help with Loop Extra to Excel
Help with Loop Extra to Excel
(OP)
Hi, I'm trying to pull numbers from an open Excel sheet (col A), Paste into an Extra (open session on correct screen), pull info back to Excel (col B), then loop to next row in column A. The first one works...but then it doesn't loop to next. Nothing is entered in Extra for next row. Here is my enter code...which I copied from here:
Sub ExcelExtraExcel()
Dim Excel As Object, ExcelWorkbook As Object
Set Excel = GetObject(, "Excel.Application")
Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set MyScreen = Sess.Screen
With Excel.Worksheets("Sheet1")
Row = 1
Do
Pull = .Cells(Row, "A").Value '**This will pull information from Excel
MyScreen.Putstring Pull, X, X '**This will put information from Excel into Extra, use your own coordinates
MyScreen.SendKeys ("<Enter>")
MyScreen.WaitHostQuiet 100
NewPull = MyScreen.Getstring(X, X, 20) '**This will pull information from Extra, use your own coordinates
.Cells(Row, "B").Value = NewPull '**This will place information from Extra to Excel
Row = Row + 1 '**This will progress the code to the next line
Loop Until .Cells(Row, "A").Value <> "" '** This will loop your code until there is a blank value in Column A in Excel
End With
End Sub
Sub ExcelExtraExcel()
Dim Excel As Object, ExcelWorkbook As Object
Set Excel = GetObject(, "Excel.Application")
Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set MyScreen = Sess.Screen
With Excel.Worksheets("Sheet1")
Row = 1
Do
Pull = .Cells(Row, "A").Value '**This will pull information from Excel
MyScreen.Putstring Pull, X, X '**This will put information from Excel into Extra, use your own coordinates
MyScreen.SendKeys ("<Enter>")
MyScreen.WaitHostQuiet 100
NewPull = MyScreen.Getstring(X, X, 20) '**This will pull information from Extra, use your own coordinates
.Cells(Row, "B").Value = NewPull '**This will place information from Extra to Excel
Row = Row + 1 '**This will progress the code to the next line
Loop Until .Cells(Row, "A").Value <> "" '** This will loop your code until there is a blank value in Column A in Excel
End With
End Sub
RE: Help with Loop Extra to Excel
Hi,
Since your are working between Excel and your emulator, I would not waste my time using Extra Basic. It is much less robust than Excel VBA and the debugging features are better in Excel VBA as well.
Not knowing what your X value is, I can't provide much specific help. If you were coding in Excel VBA, I'd tell you to use the Watch Window to observe the values in each statement.
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Help with Loop Extra to Excel
Also, here's the technique that I've been using to wait for the cursor response, rather than a specified period of time which may be longer than necessary or too short, if the system is busy...
CODE
.SendKeys ("<enter>")
Do Until (.WaitForCursor(3, 11))
DoEvents
Loop
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Help with Loop Extra to Excel
just in case nobody told you, there is a bug in the WaitHostQuiet function in Extra that will essentially return the function before a new screen has been displayed and 'settled'. that's why we keeping bringing it up :)
RE: Help with Loop Extra to Excel
RE: Help with Loop Extra to Excel
Sub ExcelExtraExcel()
Dim Excel As Object, ExcelWorkbook As Object
Set Excel = GetObject(, "Excel.Application")
Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set MyScreen = Sess.Screen
With Excel.Worksheets("Sheet1")
Row = 1
Do
Pull = .Cells(Row, "A").Value '**This will pull information from Excel
MyScreen.Putstring Pull, 3, 2 '**This will put information from Excel into Extra, use your own coordinates
.MoveRelative 1, 1, 1
.SendKeys ("<enter>")
Do Until (.WaitForCursor(3, 11))
DoEvents
Loop
NewPull = MyScreen.Getstring(9, 30, 20) '**This will pull information from Extra, use your own coordinates
.Cells(Row, "B").Value = NewPull '**This will place information from Extra to Excel
Row = Row + 1 '**This will progress the code to the next line
Loop Until .Cells(Row, "A").Value <> "" '** This will loop your code until there is a blank value in Column A in Excel
End With
RE: Help with Loop Extra to Excel
"Still no luck."
Exactly WHAT does that mean? You must be specific about what is happening, and what you expect.
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Help with Loop Extra to Excel
CODE
'since "I'm using Excel for this," you do not need to set the Excel application object.
Dim Pull as string, NewPull as string, lRow as long, System as Object, Sess as object, MyScreen as object
Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set MyScreen = Sess.Screen
With Worksheets("Sheet1")
lRow = 1
Do
Pull = .Cells(lRow, "A").Value '**This will pull information from Excel
MyScreen.Putstring Pull, 3, 2 '**This will put information from Excel into Extra, use your own coordinates
MyScreen.MoveRelative 1, 1, 1
MyScreen.SendKeys ("<enter>")
Do Until (MyScreen.WaitForCursor(3, 11))
DoEvents
Loop
NewPull = MyScreen.Getstring(9, 30, 20) '**This will pull information from Extra, use your own coordinates
.Cells(lRow, "B").Value = NewPull '**This will place information from Extra to Excel
lRow = lRow + 1 '**This will progress the code to the next line
Loop Until .Cells(lRow, "A").Value <> "" '** This will loop your code until there is a blank value in Column A in Excel
End With
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Help with Loop Extra to Excel
RE: Help with Loop Extra to Excel
Did you STEP thru your code to see what is happeneing with your objects & variables?
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Help with Loop Extra to Excel
FAQ707-4594: How to use the Watch Window as a Power Programming Tool
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Help with Loop Extra to Excel
Do Until (MyScreen.WaitForCursor(3, 11))
maybe shoud be
Do Until (MyScreen.WaitForCursor(3, 2))