Solved

Macro to reformat and save document

Posted on 2009-05-19
15
678 Views
Last Modified: 2012-05-07
Every morning I have to download about 40 .doc and then open each file, reformat the margins and fonts and save it as a .docx file with the same name as before (yesterdaytransactions.doc reformated and saved as yesterdaytransactions.docx). I used the record macro to create a macro to reformat it, but then I have to manually save it and close the file.  Then delete all the .doc files.

Is there a way I can do this without opening each file in word?  If not is there a way to make the same macro save the file.  I know very little VB.

thanks
Andy
0
Comment
Question by:amac64
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24428251
can you post the macro code you have now and say specific what else you want it to do?
0
 

Author Comment

by:amac64
ID: 24428378
Here is one like what I use at work, it reformats the report, font, margins and orientation...In addition I want it to save the file in a .docx file format with the same name as the .doc file and close the report.  If I could do this to all the files in a directory it would be great without having to open each file.

Sub REformat1()
'
' REformat1 Macro
'
'
    WordBasic.TogglePortrait Tab:=3, PaperSize:=1, TopMargin:="1.25", _
        BottomMargin:="1.25", LeftMargin:="1", RightMargin:="1", Gutter:="0", _
        PageWidth:="11", PageHeight:="8.5", Orientation:=1, FirstPage:=0, _
        OtherPages:=0, VertAlign:=0, ApplyPropsTo:=0, FacingPages:=0, _
        HeaderDistance:="0.5", FooterDistance:="0.5", SectionStart:=2, _
        OddAndEvenPages:=0, DifferentFirstPage:=1, Endnotes:=0, LineNum:=0, _
        StartingNum:=1, FromText:=wdAutoPosition, CountBy:=0, NumMode:=0, _
        TwoOnOne:=0, GutterPosition:=0, LayoutMode:=0, CharsLine:=36, LinesPage:= _
        36, CharPitch:=240, LinePitch:=360, DocFontName:="Times New Roman", _
        DocFontSize:=12, PageColumns:=1, TextFlow:=0, FirstPageOnLeft:=0, _
        SectionType:=1, FolioPrint:=0, ReverseFolio:=0, FolioPages:=1
    WordBasic.PageSetupMargins Tab:=0, PaperSize:=1, TopMargin:="0.25", _
        BottomMargin:="0.25", LeftMargin:="1", RightMargin:="1", Gutter:="0", _
        PageWidth:="11", PageHeight:="8.5", Orientation:=1, FirstPage:=0, _
        OtherPages:=0, VertAlign:=0, ApplyPropsTo:=0, FacingPages:=0, _
        HeaderDistance:="0.5", FooterDistance:="0.5", SectionStart:=2, _
        OddAndEvenPages:=0, DifferentFirstPage:=1, Endnotes:=0, LineNum:=0, _
        CountBy:=0, TwoOnOne:=0, GutterPosition:=0, LayoutMode:=0, DocFontName:= _
        "", FirstPageOnLeft:=0, SectionType:=1, FolioPrint:=0, ReverseFolio:=0, _
        FolioPages:=1
    Selection.WholeStory
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 8
End Sub
0
 
LVL 3

Accepted Solution

by:
jakemdrew earned 250 total points
ID: 24428719
Caution!  This code will attempt to open EVERY FILE in the directory you point it at.  You will need to create a special folder where you place all of the documents which you want to run your formatting on.  Then point the code at that folder and run.  

I could not really test your formatting macro since I have no sample files etc.  You will have to do that part.

INTRUCTIONS
1. Paste the code below in the module that your macro above is located in.
2. In the Visual Basic Editor select Tools > References
3. Find the reference "Microsoft Scripting Runtime" under "Available References" and check the box.
4. Each time you run the code, you need to make sure the file path in this line of code is updated to the correct FOLDER location:  FILES_IN_FOLDER ("C:\Users\Administrator\Documents")
5. To run the code click on the line of code that says:
Sub UPDATE_FILES() then select Run > Run Sub from the menu (or hit PF5)

' THIS IS THE CODE YOU NEED TO PASTE IN YOUR MODULE BELOW:

Sub UPDATE_FILES()
'UPDATE THE FILE PATH BELOW TO POINT AT YOUR SPECIFIC UPDATE FOLDER
FILES_IN_FOLDER ("C:\Users\Administrator\Documents")
End Sub

Sub FILES_IN_FOLDER(SourceFolderName As String)
Dim fso As New Scripting.FileSystemObject, SourceFolder As Scripting.FOLDER, FileItem As Scripting.File

Set SourceFolder = fso.GetFolder(SourceFolderName)
   
For Each FileItem In SourceFolder.Files
    On Error GoTo N_FILE
    'open each document in the folder provided
    Documents.Open FileItem.Path, , , False
    'run your macro
    REformat1
    'close and save the document
    Documents.Close True
N_FILE:
Next FileItem

End Sub




0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
LVL 3

Expert Comment

by:jakemdrew
ID: 24428724
Also, The way the code is written, you can plug any formatting macro into it by just replacing this line of code:

 'run your macro
    REformat1
 
If you have a macro called Reformat_New just replace the line of code as follows:

 'run your macro
    Reformat_New
0
 

Author Comment

by:amac64
ID: 24437272
Thanks jakemdrew.  I will try it in the morning and let you know how it works.

Thank you
Andy
0
 

Author Comment

by:amac64
ID: 24437275
jakemdrew, Do I put this in the normal.dot file?
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24437395
You put it in the same module where you got the macro from. Be sure to make backups for the files you test it on.
0
 

Author Comment

by:amac64
ID: 24450505
When I run the macro, I get a User-defined typpe not defined on this line:

Dim fso As New Scripting.FileSystemObject,
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24451597
1. In the Visual Basic Editor select Tools > References
2. Find the reference "Microsoft Scripting Runtime" under "Available References" and check the box.
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24452241
I made a file for you with a textbox and a button.

1. Open the file
2. Put the path of the folder you want to update in the text box.
3. Click the button.

The code in this file can be found by:

1. From the Tools menu, select Macro, then select Visual Basic Editor.
2. In the Left Pane:  Open the folder "Project (format files in dir 2)
3. Open the folder "Microsoft Word Objects"
4. Double click on the item:   "ThisDocument"
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24452438
I did not see the last file show up.  I am attaching a zipped file now.
0
 

Author Comment

by:amac64
ID: 24453131
I don't see the file, where should it be?

I was able to put the code above in my module and ran it.  It cycle through all the files, but only changed the orientation.  It didn't change the size of the fonts or the margin and then saved it in the same format, which is Word 97-2003.  If I manually run my macro, the one I am calling from your code, it does everything, but then I have to manually save it in the Word document format....

I commented the Documents.close line out and ran the code.  It ran the macro, and changed everything, but then didn't save it.  Before I did that, I never saw it run the macro.  It looks like it is skipping, or not having enough time to execute the macro before it hit the close statement.  Sorry I know nothing about VB.  Thank you for all your help.
Hope this helps.  I can upload a file if it would help.

0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24454341
I cant get it to upload.  Email me and I send it to you.  jakemdrew@gmail.com

0
 

Author Comment

by:amac64
ID: 24455157
I sent you an email...thanks
0
 

Author Closing Comment

by:amac64
ID: 31583256
Thanks Alot....
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question