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

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.
0
qeng
Asked:
qeng
  • 4
  • 3
  • 2
  • +1
1 Solution
 
NVITCommented:
Search here for Joe Winograd. He has solutions to convert to PDF
0
 
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
0
 
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.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
0
 
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.
0
 
NVITCommented:
Notes:
- 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")

FolderA="c:\local\test\a"
FolderB="c:\local\test\b"
FolderC="c:\local\test\c"
FolderD="c:\local\test\d"
FolderE="c:\local\test\e"
arrFolders = Array(FolderB, FolderC, FolderD, FolderE)
ChkFolders

ChkFExt="DOC"
ProcessFolders objFSO.GetFolder(FolderA)

WScript.Quit

'-------------------------------------------------------------------------------
Sub ProcessFolders (folder)
Const OverwriteExisting = TRUE

Dim file, fname, srcfname, tgtfname, upext
For Each file in folder.files
	fname = file.name
	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 & "\"
		'WScript.Quit
'- Moves myfile.doc from Folder A into Folder E.
'  ? myfile.doc was already moved to Folder B in prior step.
	End if
Next
End Sub

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

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

'-------------------------------------------------------------------------------
Function MakeFolder(cName)
If objFSO.FolderExists(cName) Then
    Set objFolder = objFSO.GetFolder(cName)
Else
		' 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:
' http://www.microsoft.com/downloads/details.aspx?
'        familyid=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com

    ' 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
        Else
            WScript.Echo "FILE OPEN ERROR: The file does not exist" & vbCrLf
            ' Close Word
            .Quit
            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
        objDoc.Close

        ' Close Word
        .Quit
    End With
End Sub

Open in new window

0
 
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
0
 
NVITCommented:
In my last post, the Doc2PDF tgtfname line is disabled. Remove the quote to enable the line.
0
 
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
NVITCommented:
I've provided a working solution per http:#a40688410
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

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now