How to capture a Field that moves

[Mark Starnes]

I've got Monarch v5 and have a report where one of the header fields is centered in the report. Depending on the contents, the text can be in a different position. However, it always starts the same. Here's a sample...

E X P E N S E A N A L Y S I S 18221 - Telephone - New

E X P E N S E A N A L Y S I S 18222 - Telephone - Used

The field I'm interested in the # (18221 & 18222). Although it doesn't show in my post, the word Expense is not lined up in the report (nor is the rest of the line). Is this possible?

Thanks for your help!

[Data Kruncher]

Mark,

Since v5 doesn't have a floating trap, we want to trap the whole line as one Character field that we can work with.

I'm assuming (uh-oh ;) ) that each line you want to capture begins with the phrase "E X P E N S E A N A L Y S I S ".

Define the whole line as one field called, say, AcctHdg. Experiment a bit with the trap until you can successfully preview your template. Hard to say what will work without seeing a larger sample of your report.

Now in the Table view, create a calculated field named AccountNum. I'm assuming (again!) that your account numbers are all 5 digit.

The formula for this field is Substr(LTrim(AcctHdg),30,5), where 30 is the length of "E X P E N S E A N A L Y S I S " (including the space at the end), and 5 is the length of your account number.

That ought to do it. Please let us know if this works for your needs.

Regards,
Sandy

[Mark Starnes]

Thanks Sandy! That does exactly what I was looking for. I'm fairly new to Monarch, but I'm pretty excited about what I see. I'm going to make a separate post, but do you know of some sort of general reference for automating Monarch through VBA? I'm somewhat proficient with Excel VBA so I'm not starting completely at the ground level.

Thanks again,

Mark



Originally posted by Data Kruncher:
Mark,

Since v5 doesn't have a floating trap, we want to trap the whole line as one Character field that we can work with.

I'm assuming (uh-oh ;) ) that each line you want to capture begins with the phrase "E X P E N S E A N A L Y S I S ".

Define the whole line as one field called, say, AcctHdg. Experiment a bit with the trap until you can successfully preview your template. Hard to say what will work without seeing a larger sample of your report.

Now in the Table view, create a calculated field named AccountNum. I'm assuming (again!) that your account numbers are all 5 digit.

The formula for this field is Substr(LTrim(AcctHdg),30,5), where 30 is the length of "E X P E N S E A N A L Y S I S " (including the space at the end), and 5 is the length of your account number.

That ought to do it. Please let us know if this works for your needs.

Regards,
Sandy

[Data Kruncher]

You can take a look at this fairly recent thread (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000312#000003) for the basics of automating Monarch with VBA.

Looking forward to your new post.

Glad to hear that you have a solution.

Sandy

[ May 01, 2006, 05:38 PM: Message edited by: Todd Niemi ]