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!

Input from excel to attachmate

Status
Not open for further replies.
poem2004

poem2004

Technical User
Feb 17, 2012
5
US
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


 
Sort by date Sort by votes


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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote


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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

247xavi
  • Locked
  • Question
Attachmate to BlueZone
Replies
0
Views
360
247xavi
247xavi
nightrider69
  • Locked
  • Question
Micro mainframe transmission issues using Attachmate myExtra
Replies
0
Views
426
nightrider69
nightrider69
CADD6953
  • Locked
  • Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
1K
SkipVought
S
AlbertShenko
  • Locked
  • Question
Excel VBA Not Sending F keys to Attachmate 1
Replies
15
Views
1K
SkipVought
S
CADD6953
  • Locked
  • Question
Macro for Excel to Attachmate and Back Again 1
2
Replies
34
Views
2K
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top