• Save Range as Webpage (2003 SP1)

    Author
    Topic
    #420591

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #952813

      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.

      • #952827

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

        • #952866

          (Edited by JohnBF on 10-Jun-05 13:59. Code correction.)

          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).[A1] ‘ 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

          • #952868

            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?

            • #952869

              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:

                 Set rngSource = Selection
              

              to

                 Set rngSource = Range("Test")
              

              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.

            • #952876

              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?

            • #952878

              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

            • #952882

              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.

    Viewing 0 reply threads
    Reply To: Save Range as Webpage (2003 SP1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: