×
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

Input from excel to attachmate

thread99-1679233

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