Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Run VBA macro to drive Excel report on intranet

Posted on 2011-02-16
Medium Priority
Last Modified: 2012-05-11
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?


Question by:taduh
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 22

Accepted Solution

rspahitz earned 1500 total points
ID: 34907973
Not sure about the problem but I would suggest setting a breakpoint on the first line of the button event procedure and see if it even gets there.
If not, the link to the code is probably not working; if it gets there, you can step in to investigate exactly which line goes wrong, then maybe ask more questions about why that line fails.

Author Comment

ID: 34909041
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.

Author Comment

ID: 34997791
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.  Excel range box
Appreciate any help anyone can offer.
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

LVL 22

Expert Comment

ID: 34998562
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.


Author Comment

ID: 34998741
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.....


LVL 22

Expert Comment

ID: 34998811
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

Author Comment

ID: 35017457

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.


Author Closing Comment

ID: 35017529
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.
LVL 22

Expert Comment

ID: 35018526
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 :)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question