vzachin
03-17-2006, 11:16 AM
i very new to vbe.
i am extracting records from a mainframe using attachmate extra and i need to put 3 lines of data per record into an excel sheet. the data will always be in the same position in extra.
the number of records can range from 1 to 5000, possibly more.
i know how to paste the 1st record into excel, but i cannot figure out how to paste subsequent records onto subsequent lines in excel using a do/loop while.
my first record is pasted into excel in cells B5,C5,D5
my next record should be pasted into cells B6,C5,D6
and so forth until i reach the end of my record search
my coding so far is:
Do
B5 = Sess0.Screen.GETstring(9, 14, 8) 'DATA FROM EXTRA
C5 = Sess0.Screen.GETstring(10, 14, 2) 'DATA FROM EXTRA
D5 = Sess0.Screen.GETstring(11, 14, 5) 'DATA FROM EXTRA
Worksheets(1).Range("B5").Value = B5
Worksheets(1).Range("C5").Value = C5
Worksheets(1).Range("D5").Value = D5
Sess0.Screen.SendKeys ("<PF6>") 'NEXT FILE IN EXTRA
Sess0.Screen.WaitHostQuiet (1000)
'B5 = Sess0.Screen.GETstring(9, 14, 8) this is how i currently do this
'C5 = Sess0.Screen.GETstring(10, 14, 2) by incrementing each line
'D5 = Sess0.Screen.GETstring(11, 14, 5) and so forth in vbe
'Worksheets(1).Range("B6").Value = B5 it's rather pathetic
'Worksheets(1).Range("C6").Value = C5
'Worksheets(1).Range("D6").Value = D5
'Sess0.Screen.SendKeys ("<PF6>")
'Sess0.Screen.WaitHostQuiet (1000)
'B5 = Sess0.Screen.GETstring(9, 14, 8)
'C5 = Sess0.Screen.GETstring(10, 14, 2)
'D5 = Sess0.Screen.GETstring(11, 14, 5)
'Worksheets(1).Range("B7").Value = B5
'Worksheets(1).Range("C7").Value = C5
'Worksheets(1).Range("D7").Value = D5
'Sess0.Screen.SendKeys ("<PF6>")
'Sess0.Screen.WaitHostQuiet (1000)
Loop While Sess0.Screen.GETstring(24, 2, 7) <> "TTS706I" 'this is the end of the file in extra
MsgBox "complete"
End Sub
thanks
zach
i am extracting records from a mainframe using attachmate extra and i need to put 3 lines of data per record into an excel sheet. the data will always be in the same position in extra.
the number of records can range from 1 to 5000, possibly more.
i know how to paste the 1st record into excel, but i cannot figure out how to paste subsequent records onto subsequent lines in excel using a do/loop while.
my first record is pasted into excel in cells B5,C5,D5
my next record should be pasted into cells B6,C5,D6
and so forth until i reach the end of my record search
my coding so far is:
Do
B5 = Sess0.Screen.GETstring(9, 14, 8) 'DATA FROM EXTRA
C5 = Sess0.Screen.GETstring(10, 14, 2) 'DATA FROM EXTRA
D5 = Sess0.Screen.GETstring(11, 14, 5) 'DATA FROM EXTRA
Worksheets(1).Range("B5").Value = B5
Worksheets(1).Range("C5").Value = C5
Worksheets(1).Range("D5").Value = D5
Sess0.Screen.SendKeys ("<PF6>") 'NEXT FILE IN EXTRA
Sess0.Screen.WaitHostQuiet (1000)
'B5 = Sess0.Screen.GETstring(9, 14, 8) this is how i currently do this
'C5 = Sess0.Screen.GETstring(10, 14, 2) by incrementing each line
'D5 = Sess0.Screen.GETstring(11, 14, 5) and so forth in vbe
'Worksheets(1).Range("B6").Value = B5 it's rather pathetic
'Worksheets(1).Range("C6").Value = C5
'Worksheets(1).Range("D6").Value = D5
'Sess0.Screen.SendKeys ("<PF6>")
'Sess0.Screen.WaitHostQuiet (1000)
'B5 = Sess0.Screen.GETstring(9, 14, 8)
'C5 = Sess0.Screen.GETstring(10, 14, 2)
'D5 = Sess0.Screen.GETstring(11, 14, 5)
'Worksheets(1).Range("B7").Value = B5
'Worksheets(1).Range("C7").Value = C5
'Worksheets(1).Range("D7").Value = D5
'Sess0.Screen.SendKeys ("<PF6>")
'Sess0.Screen.WaitHostQuiet (1000)
Loop While Sess0.Screen.GETstring(24, 2, 7) <> "TTS706I" 'this is the end of the file in extra
MsgBox "complete"
End Sub
thanks
zach