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

    Save Range as Webpage (2003 SP1)

    I have a named range from within a worksheet. I would like to save the range only as a webpage. I require no interactivity on the outputted webpage.

    I'm unclear what my VBA statement would have to look like to do that. The range object and the saveas method don't seem to go together.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Range as Webpage (2003 SP1)

    You can only save a workbook, not a range. You will need a macro that creates a new workbook, copies the range to that workbook, and then saves the workbook in HTML format. If you want help with that macro, give us some more details like the range name, and what name and where you want it saved.
    Legare Coleman

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

    Re: Save Range as Webpage (2003 SP1)

    Hmmm.

    What I would do at my keyboard is select the range (named "test") from the drop-down list in the name box, click "File" and "Save as webpage", and my range would come up with a radio buttion for "Selection:Test". I'd click that radio buttion, enter my filename and click "Save".

    Are you saying that I can't do this (directly) with VBA? (If this is the case, the information you asked for is that the range is named "test" and can be saved as "C:test.htm".

    Am I correct in thinking that I can do this using the Macro Recorder (but that isn't advisable)?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save Range as Webpage (2003 SP1)

    <P ID="edit" class=small>(Edited by JohnBF on 10-Jun-05 13:59. Code correction.)</P>The macro recorder won't give you the flexibility you need, it hard codes too many things, it's inefficient, and will require a lot of work to get where you want.

    I hope Legare doesn't mind me jumping in, this doesn't do exactly what you want, but should get you started:

    Public Sub ExportRangeToHTM()
    Dim strFName As String
    Dim rngSource As Range
    Dim wbkTemp As Workbook

    Application.ScreenUpdating = False
    Set rngSource = Selection
    strFName = CStr(Application.GetSaveAsFilename(, "Web Page (*.htm; *.html), *.htm")) ' get a file name
    If strFName <> "False" Then
    Set wbkTemp = Workbooks.Add(xlWBATWorksheet) ' create a new workbook with one sheet
    rngSource.Copy wbkTemp.Worksheets(1).<!t>[A1]<!/t> ' copy the selection to cell A1 in the new workbook
    wbkTemp.SaveAs Filename:=strFName, FileFormat:=xlHtml ' save as HTML
    wbkTemp.Close ' close the new workbook
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Set rngSource = Nothing
    Set wbkTemp = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Save Range as Webpage (2003 SP1)

    Two questions:

    1) I can't get "Selection" to work. What should be my format for a cell reference or named range to go in there?
    2) Do I substitute a filename for the wildcard in *.htm?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Range as Webpage (2003 SP1)

    John's code should save whatever is selected when the macro is run as a .HTML file. If you want to save a specific range named "Test", then you can change this line:

    <pre> Set rngSource = Selection
    </pre>


    to

    <pre> Set rngSource = Range("Test")
    </pre>


    John's macro will display the stanard SaveAs dialog box to ask you for a file name to store the .HTML file. If you want to store to a specific file, then there are several things that will need to be changed. If that is what you want, then you will need to tell us the full path and file name where you want it stored.
    Legare Coleman

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

    Re: Save Range as Webpage (2003 SP1)

    I almost had that format, but not quite. Thanks.

    This does a good job of what I need (although the formatting of the resulting HTML page looked poor - is this a result of the macro or Excel's indifferent conversion of formats into HTML?).

    I would like it to save the range "Test" to C:Test.htm if that is possible.

    One other question: I intend to use this to periodically save two named ranges ... is there a way to embed two ranges with two output file names in this macro, or should I create twin macros for the conversion and a third one to call and run both of them?

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save Range as Webpage (2003 SP1)

    Makes it easy, though it becomes inflexible. This is barely tested code, I assumed the the named ranges were in the same worksheet in the same workbook, if not that will complicate things:

    Public Sub ExportRangeToHTM()
    Dim wbkTemp As Workbook

    Application.ScreenUpdating = False
    Set wbkTemp = Workbooks.Add(xlWBATWorksheet)
    ThisWorkbook.Worksheets(1).Range("Test1").Copy wbkTemp.Worksheets(1).[A1]
    wbkTemp.SaveAs Filename:="C:Test1.htm", FileFormat:=xlHtml ' save Test1 as HTML
    ThisWorkbook.Worksheets(1).Range("Test2").Copy wbkTemp.Worksheets(1).[A1]
    wbkTemp.SaveAs Filename:="C:Test2.htm", FileFormat:=xlHtml ' save Test2 as HTML
    wbkTemp.Close
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Set wbkTemp = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Save Range as Webpage (2003 SP1)

    Works like a charm. Thanks.

    P.S. I don't need something flexible - I've never figured out how to solve this problem before because it simply doesn't come up too much for me.

Posting Permissions

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