Re: EXCEL From RPG applications

Probably not an RPG topic and more appropriate over in the
microsoft.public.excel.programming newsgroup thought you won't get any
as400/iseries flavor to the answers, but I'll comment anyway.

I'll assume you are running the macro (VBA Code) on a PC. If it really is a
MACRO (from version 4 excel before VBA code existed) your on your own.

It depends on what the macro is doing but a minute and half is forever
compared to what properly written VBA code should do.

The first thing the macro should do is turn off automatic calculation and
turn off screen updating. If calculation and screen updating is left on,
every time you put data in a cell, excel will have to check to see if that
cell is involved in a calculation. Having the screen refresh every time you
put data in cell is slow too if the sheet being written too has focus.

Use this as your first 2 lines of code

Application.ScreenUpdating = False
Application.Calculation = xlManual

Then have these as your last 2 lines

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

Make sure the last 2 lines happen even if there is a trapped error
condition.

regards

Steve Moland
Access Paths Inc
12 Parmenter Rd Unit C4
Londonderry NH 03053
603 845-0190 Ext 2
steve@xxxxxxxxxxx




"David Foxwell" <david.foxwell@xxxxxxxxx>
wrote in message news:6FB278720F464B71A687702C133C64B6@xxxxxxxxxxxx

Hi.

Our programs are increasingly being used to generate EXCEL spreadsheets
instead of reports. Until recently, we just called a program to copy the
i5 file to the IFS as a CSV file.

We now have a small JAVA application that converts the CSV to XLS.

Added to that, we use RPG and JAVA to send the file automatically by email
to the user.

Now I have an application that sends 3 files. The result is 3 different
spreadsheets. I'm having a few problems because I'm having to depend on
JAVA programmers when things don't work properly.

As an alternative, I have created a spreadsheet with a macro that imports
the 3 files as 3 sheets. That way the user only has one spreadsheet to
open.

I get the results without depending on any JAVA application and I have
complete control over the presentation, etc.

The problem : I'm generating around 20 000 lines and the macro takes a
minute and a half to work. This is quicker than using the JAVA application
but that runs in batch.

How do I get my spreadsheet to the user only after the macro has been
executed ?


TIA
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.