Hello everyone. I wouldn't classify myself as a programmer but a tinker who knows enough to screw things up.
I am using code in Excel to utilize Reflection for Unix and OpenVMS (14.0.687) SP6 to automatically run the same report on several codes that have been entered in Excel. The problem I ran into is that after the report is run the first time, one or two returns are necessary before the report can restart. When a return is necessary, the following prompt is shown: "Enter RETURN to continue or '^' to exit:". The prompt I'm trying to get to is "Select ACRP Reports Menu Option" before starting the process over. From perusing through the forums, I tried using getstring, but the row will always change so I'm unsure if that can be use. Then I saw ReadLine command but I'm not sure if I'm using it correctly. Could you please give me ideas or point out where in this code I went wrong?
Sub CUSSReportVistAExtract()
' This will use an existing VistA session to run the CUSS Ad Hoc Report
Dim CUSSReportVistAExtract As String
CUSSReportVistAExtract = "H:\CUSS_VistA_Extract.txt"
' Save current Workbook while deleting previous export file if it exists
ActiveWorkbook.SaveAs Filename:="H:\tempWeeklyAccessReport.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Len(Dir$(CUSSReportVistAExtract)) > 0 Then
'First remove readonly attribute, if set
SetAttr CUSSReportVistAExtract, vbNormal
'Then delete the file
Kill CUSSReportVistAExtract
End If
' Activate Attachmate session
Dim isFound As Integer
Dim ReflectionUO As Object
Set ReflectionUO = GetObject(, "Reflection2.Session")
With Session
.DisplayColumns = 132
' Extract report
.DefaultPrinter = "detpt-sysred,winspool,\\xxxx\xxxx"
.PrintToFile = CUSSReportVistAExtract
.PrinterLogging = True
' Run report Stop Codes
Dim w As Integer
w = 4
If Cells(w, 1) <> "FinallyDone" Then
Do Until Cells(w, 1).Value = "FinallyDone"
.Transmit "^CUSS"
.TransmitTerminalKey rcVtEnterKey
.Transmit "1"
.TransmitTerminalKey rcVtEnterKey
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(4, 8)
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(5, 8)
.TransmitTerminalKey rcVtEnterKey
.Transmit "RS"
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(w, 1)
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(w, 1)
.TransmitTerminalKey rcVtEnterKey
.Transmit "home;132;99999999"
.TransmitTerminalKey rcVtEnterKey
' *** THIS IS WHERE I NEED TO DETERMINE IF AN ENTER IS NECESSARY
Dim strVistAPrompt As String
strVistAPrompt = ReadLine("00:00:01")
If strVistAPrompt = "Enter RETURN to continue or '^' to exit:" Then
.TransmitTerminalKey rcVtEnterKey
End If
strVistAPrompt = Sess0.screen.ReadLine("00:00:01")
If strVistAPrompt = "Enter RETURN to continue or '^' to exit:" Then
.TransmitTerminalKey rcVtEnterKey
End If
' Go to the next code
w = w + 1
Loop
End If
' Turn off Extract
.PrinterLogging = False
End With
End Sub
Thank you in advance.
Dave
I am using code in Excel to utilize Reflection for Unix and OpenVMS (14.0.687) SP6 to automatically run the same report on several codes that have been entered in Excel. The problem I ran into is that after the report is run the first time, one or two returns are necessary before the report can restart. When a return is necessary, the following prompt is shown: "Enter RETURN to continue or '^' to exit:". The prompt I'm trying to get to is "Select ACRP Reports Menu Option" before starting the process over. From perusing through the forums, I tried using getstring, but the row will always change so I'm unsure if that can be use. Then I saw ReadLine command but I'm not sure if I'm using it correctly. Could you please give me ideas or point out where in this code I went wrong?
Sub CUSSReportVistAExtract()
' This will use an existing VistA session to run the CUSS Ad Hoc Report
Dim CUSSReportVistAExtract As String
CUSSReportVistAExtract = "H:\CUSS_VistA_Extract.txt"
' Save current Workbook while deleting previous export file if it exists
ActiveWorkbook.SaveAs Filename:="H:\tempWeeklyAccessReport.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Len(Dir$(CUSSReportVistAExtract)) > 0 Then
'First remove readonly attribute, if set
SetAttr CUSSReportVistAExtract, vbNormal
'Then delete the file
Kill CUSSReportVistAExtract
End If
' Activate Attachmate session
Dim isFound As Integer
Dim ReflectionUO As Object
Set ReflectionUO = GetObject(, "Reflection2.Session")
With Session
.DisplayColumns = 132
' Extract report
.DefaultPrinter = "detpt-sysred,winspool,\\xxxx\xxxx"
.PrintToFile = CUSSReportVistAExtract
.PrinterLogging = True
' Run report Stop Codes
Dim w As Integer
w = 4
If Cells(w, 1) <> "FinallyDone" Then
Do Until Cells(w, 1).Value = "FinallyDone"
.Transmit "^CUSS"
.TransmitTerminalKey rcVtEnterKey
.Transmit "1"
.TransmitTerminalKey rcVtEnterKey
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(4, 8)
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(5, 8)
.TransmitTerminalKey rcVtEnterKey
.Transmit "RS"
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(w, 1)
.TransmitTerminalKey rcVtEnterKey
.Transmit Cells(w, 1)
.TransmitTerminalKey rcVtEnterKey
.Transmit "home;132;99999999"
.TransmitTerminalKey rcVtEnterKey
' *** THIS IS WHERE I NEED TO DETERMINE IF AN ENTER IS NECESSARY
Dim strVistAPrompt As String
strVistAPrompt = ReadLine("00:00:01")
If strVistAPrompt = "Enter RETURN to continue or '^' to exit:" Then
.TransmitTerminalKey rcVtEnterKey
End If
strVistAPrompt = Sess0.screen.ReadLine("00:00:01")
If strVistAPrompt = "Enter RETURN to continue or '^' to exit:" Then
.TransmitTerminalKey rcVtEnterKey
End If
' Go to the next code
w = w + 1
Loop
End If
' Turn off Extract
.PrinterLogging = False
End With
End Sub
Thank you in advance.
Dave