Need VBA Code to Move MS Office Files to Different Folders and Place PDF Copies in Other Folders

Using:  Win 7 and MS Office 2010 Apps

We need to process hundreds of documents with the following general workflow and could use a VBA Script to streamline the process.

Here are the tasks:

User receives a Word 2010 doc via email, let's call it myfile.doc and drags it into Folder A (this Folder could be the designated folder for the script to target).  User may receive several files in the same email, all destined to Folder A.  Also, other users may put files requiring the same processing into Folder A.

Folder A will contain only files which need the automated processing below.

User then (the automation is needed from this point forward), using only myfile.doc as an example, but code would need to cycle through all files in Folder A:

- Moves myfile.doc into Folder B
- Saves myfile.doc as myfile.PDF into Folder C
- Saves myfile.doc as myfile.PDF into Folder D
- Moves myfile.doc from Folder A into Folder E
- Stops when Folder A is empty; leaving user with view of the empty Folder A as confirmation all files were processed

In all cases, the various folders could be on different (mapped) network drives, hence this needs to be coded to use the full filepath (windows environment).

Also, while the above example is written showing .doc files, we can expect .docx as well as Excel files in .xls, .xlsx, and .xlsm.

Thx a bunch.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NVITEnd-user supportCommented:
Search here for Joe Winograd. He has solutions to convert to PDF
Joe Winograd, Fellow&MVEDeveloperCommented:
First, my thanks to NewVillageIT for recommending me — I appreciate it! And it's true that I've created many solutions around handling PDF files. However, I'm not an expert in VBA (or VB) and my approach to this problem, which has a substantial number of requirements, would be a stand-alone script/program written in AutoHotkey. I've been using this language during the last several years to write most of my PDF and TIFF related programs, including those with extensive automation requirements, such as looping through a large number of files in a large number of folders/subfolders to an unlimited depth. But for a solution with VBA code, I'm going to bow out in deference to the experts here with strong VBA experience. Regards, Joe
qengAuthor Commented:
Thanks for the feedback NVIT and Joe.

I'm familiar with AutoHotkey and have used it on and off over the years.  Unfortunately, my particular requirements in this instance won't allow me to install/use AutoHotkey on the accounts which need to run this code.

I'll stay tuned for some VB/VBA help.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Joe Winograd, Fellow&MVEDeveloperCommented:
> won't allow me to install/use AutoHotkey

OK, I hear you on that, although you could avoid installing it on the users' computers by running the AutoHotkey compiler on your computer to create a stand-alone executable (.EXE file). Regards, Joe
qengAuthor Commented:
Ahh true enough Joe.  If all else fails, I'll investigate your AutoHotKey option though.  Thx for the feedback.

I know (as you do) that this can be done in VB/VBA or in Powershell but I'm too green in both (and too rushed) to be able to code it myself properly.
NVITEnd-user supportCommented:
- Currently only processes .DOC files
- Please clarify:
 1. Your last step "Moves myfile.doc from Folder A into Folder E" conflicts with first step "Moves myfile.doc into Folder B".
 2. ...needs to be coded to use the full filepath Do you mean like \\server\share\folder? If so, adjust Folder? names accordingly.
- Adjust FolderA, FolderB, FolderC, FolderD, FolderE as needed
- Tested with Word 2007. You have Word 2010. Try it anyway.

' ConvertToPDFAndMove.vbs

'- Moves myfile.doc into Folder B 
'- Saves myfile.doc as myfile.PDF into Folder C 
'- Saves myfile.doc as myfile.PDF into Folder D 
'- Moves myfile.doc from Folder A into Folder E 
'- Stops when Folder A is empty; leaving user with view of 
'  the empty Folder A as confirmation all files were processed 

' In all cases, the various folders could be on different (mapped) network 
' drives, hence this needs to be coded to use the full filepath (windows 
' environment). 

' Also, while the above example is written showing .doc files, we can 
' expect .docx as well as Excel files in .xls, .xlsx, and .xlsm. 

Set objFSO = CreateObject("Scripting.FileSystemObject")

arrFolders = Array(FolderB, FolderC, FolderD, FolderE)

ProcessFolders objFSO.GetFolder(FolderA)


Sub ProcessFolders (folder)
Const OverwriteExisting = TRUE

Dim file, fname, srcfname, tgtfname, upext
For Each file in folder.files
	fname =
	upext = UCase(objfso.GetExtensionName(fname))
	fbase = objfso.GetBaseName(fname)
	If upext = ChkFExt Then
		srcfname = FolderA & "\" & fname
		tgtfname = FolderB & "\" & fname
		'- Move file into Folder B 
		WScript.Echo "Moving " & FolderA & "\" & fname & " " & FolderB
		objFSO.MoveFile srcfname , tgtfname
'- Saves myfile.doc as myfile.PDF into Folder C 
		'Doc2PDF tgtfname
		WScript.Echo FolderB & "\" & fbase & ".pdf" , FolderC & "\", OverwriteExisting
		objFSO.CopyFile "Copying " & FolderB & "\" & fbase & ".pdf" , FolderC & "\", OverwriteExisting
'- Saves myfile.doc as myfile.PDF into Folder D 
		WScript.Echo FolderB & "\" & fbase & ".pdf" , FolderD & "\"
		objFSO.MoveFile "Moving " & FolderB & "\" & fbase & ".pdf" , FolderD & "\"
'- Moves myfile.doc from Folder A into Folder E.
'  ? myfile.doc was already moved to Folder B in prior step.
	End if
End Sub

Function ChkFolders()
If Not objFSO.FolderExists(FolderA) Then
    WScript.Echo "Folder missing. Please create it: " & FolderA
End If

' Create folders as needed
'arrFolders = Array(FolderB, FolderC, FolderD, FolderE)
For Each strFolder in arrFolders
End Function

Function MakeFolder(cName)
If objFSO.FolderExists(cName) Then
    Set objFolder = objFSO.GetFolder(cName)
		' Create Folder
		' Note: If the parent folder does not exist, and error occurs.
    'WScript.Echo "Folder missing: " & cName
		Set objFolder = objFSO.CreateFolder(cName)
End If
End Function

Sub Doc2PDF( myFile )
' This subroutine opens a Word document, then saves it as PDF, and closes Word.
' If the PDF file exists, it is overwritten.
' If Word was already active, the subroutine will leave the other document(s)
' alone and close only its "own" document.
' Requirements:
' This script requires the "Microsoft Save as PDF or XPS Add-in for 2007
' Microsoft Office programs", available at:
'        familyid=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en
' Written by Rob van der Woude

    ' Standard housekeeping
    Dim objDoc, objFile, objFSO, objWord, strFile, strPDF

    Const wdFormatDocument                    =  0
    Const wdFormatDocument97                  =  0
    Const wdFormatDocumentDefault             = 16
    Const wdFormatDOSText                     =  4
    Const wdFormatDOSTextLineBreaks           =  5
    Const wdFormatEncodedText                 =  7
    Const wdFormatFilteredHTML                = 10
    Const wdFormatFlatXML                     = 19
    Const wdFormatFlatXMLMacroEnabled         = 20
    Const wdFormatFlatXMLTemplate             = 21
    Const wdFormatFlatXMLTemplateMacroEnabled = 22
    Const wdFormatHTML                        =  8
    Const wdFormatPDF                         = 17
    Const wdFormatRTF                         =  6
    Const wdFormatTemplate                    =  1
    Const wdFormatTemplate97                  =  1
    Const wdFormatText                        =  2
    Const wdFormatTextLineBreaks              =  3
    Const wdFormatUnicodeText                 =  7
    Const wdFormatWebArchive                  =  9
    Const wdFormatXML                         = 11
    Const wdFormatXMLDocument                 = 12
    Const wdFormatXMLDocumentMacroEnabled     = 13
    Const wdFormatXMLTemplate                 = 14
    Const wdFormatXMLTemplateMacroEnabled     = 15
    Const wdFormatXPS                         = 18
    Const wdFormatOfficeDocumentTemplate      = 23
    Const wdFormatMediaWiki                   = 24

    ' Create a File System object
    Set objFSO = CreateObject( "Scripting.FileSystemObject" )

    ' Create a Word object
    Set objWord = CreateObject( "Word.Application" )

    With objWord
        ' True: make Word visible; False: invisible
        .Visible = True

        ' Check if the Word document exists
        If objFSO.FileExists( myFile ) Then
            Set objFile = objFSO.GetFile( myFile )
            strFile = objFile.Path
            WScript.Echo "FILE OPEN ERROR: The file does not exist" & vbCrLf
            ' Close Word
            Exit Sub
        End If

        ' Build the fully qualified HTML file name
        strPDF = objFSO.BuildPath( objFile.ParentFolder, _
                 objFSO.GetBaseName( objFile ) & ".pdf" )

        ' Open the Word document
        .Documents.Open strFile

        ' Make the opened file the active document
        Set objDoc = .ActiveDocument

        ' Save as HTML
        objDoc.SaveAs strPDF, wdFormatPDF

        ' Close the active document

        ' Close Word
    End With
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe Winograd, Fellow&MVEDeveloperCommented:
Yes, I'm sure it can be done in VB/VBA and likely PowerShell, too, but it sounds as if we're both too inexperienced in those tools to do it. Also, your comment to "code it...properly" makes a good point — it will take a lot longer to write a robust program with decent error-checking and failure-handling code, which are especially important since you're processing hundreds of documents (for example, a write error when creating the PDF or copying it).

My personal opinion (and that's all it is) is that you're asking way too much for a gratis effort here on EE. I think you should look for VB/VBA/PDF experts who have the Hire Me button in their profiles and get a quote for a custom program that meets all of your requirements.

Speaking of your requirements, off the top of my head I can think of two clarifications that are needed:

(1) For Word files, the requirement is clear — make a single PDF from the DOC, DOCX, RTF, and whatever other formats you want that Word can handle. But for Excel files, do you want just the first (leftmost) tab/sheet? Or the active tab/sheet (the one that was selected when the Save was done)? Or do you want all of the tabs/sheets? If the latter, do you want all of them merged into a single PDF or each tab/sheet in a separate PDF?

(2) What do you want to do when the destination folder already contains the file that needs to be copied into it? Overwrite with no warning? Overwrite with warning? Option to overwrite or abort copy? Automatic rename with a unique suffix (_0001, _0002, etc.)?

Regards, Joe
NVITEnd-user supportCommented:
In my last post, the Doc2PDF tgtfname line is disabled. Remove the quote to enable the line.
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
NVITEnd-user supportCommented:
I've provided a working solution per http:#a40688410
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.