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


Hi,

Code: 
Sub Main
    
Dim oSessions as object, oSystem As Object, Sess0 As Object 

Set oSystem = CreateObject("EXTRA.System")
Set oSessions = System.Sessions
I would not use System or Sessions as an object as they are reserve words.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote

use 2 lines...

Code: 
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
 
Upvote 0 Downvote



Where? I cannot read you mind. You must explain what you mean.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
I have it working, but it doesn't work properly. What I'm trying to get it to do is get data from excel and put it in Extra the columns are labeled below, row one would start where name1, name2, name3 would be. basically i need to get the dates in col A, and the number in col C row2, put it in extra and retrieve the data and put that data back into excel, repeat full a full months of date and do the same for col D, E, F, and so forth. Right now all it does it put in the info from col C row 2, and Col A, row5, and put the data in a new excel file but it repeats and puts the same data in cols G, H, I

A B C D E

Name1 Name2 Name3
Date 6565 12758 4770
20090301
20090302
20090303
20090304
 
Upvote 0 Downvote



Is this directly related to your other thread?

Please do not mix your responses or post the same question twice.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
not, not related to the other
 
Upvote 0 Downvote


where r & c are in WaitForCursor is the row and column coordinates on the screen where the cursor will rest after the ENTER command is issued and data has been returned. YOU will have to supply those numbers.
Code: 
'                '-----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>")[b]
                Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(r, c)
                [/b]
'                '-----grab data from Attachmate-----
                ExtraDat = Sess0.Screen.GetString(11, 55, 2) 'area getting data from


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

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote



Put the MoveRelative BEFORE the Sendkeys.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
it runs w/o any errors, but doesn't do anything other than opening up an excel doc, it doesn't get any data from extra or anything. 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 = "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
 
Upvote 0 Downvote



did you try to step thru the procedure to see what is happening?

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
doesn't do anything other than opening up an excel doc, it doesn't get any data from extra or anything. here is what i have
Click to expand...


as skip said "did you try to step thru the procedure to see what is happening?"

is it putting data from excel into extra?

Code: 
ExtraDat = Sess0.Screen.GetString (11,55,3)
is there any data at 11,55,3 ?

on your excel sheet, do you have different names in each column for each date?

it's possible the macro is running too quickly. add a few
Code: 
Sess0.Screen.WaitHostQuiet(50)

i stepped through your code and is appears to function, except it keeps repeating the names while changing the dates
 
Upvote 0 Downvote
I stepped through the code, and the only thing it does it open up an excel sheet. It doesn't do anything to extra and does put any data into the excel sheet.
 
Upvote 0 Downvote
verify if your code is actually looking at the correct cell in excel

in your code over here:
Code: 
        If MyDat = "" Then Exit Sub
        'msgbox MyDat
        'msgbox MyNam
place this back in
Code: 
msgbox MyDat
msgbox MyNam
are there any values for either one when you step through the code?




 
Upvote 0 Downvote
all this code gives me pop box that says 20090303 and then another that says ok.
Code: 
msgbox MyDat
msgbox MyNam
 
Upvote 0 Downvote
all this code gives me pop box that says 20090303 and then another that says ok.
Click to expand...

that would be the data from your excel sheet over here
Code: 
        MyDat = .cells(x,1)
        MyNam = .cells(4,y)

so it is reading the excell sheet.

and if you stepped through your code, it should've placed
20090303 & ok
over here
Code: 
        Sess0.Screen.PutString MyDat,4,25        'area data goes into
        Sess0.Screen.PutString MyNam,5,13       'in citilink

are you saying that at 4,25 & 5,13 coordinates in attachmate, it is blank? that there is no data being pasted?

 
Upvote 0 Downvote
Skip,

Have a * for your posts of May 12 at 9:18 and 9:39. Your example of how to use MoveRelative and WaitForCursor just opened up whole new lines of thought for me!

Cheers,

-LB
 
Upvote 0 Downvote


LB,

Thanx and good luck with this new horizon.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
yes, the coordinates are blank and no data is being put in
 
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
173
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