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 wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro for data into and from Excel 1

Status
Not open for further replies.
ptw78

ptw78

Technical User
Mar 5, 2009
155
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I get an error on the first line of code, it says
Illegal redefinition of 'Set'
 
Upvote 0 Downvote



Excel Tip:

To get the Column Number, for the corresponding Column Letter...
Code: 
ColNbr = xlSheet.cells(1,"ZZ").Column


Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote


also, what does this mean?
Click to expand...
Have you looked in Extra HELP on
With (statement) — Execute statements on a specified variable


Copyright 1996 - 1999, Attachmate Corporation. All Rights Reserved.
Click to expand...

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
what exactly is "ptw78" variable, i'm not really a programmer so i'm not too familiar with this.
 
Upvote 0 Downvote

what exactly is "ptw78" variable,
Click to expand...

"ptw78" is a LITERAL not a VARIABLE.

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

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
ptw78 said:
also, what does this mean?
Click to expand...

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


ptw78 said:
I get an error on the first line of code, it says
Illegal redefinition of 'Set'
Click to expand...
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


 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FreCard
  • Locked
  • Question
Extra macro example
Replies
3
Views
599
SkipVought
S
CADD6953
  • Locked
  • Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
1K
SkipVought
S
CADD6953
  • Locked
  • Question
Macro for Excel to Attachmate and Back Again 1
2
Replies
34
Views
2K
SkipVought
S
mdaiber614
  • Locked
  • Question
Macro to Copy Text from IE Browser and Paste in MS Word
Replies
4
Views
544
mdaiber614
mdaiber614
jeaneshe
  • Locked
  • Question
How do I select different Excel cells using a variable column loop?
Replies
10
Views
687
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top