Video: How Do I: Batch Process Excel Files?

Presenter:Steve Hansen

One common Office development task is creating a procedure to process to multiple files. In this video, Steve Hansen demonstrates a procedure for batch processing Excel files.


The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
Steve Hansen here with Visio tips com this videos how to process the best of my topics of files slot times when you re developing solutions from my subject so you might need to open up a large number for books performed the same action on each workbook and then close it down so well it is demonstrated shooter for hype about doing that today snickered a new project here at office 2007 Visual Basic posted is based on an Excel workbook formula open up the code for this workbook
Minute 1
To go back to the designer here first things and he said to the designer here to show the toolbox will use a dialogue news is just too present the user with a list of files or way to visually select the files that they want to process so for this dollar contributors is get rid of the default filename and they ll set the filter for the files that appear within a close down the designer
Minute 2
Qu bec into this workbook and I m going to just create a one procedure here and processing of as files are longtime it is a lot more difficult than you might first think he got into a couple of things to do this really when you need to make sure he needs to see for ports are opened up for you open an extra helping of workbook that is already open Again there are things I want to do is if the workbook was already open remember that an OLE that workbook open for the user because if the workbook was closed and was workbook that the process opens up a picture that I close the workbook back down so things in Dunedin is simple function to see the workbook is open at
Minute 3
Them to do for templates to understand a simple implementation of the slump from the call at is workbook open and longer do here s his declare a workbook verbal minimum you go through and look at is the first workbook in application workbooks and use the string compare function looking at the name of the current workbook compared to the name of the file that was passed in and to compare text and if it equals zero than there is in the
Minute 4
In that case a little in return true and exit the function and throw workbooks and I never do give the Mets a North return false so the reason it with simplified hears that I m assuming here that the file Heston is the file name only it does include the directory so their implementation of this is workbook opened his to also check to see if if the file as soon as either a long name that as it includes the directories well or shortening and then did with one of visible checks if it s open that would be a much better and more flexible way to undo this is workbook open function back to process files for Singh others to declare a variable to hold the result of showing my open files log lets to be a dialogue
Minute 5
His old verbal numbness at that equal to a call to my open file dialog one show dialog and then if the result equals though it was okay to go ahead and process files so will they like to do when processing a number files you know what show the user bunch of screen sizes of files open up and shut down some new turn application screen updating and said that equal to false or make sure I turn it back on form done them to declare few girdles only do
Minute 6
Versus is a simple variable to hold a reference to an index on a an array of strings than a simple verbal the whole reference to workbook that will take effect if the workbook was already open in a next and lucrative for Luke to dilute through the files in the open file though this reminds me to need to go back into the design and make sure I allow for multi select in my open file dollar zone going to do a form of your
Minute 7
The filenames and limited account instance is a zero based array or subtract one the minaret the entire thing here in the try catch block so the tribe I is for single or do is see the get the first file name in the open file dog see if it s open and use the save file names which gives you the file name only without the directory of the workbook is already open I can set my WB or my workbook variable is
Minute 8
Eyes looking at the aura using the workbooks collection with the name of the first file in my the name of the current file again using safe only and suck up filename only and since this workbook is already open and remember that sort remember to leave it open after and done processing at of the workbook is an open and need to use the open method on the application workbooks similar pass and the descendents of just the filename in a pass in the filename and the directory name to do that are use the filenames collection
Minute 9
Like such an array of strings with the current index and then the update links to set up false bollocks at the defaults and everything else so the book is not open to set already open to false so this point the workbook should be open so this is where you d add code to process workbook so was his show MessageBox here
Minute 10
Nothing really to do here if it is something that users don t initiate just update these status for the other Excel and then finally going to either close a workbook or leave it open depending on my already open veritable and we must save the changes I was in the leave the workbook open semi exception into shall be exception message
Minute 11
A system of setting be says for here before I is a new recipe is that he says are equal to false which removes your message and gives control back over to excel so far thing Anita do not excessive though you had a as simple button on the ribbon here and in that when you click the button hero Stu globals this workbook process files
Minute 12
And so demonstrate how his works to end the seventh click on the button just to pick some 2003 style workbooks are still coupled the suit should workbooks and presumably had this about racism or before closing and saving them because if I go ahead and open workbook and switch back to
Minute 13
To the workbook doublecross files and of the click the button inducing thing here in for the SingTel is already open one with some of the ones as Google and process all files and then also like to view your 2 D switch windows you can see the tricking files to open that includes his video now to quick shooter to batch process Excel files defrosting everyday

Click here to go to original video page