SaveAs

**slipperyjim** · 06-04-2009, 08:29 PM

Hi, i am trying to save a range on Sheet1 as a web page. i have managed to save the entire workbook as a webpage but am struggling to get the right syntax for just the range on sheet1. when i do this manually i select the range, set the print area, then choose file-save as web page- and choose selection:print area or republish if i have done this before. i am not sure also where to add the directory i wish to save it to. the file name always changes, so i can either: use the name of the file or i can use the same file name that is entered into a cell (cell never changes). i hope this makes some sort of sense. here is the code i used to save the entire workbook

myName = This Workbook.Fullname
myHtmlName = Left(myName,Len(myName)-4)&".htm"
ThisWorkbook.SaveAs Filename:=myHtmlName:=,FileFormat:=xlHtml

thanks for any help

**slipperyjim** · 06-04-2009, 09:22 PM

any tips? :|

**Paul** · 06-04-2009, 09:23 PM

Hello Jim, and welcome to the forum.

When I record the steps to save a range as a web page (html), I get the following code:

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Documents and Settings\Paul\Desktop\test.htm", "Sheet1", "$B$4:$C$16", _
        xlHtmlStatic, "Demo2(1)_22030", "")
        .Publish (True)
        .AutoRepublish = False
    End With

This is in Excel 2007, and I'm not sure if it has changed from 2003. Try recording it and see!

**slipperyjim** · 06-04-2009, 09:40 PM

Paul, thanks for the reply, i have done this also, however, when it is saved the filename will always change. see if i can better explain what happens.

Template: person builds the isolation procedure
when they have finished they click a "workbook save as" macro that
saves the workbook as an ordinary .xls file with the name being the same
contents as cell b35...the name in cell b35 could be DYAIES-001 then the
next time they use the template it could be DYBZES-100

Svaed As Workbook: After saving as to a directory etc, they then will need to click next
macro that will save a range as a single web page or whatever.
again, this is saved as the same name of the workbook or can be
the contents of cell b35

i can get the macro for saving the whole book working, just not saving that range from sheet1 as a web page with the name. i will try your code above and see if it will save the name each time it changes

**Paul** · 06-04-2009, 09:54 PM

To save the file with the value from cell B35, try changing

"C:\Documents and Settings\Paul\Desktop\test.htm"

to

"C:\Documents and Settings\Paul\Desktop\" & Range("B35").Value & ".htm"

**slipperyjim** · 06-04-2009, 10:12 PM

syntax error when stepping through

Sub saveaswebpage()



With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Documents and Settings\Paul\Desktop\" & Range("B35").Value & ".htm"
        xlHtmlStatic, "Demo2(1)_22030", "")
        .Publish (True)
        .AutoRepublish = False
    End With
End Sub

**slipperyjim** · 06-04-2009, 10:12 PM

i do realize i have to change directory also

**slipperyjim** · 06-04-2009, 10:16 PM

stepping through on the first code worked though

**slipperyjim** · 06-04-2009, 10:23 PM

ok, scratch that... now i have this code....

Sub saveaswebpage()



With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "J:\Isolation Data Base\IsolationWebsite\DryMillA\" & Range("B35").Value & ".htm", "Sheet1", "$A$34:$L$67", _
        xlHtmlStatic, "Demo2(1)_22030", "")
        .Publish (True)
        .AutoRepublish = False
    End With
End Sub

runtime error at .publish (true) line

**slipperyjim** · 06-04-2009, 10:55 PM

nevermind i got it

many thanks for your help, it really is greatly appreciated...the final code is...

Sub saveaswebpage()



With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "J:\Isolation Data Base\IsolationWebsite\DryMillA\" & Range("D21").Value & ".htm", "Sheet1", "$A$34:$L$67", _
        xlHtmlStatic, "Demo2(1)_22030", "")
        .Publish
        .AutoRepublish = False
    End With
End Sub

SaveAs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

SaveAs

re: SaveAs

re: SaveAs

re: SaveAs

re: SaveAs

re: SaveAs

re: SaveAs

re: SaveAs

re: SaveAs

Re: SaveAs

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions