X
xphile
I have multiple worksheets in a single workbook and I want to have a
button that will publish the workbook to a single file web page (*.mht)
without leaving .xls spreadsheet. I have 2 problems, well 3. The code
works on one of my sheets but when I copy the code to an IDENTICAL
sheet, it will either not run and error out on the .publishobjects(1)
line or it will run and only publish the first sheet "Financial
Summary". In the .mht file, I don't want the file to function as it
should only be for viewing by browser. I am at a loss....
(There are currently alot of ' statements as I have been working on
why this doesnt work) I have a sheet where all the code works
flawlessly everytime but I don't know why. Here is the code I am
working with:
Dim fn, savefn As String 'file name
Dim bfn As String 'base name of workbook
Dim fileSaveName
Dim fs 'file system object
'Sheets(Array("Technical Report", "2005 IS Recovery Rates &
Data")).Select
'ActiveWindow.SelectedSheets.Visible = False
'Sheets("Financial Summary").Select
'ActiveSheet.Shapes("CommandButton1").Visible = False
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
'Sheets(Array("Financial Summary", "labor", "hardware")).Select
Set fs = CreateObject("Scripting.FileSystemObject")
fn = Replace(ActiveWorkbook.Name, ".", "-")
fn = Replace(fn, "-xls", ".xls")
savefn = fn
bfn = fs.GetBaseName(fn)
'Title = fn
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=fn, _
fileFilter:="Single File Web Page (*.mht), *.mhtl", _
Title:="Publish as Single File Web Page")
If fileSaveName <> False Then
Range("Print_Area").Select
With ActiveWorkbook
With .WebOptions
..RelyOnCSS = False
..RelyOnVML = False
End With
With .PublishObjects(1)
..Publish (True)
..AutoRepublish = False
..HtmlType = xlHtmlStatic
..Filename = fileSaveName
..Publish (True)
..AutoRepublish = False
End With
End With
'ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False
'Sheets("Technical Report").Visible = True
'Sheets("2005 IS Recovery Rates & Data").Visible = True
'ActiveSheet.Shapes("CommandButton1").Visible = True
Range("a1").Select
End If
End Sub
button that will publish the workbook to a single file web page (*.mht)
without leaving .xls spreadsheet. I have 2 problems, well 3. The code
works on one of my sheets but when I copy the code to an IDENTICAL
sheet, it will either not run and error out on the .publishobjects(1)
line or it will run and only publish the first sheet "Financial
Summary". In the .mht file, I don't want the file to function as it
should only be for viewing by browser. I am at a loss....
(There are currently alot of ' statements as I have been working on
why this doesnt work) I have a sheet where all the code works
flawlessly everytime but I don't know why. Here is the code I am
working with:
Dim fn, savefn As String 'file name
Dim bfn As String 'base name of workbook
Dim fileSaveName
Dim fs 'file system object
'Sheets(Array("Technical Report", "2005 IS Recovery Rates &
Data")).Select
'ActiveWindow.SelectedSheets.Visible = False
'Sheets("Financial Summary").Select
'ActiveSheet.Shapes("CommandButton1").Visible = False
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
'Sheets(Array("Financial Summary", "labor", "hardware")).Select
Set fs = CreateObject("Scripting.FileSystemObject")
fn = Replace(ActiveWorkbook.Name, ".", "-")
fn = Replace(fn, "-xls", ".xls")
savefn = fn
bfn = fs.GetBaseName(fn)
'Title = fn
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=fn, _
fileFilter:="Single File Web Page (*.mht), *.mhtl", _
Title:="Publish as Single File Web Page")
If fileSaveName <> False Then
Range("Print_Area").Select
With ActiveWorkbook
With .WebOptions
..RelyOnCSS = False
..RelyOnVML = False
End With
With .PublishObjects(1)
..Publish (True)
..AutoRepublish = False
..HtmlType = xlHtmlStatic
..Filename = fileSaveName
..Publish (True)
..AutoRepublish = False
End With
End With
'ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False
'Sheets("Technical Report").Visible = True
'Sheets("2005 IS Recovery Rates & Data").Visible = True
'ActiveSheet.Shapes("CommandButton1").Visible = True
Range("a1").Select
End If
End Sub