×
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

Macro for data into and from Excel

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