Solved

Run VBA macro to drive Excel report on intranet

Posted on 2011-02-16
9
440 Views
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?

Thanks,

taduh
0
Comment
Question by:taduh
  • 5
  • 4
9 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 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.
0
 

Author Comment

by:taduh
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.
0
 

Author Comment

by:taduh
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.
0
 
LVL 22

Expert Comment

by:rspahitz
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.

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:taduh
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.....

Thanks,

taduh
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34998811
Also, try checking this is the VB immediate window:

?ActiveWorkbook.Names.Count
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
0
 

Author Comment

by:taduh
ID: 35017457
rspahitz,

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.

taduh
0
 

Author Closing Comment

by:taduh
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.
0
 
LVL 22

Expert Comment

by:rspahitz
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 :)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now