Newbie needs help with processing a number of docs please

[Tecnik]

Hi There,

I'm a newbie to VBA however just recently I've found myself playing with macros in Word and would like to find out more.

I've created some macros that have proved real timesavers with a recent project, which was quite rewarding. I've got something a little more in depth I'm trying to create now and wondered if someone might be able to point me in the right direction as far as code or tutorials go.

I have a number of idividual word files that I want to run through PDFmaker. I can execute the macro I need with:-



Application.Run MacroName:="AdobePDFMakerA.AutoExec.ConvertToPDF"


However I want to be able to select a number of files, or folder containing files, and run them through PDFmaker. I also need to bea able to take care of a Save dialog box that prompts the user to save the file.

Any help would be appreciated, thanks in advance,

Nick

P.S. Can anyone recommend any good tutorials on VBA or any good books, thanks.

[fumei]

In terms of processing a number of files, look up the Dir function, as well as FileSystyemObject.

As for processing a number of PDFMaker instructions...shudder...just thinking about it gives me the willies.

I assume that the Adobe macro will take parameters, that is, a file name? Then something like:Dim oFile
Dim strPath As String
strPath = "c:\blahblah\DocFiles\"
oFile = Dir(strPath & "*.doc")
Do While oFile <> ""
' do you have to open the file to
' use the PDFmaker macro?
' if so...
Documents.Open Filename:=strPath & oFile
' run the PDFMaker macro
Documents(oFile).Close wdDoNotSaveChanges
oFile = Dir
Loop

[Tecnik]

Hi Gerry,

Thanks for the help, I'll give the code a try and see what I come up with.

Having looked at the code I think I'm right in saying I need to put this:-

Application.Run MacroName:="AdobePDFMakerA.AutoExec.ConvertToPDF"
where your code says:-
' run the PDFMaker macro
My only thought with that at the moment is what to do when prompted to hit the 'save' button on the 'save' dialog box that appears. This could be the only snag and hold up the whole process, obviously it's that user intervention I'm trying to do away with. It's always been one of those things to watch for when I've been putting together a Photoshop action or batch processing in Acrobat.

There is another thing that I may need to get round, I've just thought of it whilst sat here, and that's, when PDFmaker has finished creating the pdf, from the word doc, it automatically opens the pdf in Acrobat. That obviously makes Acrobat active with Word sat behind it. Will the Word VBA continue to chug through the files even though Acrobat is active?

The looping code you've given me looks useful, thanks for that. I'll have a read up on the Dir Function and FileSystyemObject.

I'll try the code a little later and let you know how it goes, thanks again for the help Gerry, it's greatly appreciated.

Regards,

Nick

[Tecnik]

Hi Gerry,

Thanks again for the help with this, the code worked fine and it's been a real time saver.

The 'save' dialog prompt could be turned off in the PDFmaker prefs, so now that isn't an issue. The previewing of the finished pdf in Acrobat could also be disabled in the prefs so now the macro just chugs through the files in a given folder without too many holdups.

I took the script one step further so that now completed files are moved to another folder. I'll post the code after I've made another slight tweak. At the mo if a file already exists in the folder it's being moved to, an error is thrown and this stops the script.

Thanks again,

Nick

[fumei]

Adding an instruction to save to a different folder is not difficult. And you can certainly do checks for the existence of a file.

[Tecnik]

Regarding saving the file to a different location, once I've called the PDFmaker macro, in the macro I've now got, how can this be interupted or told to do something different?

Here's the code as it stands at the mo, it still needs some error handling adding to the moving of the processed files, as mentioned earlier, if a file with the same name is encountered the script throws an error. Any other comments on how it can be improved would be appreciated, thanks.

Here's the code:-

Sub batchPDFmaker()
Dim oFile
Dim strPath As String, movFold As String
strPath = "C:\PATH TO FILES TO BE PROCESSED"
movFold = "C:\PATH TO PROCESSED FILES FOLDER"
oFile = Dir(strPath & "*.doc")

Do While oFile <> ""
Set fso = CreateObject("Scripting.FileSystemObject")
' do you have to open the file to
' use the PDFmaker macro?
' if so...
Documents.Open FileName:=strPath & oFile
' run the PDFMaker macro
Application.Run MacroName:="AdobePDFMakerA.AutoExec.ConvertToPDF"

Documents(oFile).Close wdDoNotSaveChanges

fso.MoveFile (strPath & oFile), movFold
oFile = Dir
Loop
End Sub


Nick

[mdmackillop]

Hi Nick,
Here's a bit of code I use in a mailmerge function, to check for existing names and add a suffix if found.

'Check for previous instances of the SaveName, if found add incremental suffix
With Application.FileSearch
.NewSearch
.LookIn = Direct
.SearchSubFolders = False
.FileName = SaveName & "*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
SaveAsName = SaveName & "-" & .FoundFiles.Count & ".doc"
End If
End With

[Tecnik]

Thanks for the code, I'll incorporate it into what I already have, all being well that will eliminate the error.

Apart from that small problem everything else worked fine and Word chugged through all the files creating pdfs from them, it proved to be a big timesaver.

Thanks again to all who helped,

Regards

Nick