PDA

View Full Version : Solved: row increment



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

mdmackillop
03-17-2006, 12:24 PM
Hi Vzachin,
Welcome to VBAX
If you use the Cells terminology to refer to your range, you can specify rows and columns by number making incremental changes very simple
Range("B4") is the same as Cells(4,2) or Cells(4,"B")

Applying this to your code, you get

Sub Extra()
Dim Rw As Long
'Set starting row variable
Rw = 4
Do
'Increment Rw
Rw = Rw + 1
'Get Data
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
With Worksheets(1)
.Cells(Rw, "B").Value = B5
.Cells(Rw, "C").Value = C5
.Cells(Rw, "D").Value = D5
End With
Sess0.Screen.SendKeys ("<PF6>") 'NEXT FILE IN EXTRA
Sess0.Screen.WaitHostQuiet (1000)
Loop While Sess0.Screen.GETstring(24, 2, 7) <> "TTS706I"
End Sub

smc2911
03-17-2006, 06:23 PM
Heres an alternative.

Sub Extra()
Dim dest As Range 'Set starting row variable
Set dest = Range("B5")
Do
'Get Data
dest.Cells(1,1) = Sess0.Screen.GETstring(9, 14, 8) 'DATA FROM EXTRA
dest.Cells(1,2) = Sess0.Screen.GETstring(10, 14, 2) 'DATA FROM EXTRA
dest.Cells(1,3) = Sess0.Screen.GETstring(11, 14, 5) 'DATA FROM EXTRA
' Deal with next record
Set dest = dest.Offset(1,0) ' Move dest down one row
Sess0.Screen.SendKeys ("<PF6>") 'NEXT FILE IN EXTRA
Sess0.Screen.WaitHostQuiet (1000)
Loop While Sess0.Screen.GETstring(24, 2, 7) <> "TTS706I"
End Sub
Note that I'm making use of the fact the dest.Cells(1,2) = <string> will implicitly force dest.Cells(1,2).Value = <string>, which is a shortcut that not everyone will like. Also the dest.Cells(1,1) could simply be dest.

Sean.

P.S. Can someone tell be how to get indents inside [vba]

mdmackillop
03-17-2006, 06:33 PM
Neat. :yes

vzachin
03-18-2006, 07:40 PM
both methods work great! you guys rock!!!
i will pose another question on a new post

thanks again
zach