Problem Publishing Password Protected worksheet to HTM

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
I have an excel workbook with mutliple worksheets set to publish to a .htm file and it works beautifully. However, once I password protect the sheets, I receive a Run time error 1004 "Can not publish protected cells....etc. Is there any way for me to correct this without removing the protection?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
SOLVED:Problem Publishing Password Protected worksheet to...

Solved problem
 

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
The VB code below will save the current workbook as a Single File Webpage (.mht), then strip the VB code and forms and save the file again.

Code:
Sub SaveCommonFile() 

Dim strServer As String 
Dim strCommon As String 
Dim strCJProj As String 
Dim strMolds As String 

Dim strMoldGrp As String 
Dim strMold As String 

Dim strDate As String 

strServer = Sheet4.Range("F2").Value 
strCommon = Sheet4.Range("F4").Value 
strCJProj = Sheet4.Range("F6").Value 
strMolds = Sheet4.Range("F7").Value 

strMoldGrp = Sheet6.Range("B1").Value 
strMold = Sheet5.Range("E6").Value 

'Unprotect all worksheets
    Dim sht As Object
    Dim pw As Range
    Set pw = Sheet5.Range("a1")
   
For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "" Then _
    sht.Unprotect Password:=pw.Value
Next sht


vFilename = strServer & strCommon & strCJProj & strMolds & strMold & ".mht" 

'Save As .mht file 
    ActiveWorkbook.SaveAs Filename:= _ 
        vFilename, FileFormat:= _ 
        xlWebArchive, CreateBackup:=False 

'Remove macros, forms, etc... 
Set wbActiveBook = ActiveWorkbook 

Set oVBComps = wbActiveBook.VBProject.VBComponents 

For Each oVBComp In oVBComps 
Select Case oVBComp.Type 
Case 1, 2, 3 'Standard Module, Class Module, Userform 
oVBComps.Remove oVBComp 
Case Else 
With oVBComp.CodeModule 'Worksheet or workbook code module 
.DeleteLines 1, .CountOfLines 
End With 
End Select 
Next oVBComp 
      
ActiveWorkbook.Save 

'Optional message 
MsgBox " Please close this file without saving changes! " 

End Sub 


Hope this helps! Has been working pretty well for me.  
Joe
 

Watch MrExcel Video

Forum statistics

Threads
1,114,407
Messages
5,547,771
Members
410,811
Latest member
adustin42
Top