I have VBA code that is protected and I want to distribute the file once all the code runs, however, I want to remove all the macros before I do so that the user is not prompted to "enable" macros when they open the document.
I have this code that works if the project is "unlocked" but of course it will not work if the project is not unlocked.
I want to be able to UNLOCK the project then have the code below run. Again, I know the password.
This is a unique situation in that I have quite a few pieces of code and functions that run to perform a scan of another document. I think copy the results of the scanned document that includes comments and highlighted text into the existing document that contains all the code. I then want to remove all traces of itself so that the saved document contains no VBA or Macros.
Private Sub NothingHere(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub