This one is bizarre. Essentially I have a system that pulls up a DOS program (great start right??) to export data from it. I am having my macro send the keys to navigate and then copy and paste the data from the screens. When I test the function going line by line with it works perfect. However, when I run the full command it breaks. Am I missing something obvious here??
Sub Import_Account_Cusip() 'Completes search by Account and Cusip
Dim LastPageCheck As String
Dim AccountCheck As String
Dim AcctNumber As String
Dim iRow As Integer
Dim AcctType As String
Dim Cusip As String
Dim FromDate As String
Dim ToDate As String
FromDate = Range("C25").Value 'This assigns the from date in the search filter
ToDate = Range("G25").Value 'This assigns the to date in the filter
Worksheets("LTXN Data").Visible = True
Worksheets("LTXN Formatting").Visible = True
Worksheets("Definitions").Visible = True
Sheets("Launch").Activate
AcctNumber = Range("C23").Value 'This assigns the value of the Account Number search
Cusip = Range("G23").Value 'This assigns the value of the Cusip search
' ******** set session to bluezone **********
Set Sess0 = CreateObject("BluezoneDOSSystem.WhllObj")
Sess0.Connect ("") 'Connect to bluezone session 1 - internally called A
' *****************************************************************************
HostSettleTime = 200 'set 200 milliseconds to wait for session to respond
Sess0.WaitReady 10, HostSettleTime
'--------------RESET VARIABLES-------------------
AccountCheck = ""
LastPageCheck = ""
Sess0.SendKeys ("<clear>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("LTXN ") & AcctNumber
Sess0.SendKeys ("<Enter>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Tab>") 'This is the search filter for CUSIP
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys ("CUS")
Sess0.SendKeys Cusip
Sess0.SendKeys ("<Enter>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<F12>")
'This is the date filter this is where the code
'is breaking as well because this basically
'pulls up a filter menu where you have to tab
'5 times to get to date input.
'However it seems to skip the
'next 6 lines of code. Any ideas???
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys FromDate
Sess0.SendKeys ToDate
Sess0.SendKeys ("<Enter>")
Sess0.WaitReady 10, HostSettleTime
Sess0.ReadScreen AccountCheck, 24, 4, 1
'looks for account number not found language
If LCase(AccountCheck) = "account number not found" Then
'using lower case to reduce case sensitivity
MsgBox ("Account Number Not Found. Please Double Check Account Number.")
Exit Sub
End If
Sess0.ReadScreen NoDataChk, 1, iRow, 4
'check for no data in at all
If NoDataChk = " " Then
MsgBox ("There appears to be no transactions.")
Exit Sub
End If
Sheets("LTXN Data").Activate
'set active WS to LTXN Data
Range("A2").Name = "Next_Line"
'Starts row A2 as starting row in spreadsheet
Do Until LCase(LastPageCheck) = "last page"
'Main Loop, do until it sees Last Page
For iRow = 10 To 22 'start Sub Loop
Sess0.ReadScreen NoDataChk, 1, iRow, 4
'check for no data in row-slight variation of above routine
If NoDataChk = " " Then GoTo 1000
Sess0.ReadScreen AcctType, 1, iRow, 2
'start read section, need to double check capture
'strings and look for unusual cases with long sym, high qtys, etc.
Sess0.ReadScreen EffDate, 8, iRow, 4
Sess0.ReadScreen TranType, 6, iRow, 13
Sess0.ReadScreen TickerSym, 5, iRow, 19
Sess0.ReadScreen Quantity, 14, iRow, 25
Sess0.ReadScreen SNoS, 1, iRow, 39
Sess0.ReadScreen TranDesc, 24, iRow, 41
Sess0.ReadScreen TrnTotal, 15, iRow, 65
Sess0.ReadScreen TrnType, 1, iRow, 80
Range("Next_Line").Value = AcctType 'filling in spreadsheet rows
Range("Next_Line").Offset(0, 1).Value = EffDate
Range("Next_Line").Offset(0, 2).Value = TranType
Range("Next_Line").Offset(0, 3).Value = TickerSym
Range("Next_Line").Offset(0, 4).Value = Quantity
Range("Next_Line").Offset(0, 5).Value = SNoS
Range("Next_Line").Offset(0, 6).Value = TranDesc
Range("Next_Line").Offset(0, 7).Value = TrnTotal
Range("Next_Line").Offset(0, 8).Value = TrnType
Range("Next_Line").Offset(1, 0).Name = "Next_Line"
'sets Next Line to next row first cell
Next iRow 'next subloop
1000
Sess0.SendKeys ("<PF8>") 'Main Loop page forward
Sess0.WaitReady 10, HostSettleTime
Sess0.ReadScreen LastPageCheck, 9, 4, 1
'Main Loop look for last page language.
'might need to be move depending on second f8 press needed or not.
'if not then move to beginning after Do statement
Loop 'Main Loop
MsgBox ("LTXN Transaction Macro has completed gathering information from Account " + AcctNumber) 'Closing message
End Sub
To @ALeXcel's credit, this is absolutely Attachmate Extra. I was able to fix it with the following code:
Sess0.SendKeys ("<F12>") 'This is the date filter
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Tab>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Tab>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Tab>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Tab>")
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Tab>")
Sess0.SendKeys FromDate
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ToDate
Sess0.WaitReady 10, HostSettleTime
Sess0.SendKeys ("<Enter>")
Sess0.WaitReady 10, HostSettleTime