×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Input from excel to attachmate

Input from excel to attachmate

Input from excel to attachmate

(OP)
Gentlement:

I have the code to grab data from a range of cells from excel, row by row and input to attachmate screen.  The code works fine for a few rows, but I would like to see if we can add the 2 FOR loops to automatically run the macro until the end of data rows.
Below is my code.  Thanks for any help!

=======================
'Declare the Excel Object
        Dim xlApp As Object, xlWorkbook As Object, xlSheet As Object, MyRange As Object
    Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True       
     
        xlApp.Workbooks.Open FileName:="C:\Documents and Settings\pp28569\Desktop\exception_for_notes.xls"  
        
        Set xlSheet = xlApp.worksheets("Sheet1")
    '=======exception 1        
        Set cus1 = xlapp.activesheet.Range("A2")     '
        Set fac1 = xlapp.activesheet.Range("B2")     '
        Set obl1 = xlapp.activesheet.Range("C2")     '
        Set reqst1 = xlapp.activesheet.Range("D2")     '
        Set mstop1 = xlapp.activesheet.Range("E2")     
        Set amt1 = xlapp.activesheet.Range("F2")   
        Set date1 = xlapp.activesheet.Range("G2")     '
     '=======exception 2   
        Set cus2 = xlapp.activesheet.Range("A3")     '
        Set fac2 = xlapp.activesheet.Range("B3")     '
        Set obl2 = xlapp.activesheet.Range("C3")     '
        Set reqst2 = xlapp.activesheet.Range("D3")     '
        Set mstop2 = xlapp.activesheet.Range("E3")     
        Set amt2 = xlapp.activesheet.Range("F3")   
        Set date2 = xlapp.activesheet.Range("G3")     '
    '=======exception 3    
        Set cus3 = xlapp.activesheet.Range("A4")     '
        Set fac3 = xlapp.activesheet.Range("B4")     '
        Set obl2 = xlapp.activesheet.Range("C4")     '
        Set reqst3 = xlapp.activesheet.Range("D4")     '
        Set mstop3 = xlapp.activesheet.Range("E4")     
        Set amt3 = xlapp.activesheet.Range("F4")   
        Set date3 = xlapp.activesheet.Range("G4")     '
        
        '======switch from  PNC General Customer Information screen to Collateral Create screen
        'Sess0.Screen.Sendkeys("<Pf12>")
       ' Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
        'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf8>")
        'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.PutString "s", 9, 24
        'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.SendKeys("<Enter>")
        'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.PutString "s", 8, 24
        'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.SendKeys("<Enter>")
        'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        
  '--------START INPUT exception 1
  
        Sess0.Screen.PutString(cus1) 5, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(fac1) 6, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(obl1) 7, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString "005", 11, 42                    '================ exception code 005
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.SendKeys("<Enter>")
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys ("<Tab><Tab><Tab>0898")                     '------our cost center here
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys("<Tab>TN<Pf8>")    
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Sendkeys("<Pf8><Pf8>")    
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(reqst1) 5, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(mstop1) 10, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(amt1) 11, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(date1) 12, 18

         'Sess0.Screen.Sendkeys("<Enter>")          '============= enter to input exception here
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
        
'=========================================exception 2
    If xlSheet.Cells(3,1).value > 0 Then
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(cus2) 5, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(fac2) 6, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(obl2) 7, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString "005", 11, 42                    '================ exception code 005
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.SendKeys("<Enter>")
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys ("<Tab><Tab><Tab>0898")                     '------our cost center here
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys("<Tab>TN<Pf8>")    
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Sendkeys("<Pf8><Pf8>")    
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(reqst2) 5, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(mstop2) 10, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(amt2) 11, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(date2) 12, 18

         'Sess0.Screen.Sendkeys("<Enter>")          '============= enter to input exception here
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
    End If
'=========================exception 3
    If xlSheet.Cells(4,1).value > 0 Then
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(cus3) 5, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(fac3) 6, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString(obl3) 7, 42
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.PutString "005", 11, 42                    
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.SendKeys("<Enter>")
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys ("<Tab><Tab><Tab>0898")                    
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys("<Tab>TN<Pf8>")    
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Sendkeys("<Pf8><Pf8>")    
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(reqst3) 5, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(mstop3) 10, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(amt3) 11, 18
            Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
         Sess0.Screen.PutString(date3) 12, 18

         'Sess0.Screen.Sendkeys("<Enter>")          '============= enter to input exception here
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        'Sess0.Screen.Sendkeys("<Pf12>")
    End If


 

RE: Input from excel to attachmate



hi,

Here's an example of the first 3 puts.  Should give you an idea of looping thru the values in a table on an Excel sheet...

CODE

  dim r as object, iCol as integer

  Set xlSheet = xlApp.worksheets("Sheet1")
  
  with xlsheet
    for each r in .range(.[A2], .[A2].end(xldown))
      Sess0.Screen.PutString(r.value) 5, 42
      Sess0.Screen.PutString(r.offset(0,1).value) 6, 42
      Sess0.Screen.PutString(r.offset(0,2).value) 7, 42
      Sess0.Screen.PutString(r.offset(0,3).value) 11, 42
'.....
    next
  end with
 

Skip,

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

RE: Input from excel to attachmate

(OP)
First, thanks for your tip Skip!
There is a syntax error on this line:

for each r in .range(.[A2], .[A2].end(xldown))

Should the format has an equal sign and "to" ?
How do I use iCol that declared?

I'll try to work around the code...

Thanks again.

RE: Input from excel to attachmate



if you do not have a reference set to the Microsoft Excel n.m Object Library, then you cannot use excel CONSTANTS like xldown value is -4121.

The correct syntax for objects in a collection is For Each...Next.

Skip,

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

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