Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New to VBA..need help with automating GETSAVEAS Macro in Excel 2003
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 -- xphile ------------------------------------------------------------------------ xphile's Profile: http://www.excelforum.com/member.php...o&userid=30692 View this thread: http://www.excelforum.com/showthread...hreadid=503577 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New to VBA..need help with automating GETSAVEAS Macro in Excel 2003
Bump X -- xphile ------------------------------------------------------------------------ xphile's Profile: http://www.excelforum.com/member.php...o&userid=30692 View this thread: http://www.excelforum.com/showthread...hreadid=503577 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating a worksheet in Excel 2003 | Excel Worksheet Functions | |||
Automating Formulas in Excel 2003? | Excel Discussion (Misc queries) | |||
Automating Excel w VB.Net 2003 | Excel Discussion (Misc queries) | |||
Automating the XML Data Mapping in Excel 2003 using C# | Excel Programming | |||
Excel 2003 VBA Help not working when automating via VB 6 | Excel Programming |