Solved

Automating almost everything...

Posted on 2002-07-23
26
559 Views
Last Modified: 2007-11-27
Hello,

I have around 10.000 docs I need to do a certain job on each one of them. I can either hire 10 people and wait a month, or I can award the points (for the moment I am just giving 50) but I will increase once you say that it can be done...

So these 10.000 doc files, are in directories and subdirectories...

e.g.
1999
--> OP
--> DP
--> NP

2000
--> OP
--> DP
--> NP

2001
... the same
each subdirectory (OP, DP, NP) has about 60 DOCS in it, with random names...

What I have to do is open each one of these documents, in Word2000, go to the Menu Tools > Fix Broken Text (this is available for International users only)... and run this procedure. Then just save the file with the changes made...

Is there an automatic way of doing this, I mean entering in each directory/subdirectories, scanning for word files converting and saving? Or do I hire the data entry people?

As this is very imperative job, I am more than happy to award up to 1000 points to whoever gives me a working answer...

Thanks for your time
gm395
0
Comment
Question by:gm395
  • 15
  • 9
  • 2
26 Comments
 
LVL 1

Author Comment

by:gm395
ID: 7172175
if entering into directories sounds difficult, would it be possible to create a macro in word (saved in normal.dot) and then from command line execute this macro in each file (one file at each turn), which runs this menu option, and then just saves? Does this sound more viable?
0
 
LVL 1

Author Comment

by:gm395
ID: 7172243
For your info this menu option I am referring is the EEFONTS.EXE executable, that you can download from:

http://office.microsoft.com/downloads/2000/fontrepair.aspx

This installs an option under Tools named Fix Broken Text...

If you look at VB code in Tools > Macro > etc... there is a project named TemplateProject (Eefonts) and this is the macro installed when running EEFONTS.EXE

Have a look and see what I mean.

So I need a way (call it macro if you like), that will call this menu option, choose the Greek language, perform the conversion in the whole of the document, and then save it and close.

Then if possible, call this macro from command line, and creating a batch file, for each directory, and this batch file just loads winword and automatically call this way (I named it macro before), which does all the job and closes...
0
 
LVL 1

Author Comment

by:gm395
ID: 7172248
by the way, most of these documents are either Word 2.0 or Word95. While saving, I would prefer saving as Word 97/2000.

Thanks
0
 
LVL 47

Expert Comment

by:dbrunton
ID: 7172584
You can probably do it with macros and a batch file.

I think you can run Word macros from the command line but my memory is hazy on this point.  Your macro would do the text fixing and saving for you.  The batch file would get the files for you.

Syntax in the batch file (let's call it alpha) would be something like


for %%a in (%1\*.doc) do path_to_word_2000 %%a macroname

where

%1 is the path to your folder containing the files
path_to_word_2000 is the path to word 2000 (something like  c:\progra~1\office\word\winword.exe - this is probably not accurate)
macroname is the macro to do the work

You would use the batch file by typing in

alpha c:\1999\op

and this would process the files in the 1999\op folder
0
 
LVL 1

Author Comment

by:gm395
ID: 7172623
Apart from the Batch file, how would I write the macro in WinWord, the one you are referring as macroname? (to convert - and save as Word 97/2000).

Do you think you can give me a working code of BATCH file, even if based on your system, as I am not really fond of scriptin either in VBA nor DOS batch files?

Thanks
0
 
LVL 47

Expert Comment

by:dbrunton
ID: 7172976
The working code of the batch file would be


for %%f  in (%1\*.doc) do Pathtoword2000 macroname

Now I don't know the Pathtoword2000 on your machine and I don't run Word at the moment as I haven't reinstalled it.

But it would possibly be something like

c:\progra~1\msoffice\word\winword.exe

You would have to use Windows Explorer to see it.

Macroname could be something like  /macro1.bas depending on how you save it.

I haven't got access to Word2000 at present so I would have to have a look at how to run a macro from the command line.  Playing with VBA is not FUN.

You can make the macro in Word2000 by following all the steps you require it to do of a file and then examining the VBA code.  Open any file you like and then start the macro recording.  Do the text alterations and then Select   File --> Save As --> and change the Type of File to Word97.  Then click on OK to do the Save.  Close File and then Exit.   That should be the macro you need.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7173046
Hi gm395,

since i could get the add inn installed i couldn't test this propoerly but it should get you going and if you got questions post them here

-in Word open a new document
-open the VB Editor with ALT + F11
-then insert a new module from the menu
-then paste the code

option explicit

Dim mastrDirList() As String
Dim mastrFiles() As String
Dim strOldDirName As String

Sub sBuildDirList(strCurDir As String)
'Builds dir list for strCurDir folder
Dim i As Long
Dim lngX As Long
Dim strName As String
    i = 1
    strOldDirName = strCurDir
    ChDir strCurDir
    'Get First Entry
    strName = Dir(strCurDir, vbDirectory)

    Do While strName <> ""
        If strName <> "." And strName <> ".." Then
            If GetAttr(strName) = vbDirectory Then
                'add dir names in current folder
                ReDim Preserve mastrDirList(i)
                mastrDirList(i - 1) = strName
                i = i + 1
                Debug.Print strCurDir & strName
            Else
                Debug.Print strCurDir & strName
            End If
        End If
        'get Next entry
        strName = Dir
    Loop
    For lngX = 0 To UBound(mastrDirList) - 1
        ConvertFilesDir strCurDir:=mastrDirList(lngX)
    Next lngX
    'restore previous curdir
    ChDir strOldDirName
End Sub

Sub ConvertFilesDir(strCurDir As String)
Dim myName As String
Dim strSaveDir As String
Dim strFileName As String
   
    strSaveDir = CurDir()
    ChDir strCurDir
    'loop all doc files in this folder
    myName = Dir(CurDir & "\*.doc")
    Do While myName <> ""
        If (GetAttr(myName) And vbDirectory) <> vbDirectory Then
            If myName <> "." Or myName <> ".." Then
              Application.DisplayAlerts = False
              strFileName = strFilesInDir
              Documents.Open strFileName
              Application.Run "MacroName"         'insert the name of the macro to run
              ActiveDocument.SaveAs strFileName
              Application.DisplayAlerts = True
              ActiveDocument.Close
            End If
        End If
        'Get Next entry
        myName = Dir
    Loop
    ChDir strSaveDir
End Sub

-the builddirlist procedure will loop through the directory list and sub directories

-the convertfiles procedure will open the doc files | run the macro | save them

you can call the function with a sub procedure like this

Public sub FileConverter()
  sBuildDirList strCurDir:=".....yourdir....."
end sub

be sure to put the correct directory in this calling procedure e.g.

if you got a structure like

c:\temp\
        1999
        2000
        2001

call it like

Public sub FileConverter()
  sBuildDirList strCurDir:="c:\temp"
end sub

hope this is a start to work on
HAGD:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7173047
to dbrunton, playing with VBA CAN be FUN ;)
0
 
LVL 1

Author Comment

by:gm395
ID: 7173885
I added the code into a new module as you mentioned, I added the FileConverter() function... like this:

Public Sub FileConverter()
 sBuildDirList strCurDir:="D:\FILES\1997\AP"
End Sub

When I run the FileConverter() macro, I get an error

Run-time error 53
File not found

This happens at the line with the following statement:

If GetAttr(strName) = vbDirectory Then

inside the sBuildDirList function....

The variables are as follows:

strCurDir = D:\FILES\1997\AP
strName = AP
vbDirectory = 16

this is if I give

Public Sub FileConverter()
 sBuildDirList strCurDir:="D:\FILES\1997\AP"
End Sub

---
IF NOW I GIVE

Public Sub FileConverter()
 sBuildDirList strCurDir:="D:\FILES\1997\AP\"
End Sub

then variables are as follows:

strCurDir = D:\FILES\1997\AP\
strName = CEREALS
vbDirectory = 16

CEREALS is a subdirectory inside AP. AP has about 10 subdirectories in it, and each one has 60 files....

That's all, I am waiting for your help.
gm395
0
 
LVL 1

Author Comment

by:gm395
ID: 7174433
Additionally, if you have time, I have a number of PDF files created automatically (some piece of code added after your Application.Run "MacroName"). These PDF files are created in a directory D:\TEMP_PDF\. Is there a possibility that all these files can be MOVED (not copied) into the directory in which your macro actually works now and runs the conversion macro? How would this moving from this default directory into the strCurDir + strName would happen in VBA code?
0
 
LVL 1

Author Comment

by:gm395
ID: 7175472
If you cannot install EEFONTS, try downloading it from here:

http://download.microsoft.com/download/word2000/EEFonts/2000/W9XNT4/EN-US/EEFonts.exe

when installed, open a document, with some text in it, Go to Tools > Fix Broken Text. This link runs the FixTextMain macro (installed with eefonts.dot). It will ask you to choose a Language, and then it will appear a dialog box saying that an Arial font will be applied, in which you will press OK. Then the text will be rectified (if it has any problems).

