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.
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.
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.
Asked:
4
3-
2
- +1
1 Solution
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
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.
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.
> 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
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
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.
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.
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.
- 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
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
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
I've requested that this question be deleted for the following reason:
Not enough information to confirm an answer.
Not enough information to confirm an answer.
I've provided a working solution per http:#a40688410
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 Already a member? Login.
Featured Post
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.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now