Linking Excel files on a website? 1

[mozingod]

I'm woundering if there is any way to "link" an Excel spreadsheet to a web site? So if there's a report stored on a share, can I make it so visitors can open the report without having to download and without needing the author to publish it, but updated whenever the spreadsheet is saved? This is for an intranet webpage, so everyone has Excel loaded and is running IE5+ if that helps at all. Also, the spreadsheets in question are all stored on the webserver already. Any help would be greatly appreciated!!

Darrell Mozingo

 

[DoubleG123]

Hi Darrell:

There are a few options at your disposal; however, I have used a particular solution that provided good results similar to what I believe you are seeking...

You can set up the Excel spreadsheet to automatically convert itself to HTML upon closing the workbook and save itself to the Web directory of your choice (or anywhere else for that matter) before closing the spreadsheet (See 'SaveAs HTML' results entered into Excel's VBA Help file and the example, below). That way, your users won't even need to have Excel on their machines -- just a browser. Of course, you can also have it save a copy of the original .xls file to the same directory for the users to retrieve.

[tt]ActiveWorkbook.SaveAs _
Filename:="C:\Reports\myfile.htm", _
FileFormat:=xlHTML[/tt]

The solution we came up with was to set up Excel templates to pull in remote data (as the template opened); perform functions on the data; then save itself (upon closing) as a Web page to the directory running on an IIS server. This was all kicked off periodically by the system scheduler. That way no one had to manually work with the data and we received nearly real-time results presented to the users. This will also work in presenting online graphs, Pivot tables and Pivot Charts, presented as Web pages.

 

[mozingod]

That sounds very promising. Did you put that code into a macro to run apon closing, or some other way? Thanks a lot for the tip, it sounds just what I was looking for.

Darrell Mozingo

 

[DoubleG123]

I put all of the code into VBA macros for the templates I used. The [tt]Workbook_Open()[/tt] event was where I put the call to reference a separate module to automate everything including [tt]ThisWorkbook.Close[/tt], after everything was done. By putting it into a separate module and calling it from the []Workbook_Open[] event you can re-use the code in the module in other Web reports by exporting the module or modules.


Glad this helped!

-Galen
(DoubleG123)

 

[mozingod]

This might be a bit over the edge to make it transparent to the users/authors of the spreadsheets, but is there a way to make it automatically over-write the saved .htm? I have the code going good, but it still pops up on closing the workbook to see if they want to over-write the old one, which I'd like it to do automatically somehow. I searched through the VB help file with no luck. Thanks for any help.

Darrell Mozingo

 

[DoubleG123]

No problem, Darrell...

Just add

Application.DisplayAlerts = False

before you do your "SaveAs" and then add

Application.DisplayAlerts = True

just before you close the workbook.

-Galen
(DoubleG123)

 

[mozingod]

Wow, that's great Galen, thanks! One last question... is there a way to block certain sheets in a workbook from being saved as html? Or only allow certain ones? I've never had the need to look into what VBA can do before now Thanks!

Darrell Mozingo

 

[DoubleG123]

Darrell:

Not only can you get down to converting certain worksheets into HTML, but you can even define ranges (as small as one cell) and use the "

Publish

" method as in the following Microsoft example...

ActiveWorkbook.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:="\\Server2\Q1\stockreport.htm", _
    Sheet:="First Quarter", _
    Source:="D5:D9", _
    HTMLType:=xlHTMLCalc).Publish

Cheers!

-Galen
(DoubleG123)

 

[mozingod]

A * for all that help. Thanks!!

Darrell Mozingo

 

[mozingod]

Hmm, happen to know why I'm getting this error:

Run-time error '1004':
Application-defined or object-defined error

when I run this code:

Application.DisplayAlerts = False

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceSheet, _
Filename:="C:\filename.htm", _
Sheet:="Sheetname", _
Title:="title&quot.Publish

Application.DisplayAlerts = True

?? When I debug the error it highlights that whole line. Sheetname is a valid sheet in the workbook. It's for Excel 2000 if that makes a difference.

Darrell Mozingo

 

[DoubleG123]

My guess is that your Worksheet Name isn't "sheetname". It's probably "Sheet1" or something else. That will hose it every time.

Just make sure you enter the correct name for the sheet you are referencing, or use the numeric equivalent for the Worksheet item in the collection.



-Galen
(DoubleG123)

 

[mozingod]

It's the right name. Would putting spaces in the name affect it at all?

Darrell Mozingo

 

[coachdan]

Spaces will affect it. VB does not like spaces. You should get used to using the underscore in place of spaces.

 

[mozingod]

Removing the spaces still gives the error unfortunately.

Darrell Mozingo

 

[DoubleG123]

Darrell:

I copied your code and pasted into Excel.

As long as my capitalization, and spaces were identical to what was referenced in the spreadsheet, I had no problems whatsoever in running the example.

If the Worksheet Name was different in any way from what is referenced in code, I got the same error that you reference above.

I did notice that as it is written, the HTML is appended with each update of the code. it doesn't overwrite the code as I thought you originally had intended.



-Galen
(DoubleG123)

 

[mozingod]

Well it's still not working for me. Could there be something else I need to install w/Excel?

Darrell Mozingo

 

[DoubleG123]

Darrell:

OK...Here's another thought for why you're receiving the error...

If your worksheet or workbook is protected...You will get the error you mentioned. If this is what's causing your issue, you have at least a couple of choices.

[ol]
[li]If possible, unprotect the entire Workbook or template since one the workbook is converted to HTML, the users won't be able to mess with it anyway.
[li]Temporarily unprotect the Excel worksheet/workbook using
"

ActiveWorkbook.Unprotect

" and "

ActiveWorkbook.Protect

" to surround the publishing activities, but realize that if someone else fires of this event as an automated process, their really isn't any protection this way. (And yes, you can use code to pass a password to unlock and relocak the workbook or template.)
[li]Extract the "protected data" from the workbook you have protected and run the automated Web-report generator in a different Excel template or spreadsheet.
[/ol]

If this doesn't work, their are still other avenues to explore. -- Keep smiling! It's always the things right in front of our noses that elude us.



-Galen
(DoubleG123)

 

[mozingod]

Unfortunately it's not protected, although one of the other workbooks I need to do this to is, so that information will be very valuable then. I realised that the sheet I was trying to publish was a chart, even though I used that sheet property. So as a test I pointed it to a normal sheet just with data, and now I get this error:

Run-time error '1004':
Method 'Publish' of object 'PublishObject' failed

with this code:

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceSheet,
Filename:="C:\Inetpub\

http://wwwroot\Plant\Metrics\ScheduleAccomplishment.htm",

_
Sheet:="2003 Schedule Accomplishment", _
Title:="Schedule Accomplishment&quot.Publish

Any ideas? BTW, I tried replacing the spaces with underscores and just removing them (both from the sheet and code) and it still gives me that error.

Darrell Mozingo

 

[DoubleG123]

OK Darrell:

In the source code you provided above...

ActiveWorkbook.PublishObjects.Add( _
  SourceType:=

xlSourceSheet

,
  Filename:="C:\Inetpub\[URL unfurl="true"]wwwroot\Plant\Metrics\ScheduleAccomplishment.htm",[/URL] _
  Sheet:="2003 Schedule Accomplishment", _
  Title:="Schedule Accomplishment").Publish

Change xlSourceSheet to xlSourceChart and see if that doesn't fix the problem.

By the way, if you want to have the outup overwrite the existing HTML file instead of continually appending it, just add 1 space and the word "true" after

.Publish

and that should fix it. As in:

Title:="Schedule Accomplishment").Publish True

-Galen
(DoubleG123)

 

[mozingod]

OK, using:

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceChart, _
Filename:="C:\Inetpub\

http://wwwroot\Plant\Metrics\ScheduleAccomplishment.htm",

_
Sheet:="2003 Schedule Accomplishment", _
Title:="Schedule Accomplishment&quot.Publish True

I get the "Application-defined or object-defined" error again.

Darrell Mozingo