I want only the active sheet to publish, not the whole workbook

**Jcush80** · 12-19-2012, 01:32 PM

The whole workbook is huge and has proprietry/personal information on it so I cannot provide much for demonstrations. What I can provide is the macro I recorded.

I want the macro to take the activesheet (today it is Wednesday 12-19-12) and publish it as "daily assignments.htm". I need only the active sheet published, and the workbook can stay the same. Everytime the workbook is saved I want the active sheet published.

The macro was created when I though I would need to move the sheet to a new workbook. if that is not needed I can easily take that part out. The bulk of the issue is publishing the active sheet. I tried changing the sheet name to "ActiveSheet" and that was unsuccessful.

Thank you all for you input.

Jay

Please Login or Register  to view this content.

**xladept** · 12-19-2012, 02:16 PM

Hi Jcush80,

Check out the Publish Method in Help.

**Jcush80** · 12-19-2012, 02:47 PM

xladept,

Thanks for the reply, I fear I am missing something, I do not see publish method in the help directory. Would it be called something different in excel 2010?

What I did see was about publishing to sharepoint. What I was trying to do was more save as .htm. I need the original workbook to stay the same and the active sheet to be saved to the .htm file.

**xladept** · 12-19-2012, 03:24 PM

Remarks
The FileName property returns or sets the location and name of the HTML file.

Publish method as it applies to the ListObject object.

Publishes the ListObject object to a server that is running Microsoft Windows SharePoint Services. Returns a String which is the URL of the published list on the SharePoint site.

expression.Publish(Target, LinkSource)
expression Required. An expression that returns a ListObject object.

Target Required Variant. Contains an Array of Strings. The following table describes the elements of this array:

Element# Contents
0 URL of SharePoint server
1 ListName (Display Name)
2 Description of the list. Optional.

LinkSource Required Boolean.

Remarks
If the ListObject object is not currently linked to a list on a SharePoint site, setting LinkSource to True will create a new list on the specified SharePoint site. If the ListObject object is currently linked to a SharePoint site, setting LinkSource argument to True will replace the existing link (you can only link the list to one SharePoint site). If the ListObject object is not currently linked, setting LinkSource to False will leave the ListObject object unlinked. If the ListObject object is currently linked to a SharePoint site, setting LinkSource to False will keep the ListObject object linked to the current SharePoint site.

Example
This example saves the range D5:D9 on the First Quarter worksheet in the active workbook to a Web page named stockreport.htm. The Spreadsheet component is used to make the Web page interactive.

Please Login or Register  to view this content.

The following example sets elements of the Target parameter to publish the ListObject object to the SharePoint site named "MyServer". In this example, it is assumed that the ListObject object is not currently linked to another list source, so setting the LinkSource argument to True creates a new two-way link between this list and the list on the target SharePoint site.

Please Login or Register  to view this content.

**Jcush80** · 12-19-2012, 03:42 PM

Ok, Thanks I'll try some different things and let you know if they work. If not I may just create an excel sheet I can actually share.

Jay

**xladept** · 12-19-2012, 03:49 PM

Good Luck!

**Jcush80** · 12-21-2012, 12:13 PM

Here is the code I ended up using and so far it is working. I have to test it out on a couple of different machines and users but everytime I open and save the file it works. If someone sees a problem with the code let me know. I tried using BeforeSave and that was much less effective.

Please Login or Register  to view this content.

**xladept** · 12-21-2012, 02:24 PM

That looks like a nice work-around, glad that you resolved your issue!

**Jcush80** · 12-21-2012, 02:32 PM

I have made a few adjustments once I have tried it on other machines. First I went back to BeforeSave and made a few other alterations. It works better now. Thanks for your help, it spurred some additional thinking that helped me realize the solution.

**xladept** · 12-21-2012, 02:36 PM

You're welcome!

I want only the active sheet to publish, not the whole workbook

LinkBack

Thread Tools

Rate This Thread

Display

I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Re: I want only the active sheet to publish, not the whole workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions