• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

Macro to reformat and save document

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
amac64
Asked:
amac64
  • 8
  • 7
1 Solution
 
jakemdrewCommented:
can you post the macro code you have now and say specific what else you want it to do?
0
 
amac64Author Commented:
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
 
jakemdrewCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
jakemdrewCommented:
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
 
amac64Author Commented:
Thanks jakemdrew.  I will try it in the morning and let you know how it works.

Thank you
Andy
0
 
amac64Author Commented:
jakemdrew, Do I put this in the normal.dot file?
0
 
jakemdrewCommented:
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
 
amac64Author Commented:
When I run the macro, I get a User-defined typpe not defined on this line:

Dim fso As New Scripting.FileSystemObject,
0
 
jakemdrewCommented:
1. In the Visual Basic Editor select Tools > References
2. Find the reference "Microsoft Scripting Runtime" under "Available References" and check the box.
0
 
jakemdrewCommented:
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
 
jakemdrewCommented:
I did not see the last file show up.  I am attaching a zipped file now.
0
 
amac64Author Commented:
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
 
jakemdrewCommented:
I cant get it to upload.  Email me and I send it to you.  jakemdrew@gmail.com

0
 
amac64Author Commented:
I sent you an email...thanks
0
 
amac64Author Commented:
Thanks Alot....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now