Saving a Range as HTML (2003 SP1)

Topic

New Reply

With help from the lounge I developed a macro that will go to a named range in a workbook and export it as a (non-interactive) HTML file to my desktop. The code is below. What I am wondering is, if I can get Excel to go to the named range “Grades”, why does it need the string “Grades_2005_Summer-1_1258” in the next line. That string is the name of the file with the _1258 appended – but I am not sure why.

So, what I am wondering is:
1) How do I generalize this code to work with other files? Currently, if I delete the reference to the file name, the macro fails.
2) What is the purpose of the (false) after publish. Does this have to do with interactivity?

Dim strMyDocs As String
strMyDocs = CreateObject(“WScript.Shell”).SpecialFolders(“Desktop”)
‘
Application.Goto Reference:=”Grades”
With ActiveWorkbook.PublishObjects(“Grades_2005_Summer-1_1258”)
.HtmlType = xlHtmlStatic
‘.Filename = “C:Documents and SettingsSUUDesktopPage.htm”
.Filename = strMyDocs & “page.htm”
.Publish (False)
.AutoRepublish = False
End With

Reply | Quote

Replies

1)If you only have 1 “publish object” you can just use:

With ActiveWorkbook.PublishObjects(1)

If you don’t know the collection number, you will have to use its “name”(ie unique identifier) which is in the DivID property.

2) from help:
[indent]

---

This argument is used only with a PublishObject object. If the HTML file exists, setting this argument to True replaces the file, and setting this argument to False inserts the item or items at the end of the file. If the file does not exist, then the file is created regardless of the value of the Create argument

---

[/indent]

Steve

Reply | Quote

Steve (reply to both messages):

I am confused by much of both responses.

1) When you say “If you only have 1 ‘publish object’ you can just use”, do you mean 1 publishObject within the macro, or withint the “With loop”? I will generally want to publish as a web page more than 1 named range from each worksheet.
2a) What did you put into your help text box to get that statement?
2b) Does that imply that if I do use PublishObject(1) that I shouldn’t have a .publish(*) in there at all?
3) What does it mean when you say “if the ‘publish object’ has not yet been created in the workbook”? I’m not sure what I would even do to do this, or whether I am doing it already. I have a workbook with 3 sheets, with 2 named ranges each, and I periodically save those ranges as a web page. Does this create a “publish object”? If I finish this macro will that constitute creating a “publish object”?

Reply | Quote

just a caveat and admission. This is the first I played with these. They are not available in XL97 and I just got XL2002 last month. So this is a little new to me.

[indent]

---

1) When you say “If you only have 1 ‘publish object’ you can just use”, do you mean 1 publishObject within the macro, or withint the “With loop”? I will generally want to publish as a web page more than 1 named range from each worksheet.

---

[/indent]

Neither. The “publishobject” is a collection in the workbook. An item is added to the collection automatically (by excel) when you “save as” a web page. If it has been created already you can refer to the item in the collection (by a its name or collection number) just like other collections (as I list in the code). If you are starting with a new book, they must be created first (the second code will do this). If you “record a macro” you will not get the (automatic) code where excel adds an item to the collection. So if you do it you are going to use VB you will have to create this code yourself (as I have done).

[indent]

---

2a) What did you put into your help text box to get that statement?

---

[/indent]
I typed the phrase (divID) in the immediate window and hit

[indent]

---

2b) Does that imply that if I do use PublishObject(1) that I shouldn’t have a .publish(*) in there at all?

---

[/indent]

I don’t understand the question. I thought you wanted to “publish” it. As I mentioned, the code in my 2nd response, essentially does the same thing as file save as web. If that is what you are after, that code should work. If you are after something else, you will have to clarify your response.

[indent]

---

3) What does it mean when you say “if the ‘publish object’ has not yet been created in the workbook”? I’m not sure what I would even do to do this, or whether I am doing it already. I have a workbook with 3 sheets, with 2 named ranges each, and I periodically save those ranges as a web page. Does this create a “publish object”? If I finish this macro will that constitute creating a “publish object”?

---

[/indent]

As mentioned, if you do a save as web page (via the toolbar), it will be done for you. If you are not doing this (or have not done this) and are doing this “save as web page” via VB you will have to explicitly create the object (like I do by Adding it to the collection).

You can look at the number of publishobjects (as you can look at any collection) in the immediate window by typing:
?activeworkbook.publishobjects.count

You can also write code to look at the properties of the collection list them out etc.

By looking in help for the publishobjects you can select the properties and methods and see examples of the code. (I modified its code to meet your example).

Hope this helps,
Steve

Reply | Quote

This is not helping.

