Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Hi All, I am trying to run a macro when a report procedure i...


Klaus Opitz 2

Recommended Posts

Hi All, I am trying to run a macro when a report procedure is executed (currently from App Studio 8202). I have recreated the issue using a very simple example. The macro should run when the spreadsheet opens (which it does) as well as when I edit a particular cell (which it doesnt). When I open the template directly, the macro runs as expected when I edit that cell. I feel like I am missing something very simple. If I dont populate the sheet with the CAR data, then the macro gets triggered when I edit the cell. Any thoughts would be appreciated.
Link to comment
Share on other sites

Not quite. Let me try to restate the issue. If I open the excel workbook, the macro runs when I edit a given cell. If I use a WF procedure to populate the same template and then edit the cell, the macro never gets triggered. So there are 2 things happening here. First, run a macro when the spreadsheet opens. This works in both cases. Then run a macro when a specific cell is changed. This works when I open the spreadsheet directly, but not when I run it through WF.
Link to comment
Share on other sites

Heres the problem. When you use the template in an WebFOCUS Procedure and populate sheet 1, everything in sheet 1 is overlaid. Anything you had tied to that cell is now gone. What you have to do is put the data from WebFOCUS on sheet 2 and use a macro or formulas in sheet 1 to use the data to sheet 2. Your report is in sheet 1 but WebFOCUS populates sheet 2.

I do this with a report my users developed with a bunch of graphs. As they requested I gave them a summary report. They then added 7 or 8 graphs below the data on the same sheet. Next time I ran the report, they copied and pasted the data so the original workbook so the graphs would update. I found out about this by accident and told them to send me their spreadsheet. I created an Excel template with 2 sheets, the first with the graphs and the second for the data. I modified the graphs to point the sheet 2 and now when I run the report, sheet 1 has the updated graphs and sheet 2 has the data. WebFOCUS completely replaces everything in sheet 2 but leaves sheet 1 alone.

Link to comment
Share on other sites

Hi John. Thanks for the reply. I was hoping that although the data was being overlaid, the macro would be retained, but it sounds like that is not the case. So, let me see if I follow. I would have 2 sheets and have WebFocus populate the second sheet. When the report runs, the same data would have to be copied to the first sheet (not sure how to do that maybe a macro as well). The first sheet would already have the macro I had created to catch a cell change and then trigger another cell to be updated. So the users would be working from sheet 1 and sheet 2 would also need to be hidden from them, somehow as well to avoid confusion.
Link to comment
Share on other sites

There are several ways to do this, heres one. Create a .xltm file with 2 sheets. You have an auto open macro. Part of the auto open macro will call a second macro to copy the needed data from sheet 2 to sheet 1. Once that is done, you can delete the data in sheet 2. Leave the sheet, just delete the data. Now when you trigger the refresh you just repopulate sheet 2 with an excel web query. The excel web query will run just the data and save it PCHOLD. That should load the data left-to-right, top-down stating with the cell that has the web query in it.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...