why doesn't this code work 1

[ptw78]

I get the error "Illegal Redifintion of Set" it points to the second line.

Sub Main

Dim Sessions, System As Object, Sess0 As Object, Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
file = "E:\MonthlyNumbers.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 5, column a,c,d,e
'where column a is date
'column c,d,e are the names
'----------------------------------
rw = 5
col = 3
cola= 6
with obj.worksheets("Ref-Rev")

for x = rw to obj.ActiveSheet.Rows.Count 'this will navigate
'column a with the dates

for y = col to 5 'this will navigate
'column c,d,e
MyDat = .cells(x,1)
MyNam = .cells(4,y) 'name are in row 4
If MyDat = "" Then Exit Sub
'msgbox MyDat
'msgbox MyNam

'-----send data to Attachmate-------

Sess0.Screen.PutString MyDat,4,25 'area data goes into
Sess0.Screen.PutString MyNam,5,13 'in citilink
Sess0.Screen.Sendkeys("<enter>")
'-----grab data from Attachmate-----
ExtraDat = Sess0.Screen.GetString (11,55,2) 'area getting data from


'-----and place data in Excel-------

cola = cola + 1
.cells(x,cola) = ExtraDat 'this places the information in the same sheet
'column g,h,i
'----------------------------

next y 'next column
cola=6 'this brings the data back to column g,h,i
'for data input
next x 'next row

end with

End Sub

 

[vzachin]

hi ptw78

in your previous post on 15 May 09 14:05 ,
you indicated that

all this code gives me pop box that says 20090303 and then another that says ok.

at the very least, then "20090303" & "ok" will be placed into the extra screen coordinates that you indicated at 4,25 & 5,13 .

sill question: are you stepping through the code ?

 

[ptw78]

correct, it doesn't do anything in extra, it does open up the excel sheet, but does not put an data in extra or put anything in the excel sheet, and then the pop up box comes up and it's done. All this takes about 1 second.

 

[ptw78]

i step through the code and nothing happens, it basically goes through the process once and then i get the popup, it opens up the excel file as mentioned, nothing happens in extra, then the pop up "ok"box

 

[ptw78]

Ok, let me start over on this because maybe something is missing. basically i on the excel sheet i have 8 columns, A-H. 34 rows. col A is a date going down the col. starting at row 3 and going down to row 33, row 34 is a total row. all this in col A. col B is empty. cols C-H have the name of a person in row 1, and id in row2, so col C, row one have John Doe, C row 2 has 9876, col D, row 1 has john smith, D, row two has 1234, and so on until we get to col H. the starting field in extra will be 4/25, which is where it will put the date from Col A, row 3 to start. Then move to 5/13 where i will the id starting in col C, row 2. Once that has been entered, "enter" will need to be done. Then move to field 11/50 in extra and get data in that field. The data will need to be placed in col C, row 3, which would be the same row the date was taken in col A. This would continue to repeat until it got to row 33, which then it would move on to col D, row 2 which would be another person and the id number would be in row 2 on .xls file. it start back over at col A, row 3 date, put the date back in extra field 4/25, move to 5/13 put the id in from col D, row 2, "enter" get the data from 11/50 in extra, put that data in the .xls file starting in col D, row 3, and move down to row 33, then move to col E, and so on until col H, row 33 is done.

 

[vzachin]

can you confirm that the data in the msgbox is the data from excel?
and can you confirm that your coordinates are correct ?

add

Sess0.Screen.PutString "DATAHERE",4,25

before this line

Sess0.Screen.PutString MyDat,4,25

and what do you get?

how are you stepping through the code?

 

[ptw78]

it doesn't seem to do anything after the message box code, it jumps straight down to End Sub

 

[vzachin]

it jumps straight down to End Sub

try this:
rw-write your code into a new module and try it again from scratch...
rem out parts of the code and step through it line by line...

there's something in your code that's causing agita

 

[ptw78]

tried to re-write it, same thing. I'm not sure what's up w/this but here is the code again

Sub Main
    
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "E:\Macros\MthlyNumbers.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 5, column a,c,d,e
        'where column a is date
        'column c,d,e are the names
        '----------------------------------
rw = 5      
col = 3   
cola= 6            
        with obj.worksheets("Ref-Rev")
        
        for x = rw to obj.ActiveSheet.Rows.Count    'this will navigate
                                                    'column a with the dates
                                                    
        for y = col to 5                            'this will navigate
                                                    'column c,d,e
        MyDat = .cells(x,1)
        MyNam = .cells(4,y)                     'name are in row 4
        If MyDat = "" Then Exit Sub
        'msgbox MyDat
        'msgbox MyNam
        
        '-----send data to Attachmate-------
        
        Sess0.Screen.PutString MyDat,4,25        'area data goes into
        Sess0.Screen.PutString MyNam,5,13       'in citilink
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(3, 3)
                
        '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (11,55,3) 'area getting data from
                                                    
        
        '-----and place data in Excel-------
  
cola = cola + 1  
        .cells(x,cola) = ExtraDat  'this places the information in the same sheet
                                   'column g,h,i
        '----------------------------
        
        next y  'next column
cola=6         'this brings the data back to column g,h,i
               'for data input
        next x  'next row
        
        end with

End Sub

 

[ptw78]

the code keeps looping through this statement

Loop Until Sess0.Screen.WaitForCursor(3, 3)[\code] all it does prior to that is open up the .xls file.

 

[vzachin]

Do
DoEvents
Loop Until Sess0.Screen.WaitForCursor(3, 3)

does your cursor ever get to coordinates (3,3)?

 

[ptw78]

no, i'm assuming (3,3) is the beginning coordinates of the excel file. But it does not get there and put any data in.

 

[vzachin]

wait for cursor(3,3) is "waiting for cursor" in extra, so if the cursor isn't there, it will loop forever...

change the coordinates to where you expect the cursor...

 

[SkipVought]

You do not wait or a cursor in and Excel sheet.

You wait for a cursor on an emulator screen.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ptw78]

ok, i think i may be getting somewhere, i put in 4,25 for
wait for cursor, which is also the starting point of where my screen is on this line

Sess0.Screen.PutString MyDat,4,25        'area data goes into

, now it gets data, but starts on Col G, row 5, and it repeats/puts in the same data in Cols H & I, both starting row 5, so it basically duplicates the data and i see it in 3 Cols. The data being put in to extra from excel is diff on Cols C-H, starting at row 3 and ending at 33.

 

[ptw78]

K, made a few adjustments, I get the data, but I have two issues. One it doesn't line up w/the correct Col. C, it puts data in Col G and goes through L, it should be putting it in C and go through H. 2nd issue, what should be Col. C and E, or 1st and 3rd Cols of data are getting no data, it just puts 0's in all the way down. And there should be data. Here is my updated 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 = "E:\Macros\MthlyNumbers.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 3, column a,c,d,e
        'where column a is date
        'column c,d,e are the names
        '----------------------------------
rw = 3      
col = 3   
cola= 6            
        with obj.worksheets("Ref-Rev")
        
        for x = rw to obj.ActiveSheet.Rows.Count    'this will navigate
                                                    'column a with the dates
                                                    
        for y = col to 8                            'this will navigate
                                                    'column c,d,e
        MyDat = .cells(x,1)
        MyNam = .cells(2,y)                     'name are in row 2
        If MyDat = "" Then Exit Sub
        'msgbox MyDat
        'msgbox MyNam
        
        '-----send data to Attachmate-------
        
        Sess0.Screen.PutString MyDat,4,25        'area data goes into
        Sess0.Screen.PutString MyNam,5,13       'in citilink
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(4, 25)
                
        '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (11,55,3) 'area getting data from
                                                    
        
        '-----and place data in Excel-------
  
cola = cola + 1  
        .cells(x,cola) = ExtraDat  'this places the information in the same sheet
                                   'column g,h,i
        '----------------------------
        
        next y  'next column
cola=6         'this brings the data back to column g,h,i
               'for data input
        next x  'next row
        
        end with

End Sub

 

[ptw78]

i got the cols/row to all line up, now i just need the data to show up in Cols C & E, updated code below.

Sub Main
    
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "E:\Macros\MthlyNumbers.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 3, column a,c,d,e
        'where column a is date
        'column c,d,e are the names
        '----------------------------------
rw = 3      
col = 3   
cola= 2            
        with obj.worksheets("Ref-Rev")
        
        for x = rw to obj.ActiveSheet.Rows.Count    'this will navigate
                                                    'column a with the dates
                                                    
        for y = col to 8                            'this will navigate
                                                    'column c,d,e
        MyDat = .cells(x,1)
        MyNam = .cells(2,y)                     'name are in row 2
        If MyDat = "" Then Exit Sub
        'msgbox MyDat
        'msgbox MyNam
        
        '-----send data to Attachmate-------
        
        Sess0.Screen.PutString MyDat,4,25        'area data goes into
        Sess0.Screen.PutString MyNam,5,13       'in citilink
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(4, 25)
                
        '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (11,55,3) 'area getting data from
                                                    
        
        '-----and place data in Excel-------
  
cola = cola + 1  
        .cells(x,cola) = ExtraDat  'this places the information in the same sheet
                                   'column g,h,i
        '----------------------------
        
        next y  'next column
cola=2         'this brings the data back to column c,d,e
               'for data input
        next x  'next row
        
        end with

End Sub
[\code]

 

[ptw78]

also is there a way to get this to end at row 33, reason being i'm going to put a formula in row 34 to total everything up. thx

 

[ptw78]

not sure if this is why Col C & E don't show any data, but the data from excel that is put into extra on the other Cols is 5 digits, Cols C & E are only 4 digits, i changed excel to format 5 digits for all Cols which gave the data in Col C & E, row two a leading zero and it still doesn't work.

 

[ptw78]

the leading zero isn't working it's not taking it from excel, what i can see happening is the 4 digit data is put into extra and what ever is left over from the previous data is left in the spot in extra

 

[vzachin]

also is there a way to get this to end at row 33, reason being i'm going to put a formula in row 34 to total everything up. thx

do this:

where you have
rw = 3

add the following:
endrw = 33

then change this

for x = rw to obj.ActiveSheet.Rows.Count

for x = rw to endrw

not sure if this is why Col C & E don't show any data, but the data from excel that is put into extra on the other Cols is 5 digits, Cols C & E are only 4 digits, i changed excel to format 5 digits for all Cols which gave the data in Col C & E, row two a leading zero and it still doesn't work.

question: are you sure you are grabbing any data from your emulator over here:

ExtraDat = Sess0.Screen.GetString (11,55,3) 'area getting data from

check to see if there is any data...

the leading zero isn't working it's not taking it from excel, what i can see happening is the 4 digit data is put into extra and what ever is left over from the previous data is left in the spot in extra

not sure what you mean...but let's see if this makes any sense to you:

let's say in Excel cell "a1" value is "ABCDE" and cell "a2" value is "XYZ".

if you first place "ABCDE" into EXTRA and then place "XYZ" the end result will be "ABCYZ".
however if you place " " (5 spaces) before placing "XYZ", then the end result will be "XYZ"

walk through your code using a blank emulator screen and fill in "data" when & where you expect it, add a msgbox to see if the code is grabbing the data...
hth