Macro for putting data in Extra and pulling string out
Macro for putting data in Extra and pulling string out
(OP)
I'm trying to create a macro that puts a number into Extra at location 3,17 , enter is hit, the screen is displayed data. It then moves to location 5,13, pulls that field which is 10 characters long. And puts that field into excel in Column B, starting at Row 2 and going down. The original number that goes into Extra at location 3/17, is from the same excel sheet in Column A starting at row 2 and going down. Here is what I have.
CODE
Sub Main
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
file = "H:\Macros - Reports\Tests\Numbers.xls"
Dim obj as object
Dim objWorkbook as object
Set obj = CreateObject("Excel.Application")
obj.visible = True
obj.workbooks.open file
'---------------------------------
'assumption
'data begins in row 2
'----------------------------------
begrw = 2
col = 2
with obj.worksheets("Sheet1")
for x = begrw to endrw 'obj.ActiveSheet.Rows.Count
MyDat = .cells(x,1)
If MyDat = "" Then Exit Sub
'-----send data to Extra-------
Sess0.Screen.PutString MyDat,3,17 'area data goes into in extra
Sess0.Screen.Sendkeys("<enter>")
Sess0.Screen.MoveRelative 1, 1
'wait for response
Do
DoEvents
Loop Until Sess0.Screen.WaitForCursor(3,17)
'-----grab data from Attachmate-----
ExtraDat = Sess0.Screen.GetString (5,13,10) 'area getting data from
'-----and place data in Excel-------
.cells(x,col) = ExtraDat 'this places the information in the sheet
'----------------------------
next x
msgbox "Macro Done"
end with
End Sub
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
file = "H:\Macros - Reports\Tests\Numbers.xls"
Dim obj as object
Dim objWorkbook as object
Set obj = CreateObject("Excel.Application")
obj.visible = True
obj.workbooks.open file
'---------------------------------
'assumption
'data begins in row 2
'----------------------------------
begrw = 2
col = 2
with obj.worksheets("Sheet1")
for x = begrw to endrw 'obj.ActiveSheet.Rows.Count
MyDat = .cells(x,1)
If MyDat = "" Then Exit Sub
'-----send data to Extra-------
Sess0.Screen.PutString MyDat,3,17 'area data goes into in extra
Sess0.Screen.Sendkeys("<enter>")
Sess0.Screen.MoveRelative 1, 1
'wait for response
Do
DoEvents
Loop Until Sess0.Screen.WaitForCursor(3,17)
'-----grab data from Attachmate-----
ExtraDat = Sess0.Screen.GetString (5,13,10) 'area getting data from
'-----and place data in Excel-------
.cells(x,col) = ExtraDat 'this places the information in the sheet
'----------------------------
next x
msgbox "Macro Done"
end with
End Sub
RE: Macro for putting data in Extra and pulling string out
RE: Macro for putting data in Extra and pulling string out
Hi,
So what's the problem?
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
RE: Macro for putting data in Extra and pulling string out
CODE
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
file = "H:\Macros - Reports\Tests\InvestorLoanNum.xls"
Dim obj as object
Dim objWorkbook as object
Set obj = CreateObject("Excel.Application")
obj.visible = True
obj.workbooks.open file
'---------------------------------
'data begins in row 2
'----------------------------------
begrw = 2
col = 1
colb = 2
with obj.worksheets("Sheet1")
for x = begrw to obj.ActiveSheet.Rows.Count
MyDat = .cells(x,2)
If MyDat = "" Then Exit Sub
'-----send data to Extra-------
Sess0.Screen.PutString MyDat,3,17 'area data goes into in Extra
Sess0.Screen.Sendkeys("<enter>")
Sess0.Screen.MoveRelative 1, 1
'wait for response
Do
DoEvents
Loop Until Sess0.Screen.WaitForCursor(3,17)
'-----grab data from Attachmate-----
ExtraDat = Sess0.Screen.GetString (5,13,10) 'area getting data from
'-----and place data in Excel-------
.cells(x,colb) = ExtraDat 'this places the information in the sheet
'----------------------------
next x
msgbox "Macro Done"
end with
End Sub
RE: Macro for putting data in Extra and pulling string out
Debug & step to determine what value is ExtraDat.
And try this...
CODE
Set objWorkbook = obj.workbooks.open(file)
'---------------------------------
'assumption
'data begins in row 2
'----------------------------------
begrw = 2
col = 2
with objWorkbook.worksheets("Sheet1")
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
It goes straight from
CODE
CODE
RE: Macro for putting data in Extra and pulling string out
don't you want this?
CODE
21)Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
RE: Macro for putting data in Extra and pulling string out
There you go!
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
CODE
RE: Macro for putting data in Extra and pulling string out
Area Size?
CODE
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
RE: Macro for putting data in Extra and pulling string out
I don't make it a practice to SELECT in anything.
I reference objects using properties & methods.
There are many ways to skin a cat, however. Whatever works for you.
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
RE: Macro for putting data in Extra and pulling string out
Any reason why the message box doesn't pop up?
If MyDat = "" Then Exit Sub
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out
You might want to use Exit For.
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Macro for putting data in Extra and pulling string out