×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Loop Through a folder, and clear all word documents formatting from Excel.

Loop Through a folder, and clear all word documents formatting from Excel.

Loop Through a folder, and clear all word documents formatting from Excel.

(OP)
Hello Fellows,

I want to Loop through a folder and remove all formatting of MS Word documents from Excel.

I am using below attached code, but it's not working. I am stuck with actual command execution.

Please help me in this regards

Thanks



CODE --> VB

Sub clrfmt()
Dim wdapp As New Word.Application
Dim myDoc As Word.Document
Dim myFolder As String, strFile As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Please select a folder"
    .Show
    .AllowMultiSelect = False
Tha            If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
            MsgBox "You did not select a folder"
            Exit Sub
            End If
    myFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With

Application.ScreenUpdating = False

strFile = Dir(myFolder & "\*.doc", vbNormal)

While myFile <> ""
        
    Set myDoc = wdapp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
        
        With myDoc
            
            ActiveDocument.Select
            Selection.ClearFormatting
            
            
        End With
        
    myDoc.Close SaveChanges:=True
    strFile = Dir()
Wend

wdapp.Quit
Set myDoc = Nothing: Set wdapp = Nothing: Set myWkSht = Nothing
Application.ScreenUpdating = True
 
End Sub 

RE: Loop Through a folder, and clear all word documents formatting from Excel.

CODE -->

ActiveDocument.Select
            Selection.ClearFormatting 

Formatting does not apply to a document.

It applies to Range objects.

And possibly Story objects, which are sort of predefined meta-ranges...

Also, using Select simply slows things down by mucking around with the GUI. You don't need to do that. Work with objects directly.

RE: Loop Through a folder, and clear all word documents formatting from Excel.

(OP)
Thanks mintjulep for your reply,

I am sorry, I don't get what you are saying. (New to Codes)
Moreover, the code you are provided already exists in the code. It is really helpful if you can modify the code for me.

Thanks

RE: Loop Through a folder, and clear all word documents formatting from Excel.

Doing this from memory. I think that EntireStory descroes the Range of the whole document, but I could be wrong. And I’m not sure there’s ClearFormatting...

CODE

While myFile <> ""
        
    Set myDoc = wdapp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
        
    myDoc.EntireStory.ClearFormatting
        
    myDoc.Close SaveChanges:=True
    strFile = Dir()
Wend 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Loop Through a folder, and clear all word documents formatting from Excel.

There is no 'EntireStory' property or 'ClearFormatting' method for it in Word. There is a 'WholeStory' property, but even that has no 'ClearFormatting' method.

In any event, there is no way to remove all formatting from a Word document unless you save it as a plain text file - in which case it's no longer a Word document. The most you can do in Word is to apply a single Style to all the content and remove any direct formatting. Hence:

CODE

While myFile <> ""
  Set myDoc = wdapp.Documents.Open(FileName:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With myDoc
    .Range.Style = wdStyleNormal
    .Range.ParagraphFormat.Reset
    .Range.Font.Reset
    .Close SaveChanges:=True
  End With
  strFile = Dir()
Wend 

Cheers
Paul Edstein
[MS MVP - Word]

RE: Loop Through a folder, and clear all word documents formatting from Excel.

>There is a 'WholeStory' property
Whole Story is a method, not a property

So to strip formatting in a document you can do

CODE

Public Sub StripFormatting()
    Dim oldselection As Range
    
    Application.ScreenUpdating = False
    Set oldselection = Selection.Range
    Selection.WholeStory
    Selection.ClearFormatting
    oldselection.Select
    Application.ScreenUpdating = True
End Sub 

RE: Loop Through a folder, and clear all word documents formatting from Excel.

(OP)
Thanks all the experts, Let me try these methods. Will report back soon.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close