×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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

Macro for data into and from Excel

thread99-1534821

Macro for data into and from Excel

Macro for data into and from Excel

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

RE: Macro for data into and from Excel

try this out; not the best coding, but functional

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 = "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

RE: Macro for data into and from Excel

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

CODE

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

sorry for the confusion

RE: Macro for data into and from Excel

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

RE: Macro for data into and from Excel

replace

CODE

col =3
for y = 3 to 5
with

CODE

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

CODE

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

CODE

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

CODE

.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

RE: Macro for data into and from Excel

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

RE: Macro for data into and from Excel




Excel Tip:

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

CODE

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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue
glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for data into and from Excel

ptw78,

can you post your code?
 

RE: Macro for data into and from Excel

(OP)
also, what does this mean?

CODE

with obj.worksheets("ptw78")

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 = "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

RE: Macro for data into and from Excel



Quote:

also, what does this mean?
Have you looked in Extra HELP on

Quote:


With (statement) — Execute statements on a specified variable


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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue
glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for data into and from Excel

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

RE: Macro for data into and from Excel


Quote:


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,
glassesDon't let the Diatribe...
talk you to death!tongue
glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for data into and from Excel

Quote (ptw78):

also, what does this mean?

CODE

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


Quote (ptw78):

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


 

RE: Macro for data into and from Excel

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

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