Question

Adding data from DB2 to Excel — The iSeries blog  |   Apr 14 2008  4:14PM GMT

[…] Ljjk122 posted this question on ITKE: I want to add data from a DB2 file to an Excel template that has a header row and totals certain columns without overwriting the header row or the totals. […]

TSCHMITZ  |   Apr 16 2008  12:19PM GMT

Hi,

perhaps you should investigate into a different approach.

I should do that the following way if i were you :
1) Record a first VB Script inside your excel worksheet that stores all the command required to set your header line the way you want. Normally , Macro -> Record new macro -> use F2 upon every header field without modifying and after header finished -> Macro -> Stop recording.
2) Check your macro code and rename it as whished. I assume You call this Macro HEADER_FORMAT
3) For the Total , simply use the same way to record a new Total Formatting Macro, but do not forget to start macro recording before positioning your cursor inside the first field.
4) Check your Macro code and rename it as wished. I Assume you call this MAcro TOTAL_FORMAT
5) Clear completely this sheet.
6) Import Your data inside this empty excel sheet
7) Run MAcro HEADER_FORMAT to get YOUR header
Now this is time to modify your TOTAL_FORMArt macro to fit actual number of records, any value this number is and will be.

Assuming you need to Summarize column B lines 2 to 7 at cell(B8), your macro code should be like

Range("B8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"

Place the following lines in the top of your TOTAL_FORMAT macro

Dim lrow As Long
Dim xlrow As String
lrow = ActiveSheet.UsedRange.Rows.Count
xlrow = lrow

This will store the number of used lines, header included, inside variables lrow and xlrow.

Now modify the lines

Range("B8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"

to

Range_id = ("B" & xlrow)
Range(Range_id).Select
ActiveCell.Formula = "=SUM(R[" & -lrow + 1 & ">C:R[-1]C)"

This will put the cursor in column B above last data line and create a total from Row 2 to lrow stored and B(lrow+1).

That’s it.
Just do the same with other columns.

9)Now save your file (allways before testing) and run macro TOTAL_FORMAT.

This should do the tric.

Now every time you want to update your file.
1) Clear the Sheet
2) Run HEADER_FORMAT macro.
3) Import your data
4) Run TOTAL_FORMAT.

The final solution could go really further with automatization of all the process with one single button, doing clear, import, formatting and many more if required. (Pivot table…..)

If you need a more sophisticated solution example, just send a private mail to me (my ID is TSCHMITZ) with your email and name, and i’ll contact you as soon as i can

I hope i may help you.

Thierry Schmitz.