Add to Excel spreadsheet from System i

5 pts.
Tags:
DB2 Everyplace
Microsoft Excel
System i
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ljjk122
    [...] 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. [...]
    0 pointsBadges:
    report
  • TSCHMITZ
    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 8) 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.
    470 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following