Announcement

**gcraill** · August 14, 2016, 03:05 PM

Hi Adam, Depends on how you are connecting to the IBM i. Three main ones I have come across are Rumba from microfocus (boo), PCOM from IBM (or whatever it is called now) and ACS (Access Client Solutions - also from IBM). Also if you are on Mac/linux/windows it may have some bearing as to how it all works also.

PCOM is "stabilised" from IBM so no new development, but there is docco here http://www.ibm.com/support/knowledge...t_access08.htm that shows how to connect to EXCEL etc to do what you want.

Rumba can also be used for this stuff but I have less knowledge in that area.

ACS probably does it also but I find the docco quite hard to follow, and can't seem to see how it links to Excel.

Will see if I can dig up an example.

**gcraill** · August 14, 2016, 08:32 PM

The attached example requires macros to be enabled in Excel. It works on XP or Win7, (well it used to in the day for XP) and I don't have Win8/Win10 to test on.

If you have client access (PCOM) installed it uses the connections you have defined so that needs to be set up first, it checks in the default install libraries for them, so if you have your configs etc in a non standard place it may not find them.

Open the spreadsheet macro enabled, and click the big green button on the control tab. This opens a dialog, if no sessions are open you have the option to start a new session, once one or more sessions are open you can select an LPAR to run the macro over. Macros require to find certain text on the screen at a set position (this is to make sure you are on the correct start screen in order to avoid automated butchering of your database by accident)

It also has a debug mode so you can step through the code etc.

There are 2 examples ...
1 - to list all of your spoolfiles into the spreadsheet. Note this may run for some time if you have too many SPLF's. I created it to validate the page down and looping type stuff in the macro.
2 - to list jobs in joblog(pending state), this was to test a double pass idea where the first run listed jobs, and the second would do an action on them if they were then marked as "X", not that we actually use this function ... it was something I did and validated via debug etc.

Hopefully to anyone of some coding experience it makes sense.

Attached Files