Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Saving a Range as HTML (2003 SP1)

    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("Desk top")
    '
    Application.Goto Reference:="Grades"
    With ActiveWorkbook.PublishObjects("Grades_2005_Summer-1_1258")
    .HtmlType = xlHtmlStatic
    '.Filename = "Cocuments and SettingsSUUDesktopPage.htm"
    .Filename = strMyDocs & "page.htm"
    .Publish (False)
    .AutoRepublish = False
    End With

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving a Range as HTML (2003 SP1)

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

    <pre>With ActiveWorkbook.PublishObjects(1)</pre>


    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:
    <hr>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<hr>

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving a Range as HTML (2003 SP1)

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

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


    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

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Saving a Range as HTML (2003 SP1)

    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"?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving a Range as HTML (2003 SP1)

    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.

    <hr>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.<hr>

    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).

    <hr>2a) What did you put into your help text box to get that statement?<hr>
    I typed the phrase (divID) in the immediate window and hit <F1>

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

    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.

    <hr>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"? <hr>

    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

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Saving a Range as HTML (2003 SP1)

    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.

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving a Range as HTML (2003 SP1)

    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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving a Range as HTML (2003 SP1)

    That is what is in <post#=519,912>post 519,912</post#> . 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:
    <pre>Option Explicit
    Sub Test()
    Dim vName as Variant
    Dim strMyDocs As String
    strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desk top")
    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</pre>


    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving a Range as HTML (2003 SP1)

    Followup answer:

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

    <pre>Option Explicit
    Sub Test()
    Dim vName
    Dim strMyDocs As String
    strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desk top")
    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</pre>


    Steve

  10. #10
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Saving a Range as HTML (2003 SP1)

    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?

  11. #11
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Saving a Range as HTML (2003 SP1)

    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!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving a Range as HTML (2003 SP1)

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

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Saving a Range as HTML (2003 SP1)

    Sorry for not being clear.

    Yes my problem is solved.

    And yes ... thanks for the other cool tips too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •