Excel Macro : Repetitive Ctrl+C (and some more)

My Excel skills are being tested quite often now. I had to work on a repetitive task to close a large list of support incidents. The input list of incident numbers were in an excel sheet and for each, I had to go to the incident management software and update some fields (primarily drop-down selections) prior to closure. As any gamer would attest, it is not much fun having to switch between two sides of the keyboard when also working with a mouse. Hence the act of Ctrl+C & Ctrl+V would be interrupted by having to use the down arrow and get to the next cell. I decided to write an Excel Macro that would go to the next cell below, select it and copy it. If you think it would be tough, think again. Here is the code.

Public Sub NextRowCopy()
 
    Dim currRow As Integer
 
    currRow = ActiveCell.Row
    currRow = currRow + 1
 
    Range("C" & currRow).Select
    Selection.Copy
 
End Sub

I pasted this in a new module in my workbook (Alt+F11 » Right Click Workbook in Project Explorer to the left and Insert » Module). As you would have guessed, the incident numbers were in column C. After saving the macro, I went back to the workbook. I pressed Alt+F8 to see the NextRowCopy macro in the popup window. Selected it and clicked on Options, assigned it a shortcut Ctrl+Q (again for the same reason that Q was to the same side of the keyboard as C).

Once I updated the Incident, I would do a Ctrl+Q and I would have the next incident number to be worked on.

The And Some More part

Most of the fields to be updated in the system were dropdown selections, but there was a standard text to be updated as well. I couldn’t copy it from excel since it would change my cursor location and I didn’t want to have to open a notepad and cycle three windows for this. I searched for a multiple clipboard manager and decided to use QuickTextPaste. It lets you assign shortcuts to predefined text, run programs and more.

My task was completed and while looking back at my macro, I think I could have had excel do the copy of the predefined text as well. Let us say that the existing data was from columns A through N. I could add the text I needed to paste in Column Q and have the macro check where I was currently and move to the right cell.

Public Sub NextRowCopy()
 
    Dim currRow As Integer
 
    currRow = ActiveCell.Row
    currCol = ActiveCell.Column
 
    If currCol = 3 Then
        ' We are in the incidents column. Switch to column O
        currCol = "O"
    Else
        ' We are in new column O, switch to column C in the next row
        currCol = "C"
        currRow = currRow + 1
    End If
 
    Range(currCol & currRow).Select
    Selection.Copy
 
End Sub

Each time the macro ran, my clipboard had either the incident to be searched or the text to be entered to close the incident. Total time saved:  30 minutes 

Screen capture from the mainframe

If I wanted any proof that I was a resillient and obssessive automator, this incident was more than sufficient. Our team was faced with a predicament – the business wanted to get some reports downloaded from the testing region. Usually this was an easy process, we could download the report From CA-View and email or FTP it.

After a migration to a new firewall, FTP & email stopped working and we had to download reports that were more than 1000 pages. The last option (not the best, but had to be done) was a screen capture process. I had three options

  • VB.Net to use the Quick3270 Object and extract the data
  • Excel and VBA to capture screenshots (again with the Quick3270 Object)
  • Quick3270 Macro Language

I tried each and every option from the list above (in the same order). VB.Net seemed to be an overkill. I had to create forms (to collect screen details, logon information etc.)

screen-capture-macro

VBA & Excel was looking like a good option; I had worked with this combination in the past when we had to download the entire list of FCTs from the CICS region (CEMT & Screen capture did a pretty good job). The screen capture Excel Macro worked well, except for a couple of points which didn’t make this the option of choice. Firstly, the macro could capture data one line at a time (since the code needed to identify the last line of the data on the screen). Secondly, due to login process, screen navigation, etc., one couldn’t guarantee that the user was on the right page. Finally, it was nearly impossible to pause/stop the macro without force closing the program.

Quick3270 Macro it was. While it is based on Visual Basic commands and structure (it even has constants such as vbOkOnly), it does not have the entire instruction set. Picking up parts from the VBA code and modifying it for the Macro language, the final product was ready.

The Macro language had decent support for File I/O and could create Excel.Application Objects. With this, I gave flexibility to the user to route the output into a new excel sheet or into a text file. The language did not support arrays, which meant that concatenating data across screens (scrolling horizontally) required some long winding logic. Creating 24 different variables (one for each line), assigning them data, scrolling and appending data, this took up about 1/3rd of the macro coding. Similar pages of code had to be written for outputting data (variable by variable).

The reference Guide for the Quick3270 Macro language can be found on the dn-computing downloads page. I must warn you that the Macro language is specific to the version you are using. The manual is for v4.12, do take this in consideration while starting to code.

With my battle scars to show for and a running macro, I think I will keep this language in mind during future ventures into Mainframe Automation.