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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,550
Members
431,885
Latest member
Rsdg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top