Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

why doesn't this code work 1

Status
Not open for further replies.
ptw78

ptw78

Technical User
Mar 5, 2009
155
US
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
 
Sort by date Sort by votes
this should work for you
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
        '----------------------------------
[blue]begrw = 3  
endrw = 33[/blue]    
col = 3   
cola= 2            
        with obj.worksheets("Ref-Rev")
        
        for x = [blue]begrw to endrw[/blue]    '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
	[blue]Sess0.Screen.PutString "     ",5,13[/blue]  'this places 5 blank spaces. i don't know the length of your field here. change to suit
        '----------------------------
        
        next y  'next column
cola=2         'this brings the data back to column c,d,e
               'for data input

	[blue]Sess0.Screen.PutString "     ",4,25[/blue]	'this places 5 blank spaces.i don't know the length of your field here. change to suit


        next x  'next row
        
        end with

End Sub
 
Upvote 0 Downvote
ok ,the end of row info helped, and worked. the field issue wont work because the field itself where i have the issues is only 5 spaces big, so what I did was had it hit the delete key once which takes away the last digit being left in from the previous data. That works, although being able to just clear out the entire row before it puts in new data would prob be better. Also moving on to what I want to do next, is there a way to basically do the same thing and put it in a different sheet on the same .xls file? Basically let the macro run w/the existing code on the first sheet, and run again except the data being input from excel will be from the 2nd sheet and the data received back from extra will go into the second sheet? thx
 
Upvote 0 Downvote
I have the spacing issue taken care of with <EraseEOF>, as far as adding into another sheet, i haven't tried anything because I know very little about it.
 
Upvote 0 Downvote
Basically let the macro run w/the existing code on the first sheet, and run again except the data being input from excel will be from the 2nd sheet and the data received back from extra will go into the second sheet?
Click to expand...

over here:
Code: 
cola = cola + 1  
        .cells(x,cola) = ExtraDat  'this places the information in the same sheet
would be:
Code: 
with obj.worksheets("2ndsheet")
cola = cola + 1  
        .cells(x,cola) = ExtraDat  'this places the information in the 2nd sheet
end with

this will work as well:
Code: 
obj.worksheets("2ndsheet").cells(x,cola) = ExtraDat

i haven't tried anything because I know very little about it.
Click to expand...
you really need to experiment with code otherwise you will never learn...trial and error is the best teacher[smile]
 
Upvote 0 Downvote
ok, thx, question related so something simlar.
how would i get this to work with a for loop, basically i just want to take some data from excel in the first col, put it in extra, get a piece of data and put it back in the excel file in the 2nd col. this 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 = "E:\Macros\test.xls"
        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file
        '---------------------------------
        
row = 1
col = 1
cola = 2

   with obj.worksheets("Sheet1") 
   
    for x = rw to obj.ActiveSheet.Rows.Count   
    
    Dat = .cells(x,1)
    If Dat = "" Then Exit Sub

 
        
        '-----send data to Extra-------
        Sess0.Screen.Moveto 7,30
        Sess0.Screen.Sendkeys("x")
        Sess0.Screen.PutString Dat, 10,21
        Sess0.Screen.Sendkeys("<EraseEOF>")
        Sess0.Screen.Sendkeys("<enter>")
        
        
        ExtraDat = Sess0.Screen.GetString (9,49,3) 'area getting data from Extra
        .cells(x,cola)=ExtraDat  'place info in 2nd col in excel
        
        Sess0.Screen.Sendkeys("<pf3>")

                
End Sub
 
Upvote 0 Downvote
correction to the above code, also when i step through it, it get Object error for this line of code
Code: 
Dat = .cells(x,1)
and it says stoping macro playback, nothing happens in extra at this point only the excel file opens.

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\test.xls"
        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file
        '---------------------------------
        
row = 1
col = 1
cola = 2

   with obj.worksheets("Sheet1") 
   
    for x = rw to obj.ActiveSheet.Rows.Count   
    
    Dat = .cells(x,1)
    If Dat = "" Then Exit Sub

 
        
        '-----send data to Extra-------
        Sess0.Screen.Moveto 7,30
        Sess0.Screen.Sendkeys("x")
        Sess0.Screen.PutString Dat, 10,21
        Sess0.Screen.Sendkeys("<EraseEOF>")
        Sess0.Screen.Sendkeys("<enter>")
        
        
        ExtraDat = Sess0.Screen.GetString (9,49,3) 'area getting data from Extra
        .cells(x,cola)=ExtraDat  'place info in 2nd col in excel
        
        Sess0.Screen.Sendkeys("<pf3>")
    
    next x
    
    end with
                
End Sub
 
Upvote 0 Downvote

you must watch your syntax...
Code: 
for x = rw to obj.ActiveSheet.Rows.Count

rw is not defined but you do have row defined

Code: 
row=1

you are forcing me to wear glasses...
 
Upvote 0 Downvote
Ok, how would i put in an IF statement if no data was found to put in excel
i tried this, but it didn't work
Code: 
If Sess0.Screen.Getstring(23,02,17) = "DATA NOT FOUND" Then
            
            .cells(x,cola) = "DATA NOT FOUND"
 
Upvote 0 Downvote
Code: 
Sess0.Screen.Getstring(23,02,17)

23 = row
02 = col
17 = # of characters in string

DATA NOT FOUND
12345678901234

 
Upvote 0 Downvote
that's an if statement if Extra comes back w/nothing found? All i want to do is just put in the 2nd col of excel instead of it being blank for not showing any data, just put "data not found" if extra doesn't find anything.
 
Upvote 0 Downvote


Code: 
dim msga as string

msga = "DATA NOT FOUND"

If [b]TRIM(Sess0.Screen.Getstring(23,02,17))[/b] = msga Then
            
            .cells(x,cola) = msga
something that you ALWAYS have to do when picking values off a screen. TRIM, TRIM, TRIM!!!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote


Have you checked HELP?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
if Extra comes back w/nothing found
Click to expand...
doesn't say too much of anything...
i mean, is the screen totally blank? is there a field that comes back as a blank? is there a narrative that states "nothing found"????

"DATA NOT FOUND" is 14 characters in length (count it)
and your statement in your code calls for 17 characters. i already pointed that out to you.
Code: 
Sess0.Screen.Getstring(23,02,[blue]17)[/blue]
.

skip already gave you another tip with the TRIM statement

you really need to check out the HELP files.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

S
  • Locked
  • Question
Reflection Workspace extract all screen/pages
Replies
0
Views
200
SSS27
S
Daneyuleb
  • Locked
  • Question
Waitfortext1 -- How on earth does the RegularExpression option work?
Replies
2
Views
174
SkipVought
S
dhsd
  • Locked
  • Question
VBA code works fine in debug mode but not when I run it
Replies
5
Views
222
SkipVought
S
d3sdinova
  • Locked
  • Question
Using VBA to work with two Attachmate sessions at the same time
Replies
9
Views
212
SkipVought
S
Biznez
  • Locked
  • Question
Add does not work
Replies
4
Views
98
Biznez
Biznez

Part and Inventory Search

Sponsor

Back
Top