Excel VBA Macro to remove document information 1

[PureSlate]

Hi all!

I've been working on a macro (in excel) to remove document information/ from all the powerpoint, excel, and word files within a document, but I'm having a difficult time getting the .RemoveDocumentInformation(type) method to run with any non-excel types. The code will run if I replace the type with an excel type. Has anybody had any experience with this? The following is my in-progress code for Excel 2007.

Sub CleanPersonalInfo()
'Macro for cleaning personal data from files within selected folder.
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim WordFile As Object
Dim PowerPointFile As Object
Dim FolderPath As FileDialog
Dim FolderName As String
Dim DocType As String
Dim CurrentFile As String

Set WordFile = CreateObject("Word.Application")
Set PowerPointFile = CreateObject("PowerPoint.Application")

''''''''''''''''''''''''''''''''''''''''''''''
Set FolderPath = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPath
    .AllowMultiSelect = False
    .Show
    FolderName = .SelectedItems(1)
End With
''''''''''''''''''''''''''''''''''''''''''''''

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(FolderName)
Set objFiles = objFolder.Files

For Each objF1 In objFiles
CurrentFile = objF1.Path
Select Case Right(objF1.Name, 4)
    Case ".xls", "xlsx", "xlsm" ': DocType = "xlRDIAll"
        Workbooks.Open (CurrentFile)
        With ActiveWorkbook
            .RemoveDocumentInformation (xlRDIAll)
            .Save
            .Close
        End With
    Case ".doc", "docx", "docm" ': DocType = "wdRDIAll"
        WordFile.documents.Open (CurrentFile)
        With WordFile.ActiveDocument
            .RemoveDocumentInformation (wdRDIAll)
            .ActiveDocument.Save
            .ActiveDocument.Close
        End With
    Case ".ppt", "pptx", "pptm" ': DocType = "ppRDIAll"
        PowerPointFile.Visible = True
        PowerPointFile.Presentations.Open (CurrentFile)
        With PowerPointFile.ActivePresentation
            .RemoveDocumentInformation (ppRDIAll)
            .Save
            .Close
        End With
End Select
Next

Set objF1 = Nothing: Set objFiles = Nothing: Set objFolder = Nothing: Set objFS = Nothing
ExcelFile.Quit
WordFile.Quit
PowerPointFile.Quit

End Sub

 

[PHV]

As you use late binding I guess you didn't add references to Word and PowerPoint and thus don't have acces to the wdXXX and ppXXX constants.
You may use the value 99

Tip: use Option Explicit at the top of all your modules.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PureSlate]

Ah! Beautiful! I really should know better than to not use Option Explicit, but old habits die hard. Curiosity question, is there a way to avoid needing to make powerpoint visible, and is using Word.Application/PowerPoint.Application going to cause issues in Office 2010?

 

[Andrzejek]

Option Explicit - in IDE, go to: Tools - Options... - Editor tab, and check "Require Variable Declaration" checkbox. Option Explicit will be added automagically for you.

"avoid needing to make powerpoint visible" - try to comment out this line:
[tt]PowerPointFile.Visible = True
[/tt]

Have fun.

---- Andy

 

[PureSlate]

Hi Andy,

I receive an error whenever that line is commented out, hence the reason for the question!

"Presentation.Open :Invalid Request. The PowerPoint Frame window does not exist