Another problem is that with your line of code:

 Application.Run "FixTextMain"

it still shows the two dialog boxes, meaning that for all the files, I should have somebody keep pressing OK. Do you think there is a way of avoiding pressing these two OKs?

Thanks again. I think I am loading you with too much information and requests but I think this is final.

Thanks
0
 
LVL 44

Accepted Solution

by:
bruintje earned 400 total points
ID: 7175486
for your original Q on the code made some changes

Option Explicit

Dim mastrDirList() As String
Dim mastrFiles() As String
Dim strOldDirName As String

Sub sBuildDirList(strCurDir As String)
'Builds dir list for strCurDir folder
Dim i As Long
Dim lngX As Long
Dim strName As String
   i = 1
   strOldDirName = strCurDir
   ChDir strCurDir
   'Get First Entry
   strName = Dir(strCurDir, vbDirectory)

   Do While strName <> ""
       If strName <> "." And strName <> ".." Then
           If GetAttr(strName) = vbDirectory Then
               'add dir names in current folder
               ReDim Preserve mastrDirList(i)
               mastrDirList(i - 1) = strName
               i = i + 1
               Debug.Print strCurDir & strName
           Else
               Debug.Print strCurDir & strName
           End If
       End If
       'get Next entry
       strName = Dir
   Loop
   For lngX = 0 To UBound(mastrDirList) - 1
       ConvertFilesDir strCurDir:=mastrDirList(lngX)
   Next lngX
   'restore previous curdir
   ChDir strOldDirName
End Sub

Sub ConvertFilesDir(strCurDir As String)
Dim myName As String
Dim strSaveDir As String
Dim strFileName As String
   
   strSaveDir = CurDir()
   ChDir strCurDir
   'loop all doc files in this folder
   myName = Dir(CurDir & "\*.doc")
   Do While myName <> ""
       If (GetAttr(myName) And vbDirectory) <> vbDirectory Then
           If myName <> "." Or myName <> ".." Then
             Application.DisplayAlerts = False
             strFileName = myName
             Documents.Open strFileName
             Application.Run "MacroName"         'insert the name of the macro to run
             ActiveDocument.SaveAs strFileName
             Application.DisplayAlerts = True
             ActiveDocument.Close
           End If
       End If
       'Get Next entry
       myName = Dir
   Loop
   ChDir strSaveDir
End Sub

Public Sub FileConverter()
 sBuildDirList strCurDir:="c:\"
End Sub

the strCurDir has to use an explicit \ on the end

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7175490
seeing your added comment i guess the

Application.DisplayAlerts = False
strFileName = myName
Documents.Open strFileName
Application.Run "MacroName"         'insert the name of the macro to run
ActiveDocument.SaveAs strFileName
Application.DisplayAlerts = True

disabling of the alerts with

Application.DisplayAlerts = False

is taking care of the box
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 44

Expert Comment

by:bruintje
ID: 7175541
complemented the convertfilesdir proc to include a move for all pdf files in your ouput temp to teh current directory

Sub ConvertFilesDir(strCurDir As String)
Dim myName As String
Dim strSaveDir As String
Dim strFileName As String
   
   strSaveDir = CurDir()
   ChDir strCurDir
   'loop all doc files in this folder
   myName = Dir(CurDir & "\*.doc")
   Do While myName <> ""
       If (GetAttr(myName) And vbDirectory) <> vbDirectory Then
           If myName <> "." Or myName <> ".." Then
             Application.DisplayAlerts = False
             strFileName = myName
             Documents.Open strFileName
             Application.Run "MacroName"         'insert the name of the macro to run
             ActiveDocument.SaveAs strFileName
             Application.DisplayAlerts = True
             ActiveDocument.Close
           End If
       End If
       'Get Next entry
       myName = Dir
   Loop
   'loop all pdf files in the pdf output folder
   myName = Dir("D:\TEMP_PDF\*.pdf")
   Do While myName <> ""
       Name "D:\TEMP_PDF\" & myName As strSaveDir & strCurDir & myName
       'Get Next entry
       myName = Dir
   Loop
   ChDir strSaveDir
End Sub

HAGD:O)Bruintje
0
 
LVL 1

Author Comment

by:gm395
ID: 7175554
I still get the same error on the same line of code:

          If GetAttr(strName) = vbDirectory Then

Error code 53
file not found

I am explicitly using the \ at the end. In my previous example, the subdirectory AP has about 10 subdirectories in it, and each one has 60 files in it. Even if I run it like this:


Public Sub FileConverter()
sBuildDirList strCurDir:="D:\FILES\1997\AP\CEREAL\"
End Sub

strName has the value of the first filename (e.g. myfile.doc), but still I get the Error code 53 File not found. Is it working fine in your system?

0
 
LVL 1

Author Comment

by:gm395
ID: 7175559
I will check for the loop for the PDFs tomorrow from work, but I can be online for any changes as far as the original Q is concerned.
0
 
LVL 1

Author Comment

by:gm395
ID: 7175586
Error 53 refers to sub sBuildDirList()
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7175593
yeah it is working here, i even could run a macro on the file i got in the loop somewhere in a directory on my c drive

if you put a break point on the line what's in the strname variable?
0
 
LVL 1

Author Comment

by:gm395
ID: 7175727
I fixed the problem my self, I just need to have the .doc file with the macro, on the same drive and at the same directory level with the starting directory of my .doc files, in my case D:\. Then it works fine, but I get one problem:

When arriving at the code:

For lngX = 0 To UBound(mastrDirList) - 1

I get a run-time error 9
Subscript out of range

this refers to UBound(mastrDirList) = <Subscript out of range>

What is happening now?

If this works and the ConverFilesDir is called, I will be able to tell you whether the macro is running, and whether dialog boxes appear.
0
 
LVL 1

Author Comment

by:gm395
ID: 7175799
I changed the ConvertFilesDir function to:

Sub ConvertFilesDir(strCurDir As String)
Dim myName As String
Dim strSaveDir As String
Dim strFileName As String
 
  strSaveDir = CurDir()
  ChDir strCurDir
  'loop all doc files in this folder
  myName = Dir(strCurDir & "\*.doc")
  Do While myName <> ""
      If (GetAttr(strCurDir + "\" + myName) And vbDirectory) <> vbDirectory Then
          If myName <> "." Or myName <> ".." Then
            Application.DisplayAlerts = False
            strFileName = myName
            Documents.Open strCurDir + "\" + strFileName
            Selection.WholeStory
            Application.Run "FixTextMain"         'insert the name of the macro to run
            ActiveDocument.SaveAs strCurDir + "\" + strFileName
            Application.DisplayAlerts = True
            ActiveDocument.Close
          End If
      End If
      'Get Next entry
      myName = Dir
  Loop
 
  ChDir strSaveDir
End Sub

because when tested individually (just passing a directory...) I got problems. By changing it, it now works fine, but still the dialog boxes appear. Documentation on FixTextMain would be helpful... I still though have problems with the Subscript out of range and waiting for your answer.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7176378
you can solve the subscript error

if (UBound(mastrDirList) - 1 = 0) then
  For lngX = 0 To UBound(mastrDirList)
      ConvertFilesDir strCurDir:=mastrDirList(lngX)
  Next lngX
else
  For lngX = 0 To UBound(mastrDirList) - 1
      ConvertFilesDir strCurDir:=mastrDirList(lngX)
  Next lngX
end if

shall try to install it again
0
 
LVL 1

Author Comment

by:gm395
ID: 7176895
since many problems arise with the BuildDirList, I have created the directory structure my self, and call in series the:

ConvertFilesDir strCurDir:="...my directory with files..."

The function converFilesDir works fine, but I get the two OK buttons in series I have to keep pressing, each time a document is opened. Is there a way, like a utility that keeps pressing the OK button automatically, or something like this? Task automators? The Application.DisplayAlerts = False does not work, and I don't know the API of the FixTextMain macro to pass it the values...

Additionally, as I understand we are arriving to an end, so as I am not good at this, I will let you choose the points you would like for the completion of the answer, depending on your discretion... as I said I give a max of 1000, so feel free...
0
 
LVL 1

Author Comment

by:gm395
ID: 7177039
Ok I did it. I use a utility called KeyText, which captures the dialog boxes and just presses <ENTER>. At the end of the day... it works. Let me know with the points....
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7177706
sorry for not hanging for full support every time, bit hard these days

but if it helped you, i'm satisfied as is, you did a lot yourself i only gave some pointers and a bit of code
0
 
LVL 1

Author Comment

by:gm395
ID: 7179598
Thanks. I will let you know if something goes wrong or having problems....
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7179607
wow thanks for the points, yeah just let me know if there are still problems with the solution

you can also contact me at mulbum@worldonline.nl just mention the q on EE
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

760 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

23 Experts available now in Live!

Get 1:1 Help Now