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?
End Sub
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
Can this thread be deleted? I was able to solve the initial issue.
-John
RE: Excel to Attachmate Extra. Copy & Paste & Loop
No! Simply post your solution, which is standard operating procedure here at Tek-Tips.