Don’t give up on this thread – I have a lot of programming experience, but not in Excel macros. For me, the language structure is making a lot more sense than “the words”.

Let me go back to the first post.

My core problem is that I have a macro that worked in one workbook but that I am having trouble generalizing to other workbooks. This appears to be related to the assignment of a name to the collection I want to publish.

Ultimately, my problem may be simpler than that. What I want to do is automate the periodic exporting of a range (whose size may change) to an HTML file. The range is already named for another purpose – I don’t have to use the name, but it is there if it is more convenient to use it.

This is an easy (if tiresome) thing to do with a keyboard:
1) Highlight the range
2) Click File
3) Click Save As Webpage
4) Click Selection
5) Click my desired location in the Places Bar
6) Set an appropriate filename
7) Click save.

My problem is that I do this a lot. It is easy to record as a macro, but I want a macro that I can generalize to more than one workbook.

Reply | Quote

That is what is in post 519,912 . In the code the “saveAs location” is hardcoded to be the filename “Page.htm” on the desktop.

If you want to save to a different location, you can prompt the user at runtime with getsaveasfilename:

Option Explicit
Sub Test()
   Dim vName as Variant
   Dim strMyDocs As String
   strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")
   strMyDocs = strMyDocs & "page.htm"
   vName = Application.GetSaveAsFilename( _
      InitialFileName:=strMyDocs, _
      fileFilter:="Web Page (*.htm; *.html), *.htm;*.html")
   If vName = False Then
      MsgBox "Cancelled - No filename chosen"
      Exit Sub
   End If
   With ActiveWorkbook.PublishObjects.Add( _
      SourceType:=xlSourceRange, _
      Filename:=vName, _
      Source:="grades", _
      HtmlType:=xlHtmlStatic)
      .Publish (False)
      .AutoRepublish = False
   End With
   MsgBox "Web page saved as:" & _
      vbCrLf & vName
End Sub

This is essentially what i posted earlier but asks for the filename to save it as (I used page.htm on the desktop as the default, change or eliminate this option).

Steve

Reply | Quote

Reply to Steve’s 2 Most Recent Posts:

I ran the code in 519,912 and I got a “1004” run-time error. I assumed that this was from my not understanding what was in the code. I still don’t understand all of it, but I think I have led you down the wrong path.

Unfortunately, the de###### was highlighting the entire With *.*.*() sequence and I couldn’t figure out where in there the problem was. I’ve done that now, and it seems to work just fine.

Having said that, I learned a lot of cool stuff from this thread. Thanx all!

Reply | Quote

So is this what you are after, or do you still have questions on it?

Steve

Reply | Quote

Sorry for not being clear.

Yes my problem is solved.

And yes … thanks for the other cool tips too.

Reply | Quote

Followup answer:

If you do not want to always save the range “Grades” you can save the “selection” with this code:

Option Explicit
Sub Test()
   Dim vName
   Dim strMyDocs As String
   strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")
   strMyDocs = strMyDocs & "page.htm"
   vName = Application.GetSaveAsFilename( _
      InitialFileName:=strMyDocs, _
      fileFilter:="Web Page (*.htm; *.html), *.htm;*.html")
   If vName = False Then
      MsgBox "Cancelled - No filename chosen"
      Exit Sub
   End If
   With ActiveWorkbook.PublishObjects.Add( _
      SourceType:=xlSourceRange, _
      Filename:=vName, _
      Sheet:=Selection.Parent.Name, _
      Source:=Selection.Address, _
      HtmlType:=xlHtmlStatic)
      .Publish (False)
      .AutoRepublish = False
   End With
   MsgBox "Selection saved as Web page. Saved as:" & _
      vbCrLf & vName
End Sub

Steve

Reply | Quote

Another option if the “publish object” has not yet been created in the workbook, is to create it:

   Dim strMyDocs As String
   strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")
   With ActiveWorkbook.PublishObjects.Add( _
      SourceType:=xlSourceRange, _
      Filename:=strMyDocs & "page.htm", _
      Source:="grades", _
      HtmlType:=xlHtmlStatic)
      .Publish (False)
      .AutoRepublish = False
   End With

I think this should be “equivalent” to file save as webpage and saving the range named grades onto the desktop with the name page.

Steve

Reply | Quote

I’m coming late to this, (so if this has already been covered in previous threads – ignore it) – is part of your question how to obtain the active workbook name?
if so:
strCurrFileName =ActiveWorkbook.Name

Reply | Quote

Reply to Catherine:

No, that isn’t part of my problem (that I know of). But … that is a useful bit of code. I have a dreadful time finding things like that when I need them.

Care to suggest a reverse dictionary for Excel VBA code?

Reply | Quote