Solved

Macro to reformat and save document

Posted on 2009-05-19
15
650 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now