General discussion

Locked

Dynamic Rpt_Worksheet Change Event

By lacwill ·
Currently, I have an Excel Spreadsheet that I plan to populate via a MS Query that "pulls" data from an Oracle Database (version 8.1.7). In essence, the spreadsheet is setup to provide a "dynamic" Variance Report that will be used to monitor underpaid customer accounts. The report will be refreshed daily.

The format of the columns is as such:

Acc--OrigBillDt--DtOf1stPymt--DtofLastPymt--ExpPymt--TotPymt
X25--12435-------01-Dec-2004--10-Dec-2004--$1000.00--$250.00
X25--12455-------05-Dec-2004--15-Dec-2004--$2000.00--$150.00


Additional columns on the main worksheet would be as follows:

DateFirstDisplayedOnMainWksht-----No.OfDaysOnMainWorksheet
15-Dec-2004-----------------------14 days
20-Dec-2004-----------------------9 days

Note, the "DateFirstDisplayedOnMainWksht" would be the date that the Oracle database was first updated with the initial customer payment.

Currently, the MS Query is configured so that only accounts that have a ratio of TotPymts to ExpPymts of less than 75% will be displayed on the main worksheet. What I would appreciate is two additional worksheets - Worksheet2 to display all accounts that "dropped off" of the main worksheet during the prior week, two weeks, etc. as a result of additional payments received from the customer(s) and Worksheet3 to display all accounts that were added to the main worksheet during the prior week.

WorkSheet2 and Worksheet3 should contain a column that displays the date that the account was "dropped" or "added" from/to the main worksheet.

Additional criteria desired in the SQL script for the MS Query is as follows:

"Balance > 0"

I assume that the worksheet_change event would be used.

Any idea as to the VBA coding needed that will populate worksheets 2 and 3??

Is this possible?

Thanks in advance.

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

hmmm

by awfernald In reply to Dynamic Rpt_Worksheet Cha ...

Pull data down into a temp xls, then treat each different spreadsheet as it's own database, do a row vs. table compare to find if record exists, if it does, then update your current (spreadsheet 1), if not, then update spreadsheet 2. Place date on spreadsheet 2 of when updated.

You will actually need to run the validation versus your spreadsheet 2 FIRST to be able to pull out any records that had dropped off, but then been brought up to date.

Hmmm, does this really make any sense?

If you need more info, send me an email and I'll try to explain better.

Back to Windows Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums