×
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

Excel to Attachmate Extra. Copy & Paste & Loop

Excel to Attachmate Extra. Copy & Paste & Loop

Excel to Attachmate Extra. Copy & Paste & Loop

(OP)
All,

I need help with the below code, I am trying to copy data from Excel & Paste into Attachmate Extra. My columns are constant (I,A,B,D,C,E,F,G,H) while my rows will vary with the amount of data day-over-day. Can't figure out how to build the loop function after my code to run through all rows of data after the Row 2 (I have a header).

obj.Worksheets("Muni Loading").Cells(2, "A").Copy = Sess0.Screen.Paste(5, 8), this is not working for me. Can someone tweak this?

CODE --> VBA

Sub OddOrEven_2()
'Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")   ' Gets the system object
    If (System Is Nothing) Then
        MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
        Stop
    End If
    Set Sessions = System.Sessions

    If (Sessions Is Nothing) Then
        MsgBox "Could not create the Sessions collection object.  Stopping macro playback."
        Stop
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
    g_HostSettleTime = 1000    ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
        If (g_HostSettleTime > OldSystemTimeout) Then
            System.TimeoutValue = g_HostSettleTime
        End If

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
        If (Sess0 Is Nothing) Then
            MsgBox "Could not create the Session object.  Stopping macro playback."
            Stop
        End If
    If Not Sess0.Visible Then Sess0.Visible = True
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
'--------------------------------------------------------------------------------'---------------------------|||||||||||||'
'Begin Loading Municipal Bond Securities
Dim obj As Object
 Dim objWorkbook As Object
 Set obj = CreateObject("Excel.Application")
 obj.Workbooks.Open Filename:="\\evnt30\JuniorShrdata\SECURITY\High Yield\Blue Falcon\Security Add Macro.xlsm" 'File location If the file is not already open
 obj.Visible = True 'Not used if file is already open
'--------------------------------------------------------------------------------'---------------------------|||||||||||||'
'--------------------------------------------------------------------------------'---------------------------|||||||||||||'
'Activate Corp Loading Sheet & Format Date Columns
        Sheets("Corp Loading").Activate
                 Range("B2:C300").Select
                    Selection.Value = Selection.Value
                        Selection.NumberFormat = "yyyymmdd"
                        
'Activate Muni Loading Sheet & Format Date Columns
        Sheets("Muni Loading").Activate
                Range("B2:C300").Select
                    Selection.Value = Selection.Value
                        Selection.NumberFormat = "yyyymmdd"
'--------------------------------------------------------------------------------'---------------------------|||||||||||||'

Sess0.Screen.PutString "EDITSEC", 23, 47
Sess0.Screen.SendKeys ("<Enter>")
obj.Worksheets("Muni Loading").Cells(2, "I").Copy = Sess0.Screen.Paste(15, 7)
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.MoveTo 12, 37
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.MoveTo 6, 2
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.MoveTo 13, 38
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> O
    DoEvents
    Loop

obj.Worksheets("Muni Loading").Cells(2, "A").Copy = Sess0.Screen.Paste(5, 8) 'Security Field
                        Sess0.Screen.PutString "50", 8, 48                        'Issue Type
obj.Worksheets("Muni Loading").Cells(2, "B").Copy = Sess0.Screen.Paste(9, 8)  'DTD Field
obj.Worksheets("Muni Loading").Cells(2, "D").Copy = Sess0.Screen.Paste(9, 68) 'Closing Price
obj.Worksheets("Muni Loading").Cells(2, "C").Copy = Sess0.Screen.Paste(12, 32) 'Maturity Date
obj.Worksheets("Muni Loading").Cells(2, "E").Copy = Sess0.Screen.Paste(14, 14) ' M Rating
obj.Worksheets("Muni Loading").Cells(2, "F").Copy = Sess0.Screen.Paste(14, 34) 'S&P Rating
obj.Worksheets("Muni Loading").Cells(2, "G").Copy = Sess0.Screen.Paste(14, 50) 'Coupon Rate
obj.Worksheets("Muni Loading").Cells(2, "H").Copy = Sess0.Screen.Paste(15, 14) 'STATE 

End Sub

RE: Excel to Attachmate Extra. Copy & Paste & Loop

(OP)
All,

Can this thread be deleted? I was able to solve the initial issue.

-John

RE: Excel to Attachmate Extra. Copy & Paste & Loop

hi,

No! Simply post your solution, which is standard operating procedure here at Tek-Tips.

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