Link to home
Start Free TrialLog in
Avatar of taduh
taduhFlag for United States of America

asked on

Run VBA macro to drive Excel report on intranet

Hi Experts,

I have written a VBA module which creates a report on a spreadsheet, based on the user's selection of criterion from 3 dropdowns. The macro is triggered by a click on a button which appears on the spreadsheet. The program resides on our reports server and works perfectly in native Excel, but it does not run correctly when I connect to it via a link from our intranet. It does not give any errors - it just does not do what it's supposed to do when the button to create the report is clicked. Our intranet software is BancWorks.

Can anybody give me any insight on how to solve this issue or tell me whether an Excel VBA macro can even be run from an internet website?


Avatar of rspahitz
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of taduh


Good suggestion rspahitz! I know it's actually executing the code, because some of the report items get filled in, but it seems to be haphazardly determining what gets put on the report. I'll give your suggestion a shot and report back. Thanks.
Avatar of taduh


Sorry I took so long to get back to this - some other things came up.

I set a breakpoint at the first line of code and it failed there. The line of code appears below:

brkrcolnum = Range("bpoonrpt_data_brkrcol").Value

This range,  bpoonrpt_data_brkrcol, is a named range on the spreadsheet which contains a value of 1. When I try to run the line of code, it fails with this error:

Run time error '1004':
Method 'Range' of object '_Global' failed.
I think it's getting this error because on the webpage that the Excel report comes up on does not have the little dropdown box that Excel has on top of the data grid to the left of the function box (see attached .bmp image below - the range box currently  shows A2) that shows the defined range names.

That dropdown box only appears when I click somewhere on the webpage, but then it disppears again the moment I try to run a line of code.  User generated image
Appreciate any help anyone can offer.
I haven't tried using Excel on the web but a few thoughts...

1) first see if you can go to the named cell like you would on the desktop version: copy the name from your VB code and paste it into the name box where the A2 is and see if it goes to the right cell (you may need to switch to a different cell first to see if it properly jumps to the right place.  If not, then it's not saved as part of the sheet and you'll need to make sure it is added to it.

2) you may need to create your own reference sheet where maybe you put a list of named ranges and a list of values that they represent.  can you put those on a hidden sheet?  then just navigate down the list (or use a vlookup to do the work for you) and grab the corresponding cell reference.

Avatar of taduh


This is the first time i've actually created a report that tried to run VBA code behind the spreadsheet on the web. The range names are there in the dropdown box, when you can see it, but the code, for some reason - probably due to interaction with the web, can't seem to recognize them.

Your second suggestion might be an option for me to look at. I'll have to look to see if that might work. Stay tuned.....


Also, try checking this is the VB immediate window:

If it comes back zero then the sheet might have lost the named cells; if not zero, then the name is not what you think it is and you can check something like this for what it thinks the names are:

for i=1 to ActiveWorkbook.Names.Count:?ActiveWorkbook.Names(i).Name:next
Avatar of taduh



I finally got it. For whatever reason, the report needed some sort of user interaction to force it to refresh the screen completely, so I simply added a Msgbox stating that the report was created successfully. When the user clicks OK on the Msgbox prompt, the report refreshes completely.

In order to avoid errors trying to print the report, I also had to substitute the Application.Dialogs(xlDialogPrint).Show for Application.Dialogs(xlDialogPrinters).Show, which was advantageous in that Application.Dialogs(xlDialogPrint).Show goes ahead and prints the report once you've selected the printer and clicked on OK, whereas Application.Dialogs(xlDialogPrinters).Show requires a line of VB code following it to print the report.

I'm going to award you the points because you got me thinking in a way that allowed me to resolve the problem. As silly as it sounds, since my report was running on a webpage, I would not have thought it would have shown up when I brought up Microsoft Visual Basic. Since you suggested it, I was able to make changes to the code and immediately see the impact. In order to do this though, I had to hit the run arrow in the Debugger or click the buttons on my report to run the code staright through. When I tried to step through the code line by line, it errored on every line even though nothig was wrong with the code.

Thanks for helping me think outside the box.

Avatar of taduh


rasphitz' response made me aware that the VBA code would show up in the Excel VB window even though the macro that generated the report wasrunning on a web page.

I was not aware of this.

It allowed me flexibility in debugging the problem and ultimately finding the solution.
Glad you figured it out.  Sometimes things that seem obvious but don't work require that out-of-the-box thinking and it's easy to forget when you're knee deep in the marsh :)