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

[Klaus Opitz 2]

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.

[John Gelona]

So the macro automatically run when the workbook is opened, but you also want it to run when you edit a given cell Is that correct. If so, then you want an event driven macro. The following link has seems to be pretty good at describing event driven macros: https://trumpexcel.com/vba-events/

[Klaus Opitz 2]

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.

[John Gelona]

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.

[Klaus Opitz 2]

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.

[John Gelona]

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.