Export Host Screen To Excel Spreadsheet
.ZMC
This macro will prompt the user for a path and filename then export the current PASSPORT screen to the specified Excel spreadsheet where each row of the host screen is placed into a new row within the spreadsheet. If the file exists, the user will be prompted whether or not the file should be overwritten.
' This macro prompts the user for a folder and filename then exports
' the current screen to the specified Excel spreadsheet.
Sub ZMain
' Prompt the user for the path and filename for the Excel spreadsheet:
strFolder = MsgBoxGetInput("Please enter the folder and filename for the spreadsheet:" & vbCRLF & vbCRLF & _
"(Leaving blank will store MyExcel.xlsx in the user's My Documents folder)" & vbCRLF & "(Path MUST already exist.)")
If strFolder <> "" Then
strFile = strFolder
Else
strFile = "MyExcel.xlsx" 'Modify the name of this file as necessary
End If
' Create the Excel objects
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
Set objSheet = CreateObject("Excel.Sheet")
Call Excel_log (objLine, objSheet)
objSheet.Application.ActiveSheet.SaveAs strFile
End Sub
' Subroutine to export the host screen data
Sub Excel_log (objLine, objSheet)
For i = 1 to 24
objLine = GetString (i, 1, 80)
objSheet.Application.Activecell.value = objLine
objSheet.Application.Activecell.Offset(1,0).select
Next
End Sub |
The above example is designed to work with a TN3270 Model 2 screen (24 rows x 80 columns). If you require this functionality for other screen sizes, you must modify the For Next loop statement to match the number of rows and the GetString statement to match the number of columns.