Excel to Attachmate Extra. Copy & Paste & Loop

[jgarvey23]

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?

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

 

[jgarvey23]

All,

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

-John

 

[SkipVought]

hi,

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