Macro for data into and from Excel 1

[ptw78]

I'm trying to write a macro that will take 2 different strings from excel and input it into my screen, then pull the data the screen pulls up and put it in a spot in excel. I need this for a couple diff row and columns in excel. basically below is how the excel sheet will be formated, I will need the date put into my screen, then the id, so for example 3012009 then 1234, enter, some data pulls up for that date, it's just one field. i understand the rows/colums of the fields on the screen is what i need. But where i'm confused is how can i get name2 with their id and the date starting over again to input and pull the data i need. any help on this. Thx



REP name1 name2 name3
Date ID 1234 678 9876
3012009
3022009
3032009
3042009
3052009
3062009

 

[vzachin]

try this out; not the best coding, but functional

Sub Main
	
Dim Sessions, System As Object, Sess0 As Object, Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "C:\test"
        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("ptw78")
        
        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(x,y)
        If MyDat = "" Then Exit Sub
        'msgbox MyDat
        'msgbox MyNam
        
        '-----send data to Attachmate-------
        
        Sess0.Screen.PutString MyDat,2,4        'not sure where
        Sess0.Screen.PutString MyNam,6,4        'you're placing this
        Sess0.Screen.Sendkeys("<enter>")
        '-----grab data from Attachmate----- 
        ExtraDat = Sess0.Screen.GetString (24,2,18) 'not sure where you're
                                                    'getting your data
        
        '-----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]

slight modification because i think i made a mistake. the names should not change with the dates

MyNam = .cells(x,y)   'replace
MyNam = .cells(4,y)   'with this  'name are in row 4

sorry for the confusion

 

[ptw78]

thx for this, not sure i follow it completely, but what if i wanted to add more columns, do i just change this?
col = 3
cola= 6
to how many i need?

 

[vzachin]

replace

col =3
for y = 3 to 5

with

 begcol = 3 'decide which column to begin
endcol = 10 'decide which column to end
'endcol = 10 means column "j"
for y = begcol to endcol

i used

cola = 6
cola = cola +1

to place data back to your excel sheet, column 'g". change this to suit..

if you want the data in the same columns as your sheet then replace

cola = 6
cola = cola + 1
.cells(x,cola) = ExtraDat

with

.cells(x,y) = ExtraDat 'where x is the row and y is the column

lastly, if you want to place the data back in your excelsheet in different rows & columns, then define the startrow & startcolumn

hth

 

[ptw78]

I get an error on the first line of code, it says
Illegal redefinition of 'Set'

 

[SkipVought]

Excel Tip:

To get the Column Number, for the corresponding Column Letter...

ColNbr = xlSheet.cells(1,"ZZ").Column

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vzachin]

ptw78,

can you post your code?

 

[ptw78]

also, what does this mean?

with obj.worksheets("ptw78")

Sub Main
    
Dim Sessions, System As Object, Sess0 As Object, Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\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("ptw78")
        
        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

 

[SkipVought]

also, what does this mean?

Have you looked in Extra HELP on

With (statement) — Execute statements on a specified variable


Copyright 1996 - 1999, Attachmate Corporation. All Rights Reserved.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

what exactly is "ptw78" variable, i'm not really a programmer so i'm not too familiar with this.

 

[SkipVought]

what exactly is "ptw78" variable,

"ptw78" is a LITERAL not a VARIABLE.

It was vzachin's way of saying YOUR WORKSHEET since your handle is ptw78.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vzachin]

also, what does this mean?

with obj.worksheets("ptw78") 'replace "ptw78" with the name of the sheet in excel

I get an error on the first line of code, it says
Illegal redefinition of 'Set'

i don't see how you would get this error.
silly question: are you using attachmate extra?

you will be a programmer by the time you understand all this

 

[ptw78]

xtra extreme 8.0 sp1 is what it says. by attachmate. and i hope i can understand a lot of this, any tips, help would be greatly appreciated. thx