×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

why doesn't this code work

why doesn't this code work

why doesn't this code work

(OP)
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
 

RE: why doesn't this code work



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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
you lost me.

RE: why doesn't this code work


use 2 lines...

CODE

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")

RE: why doesn't this code work




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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
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                
 

RE: why doesn't this code work




Is this directly related to your other thread?  

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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
not, not related to the other

RE: why doesn't this code work



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>")
                Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(r, c)
                

'                '-----grab data from Attachmate-----
                ExtraDat = Sess0.Screen.GetString(11, 55, 2) 'area getting data from


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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work




Put the MoveRelative BEFORE the Sendkeys.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work




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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

Quote:

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


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

RE: why doesn't this code work

(OP)
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.  

RE: why doesn't this code work

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?




 

RE: why doesn't this code work

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

CODE

msgbox MyDat
msgbox MyNam

RE: why doesn't this code work

Quote:

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

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?

 

RE: why doesn't this code work

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

RE: why doesn't this code work



LB,

Thanx and good luck with this new horizon.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
yes, the coordinates  are blank and no data is being put in

RE: why doesn't this code work

hi ptw78

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

Quote:

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 ?

RE: why doesn't this code work

(OP)
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.

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work

(OP)
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.  

RE: why doesn't this code work

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

add

CODE

Sess0.Screen.PutString "DATAHERE",4,25
before this line        

CODE

Sess0.Screen.PutString MyDat,4,25  
   
and what do you get?

how are you stepping through the code?    

RE: why doesn't this code work

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

RE: why doesn't this code work


Quote:

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

 

RE: why doesn't this code work

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

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

RE: why doesn't this code work

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

RE: why doesn't this code work

CODE

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

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

RE: why doesn't this code work

(OP)
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.

RE: why doesn't this code work

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...

 

RE: why doesn't this code work




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

You wait for a cursor on an emulator screen.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
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

CODE

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.

RE: why doesn't this code work

(OP)
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

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

RE: why doesn't this code work

(OP)
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.
[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= 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]

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work

(OP)
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.  

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work

Quote:

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

CODE

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

CODE

for x = rw to endrw

Quote:

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:

CODE

ExtraDat = Sess0.Screen.GetString (11,55,3) 'area getting data from
check to see if there is any data...


Quote:

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
 

RE: why doesn't this code work

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
        '----------------------------------
begrw = 3  
endrw = 33
    
col = 3   
cola= 2            
        with obj.worksheets("Ref-Rev")
        
        for x = begrw to endrw    '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
    Sess0.Screen.PutString "     ",5,13  '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

    Sess0.Screen.PutString "     ",4,25    '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

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work

what have you tried so far?

RE: why doesn't this code work

(OP)
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.

RE: why doesn't this code work

Quote:

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?

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

Quote:

i haven't tried anything because I know very little about it.
you really need to experiment with code otherwise you will never learn...trial and error is the best teachersmile
 

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work

(OP)
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

RE: why doesn't this code work


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...

RE: why doesn't this code work

(OP)
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"

RE: why doesn't this code work

CODE

Sess0.Screen.Getstring(23,02,17)

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

DATA NOT FOUND
12345678901234

 

RE: why doesn't this code work

(OP)
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.  

RE: why doesn't this code work



CODE

dim msga as string

msga = "DATA NOT FOUND"

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

(OP)
what's trim do?

RE: why doesn't this code work



Have you checked HELP?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: why doesn't this code work

Quote:

if Extra comes back w/nothing found
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,17)
.

skip already gave you another tip with the TRIM statement

you really need to check out the HELP files.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close