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:
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) 'STATEEnd Sub