Microsoft word we'll then look at a longer example in excel showing how you can take a full vba program and translate it into python and some of the issues you'll encounter in doing that we'll then spend the rest of the tutorial looking at an outlook example so i'll show a slight digression on enumerations which i don't think we've done in any tutorial so far before showing you how you can read an outlook folder and get the messages from it and then showing you how you can loop over the messages downloading their.
Attachments at any point you'll be able to click on the link at the top right of the screen to download any files or exercises to do with this tutorial and you can also get at this link from the youtube page if for some reason it doesn't appear but that's enough of me i'm going and my colleague sven will as always guide you through the rest of the tutorial so let's get started.
What's this tutorial about well i've got here a very out of date help page for the different object models in the microsoft office applications as called so for example if you are going to write a program within excel vba the object models or objects you're likely to be manipulating are things like workbooks and charts if by contrast you're working within powerpoint the sort of things you'll be manipulating are presentations.
And i could draw an analogy for each of these so if you're working within words you'll be working within documents and characters and tables if you're doing it within um project you'll probably be working with timelines and gantt charts i'm not sure and if you're doing it within access you'll be working with tables and record sets and so on so each of these applications has got what's called a component object model and the component object model or com for short basically says what sort of thing that that application provides which you can manipulate in other.
Programs so for example it's perfectly possible to write a program in excel which will create a word document or to write a program in powerpoint which will bring information in from access so if the different microsoft applications can talk to each other by the things using the things exposed by the component object model why can't python also use the things exposed by the component object model and the answer is it can and that's what this tutorial is about.
So i've created a simple file called simpleprogramexcel.py and what we're going to do is use this to create an excel workbook and type your name into the top left cell something like that just as a proof of concept so to do this the first thing i need to do is import the necessary module which is win32com dot client and regular viewers of this won't be surprised to know that that doesn't yet exist and so what i need to do is go to a new terminal window an existing one and type.
Pip space install space pi win32 so it's not you don't type in the same thing as the name of the module you import and if i press return to run that you'll see it's installing the modules i need and it accepts win32 com.client at the top now so what i'm going to do is launch excel from within python quite an exciting.
Thing to do really and to do that i'm going to create a variable which i'll call excel and i'll set it equal to win32com dot client dot dynamic oh sorry dot dispatch and then in brackets what i need to do is specify the unique user handle or handle for that application and i can do this by either typing in the number of it which i'm unlikely to.
Know or by typing in the name which is usually excel dot application or word dot application or something similar to that so hopefully that will launch excel the next thing i'm going to do i'll just shut that distraction down the next thing i'm going to do is make it visible and i can do that by saying excel dot visible equals true and the reason i need to do that is because otherwise i can do all the programming i like within excel but i'll never actually see the results on screen.
Which would be disappointing so the next thing i'm going to do is to create a new workbook excel and i'll call it a new book and to do this i can take the excel application i can take the workbooks collection within it and apply the add method to it and seasoned vba programmers will be feeling a nice warm sense of reassurance at this point that i've just written in a fairly standard line of vba code the difference is i didn't have to in.
Fact i couldn't dimension this variable as an excel workbook but there's another difference you have
To put open and close brackets after methods so if you're copying this directly from vba it wouldn't work so there's lots of little gotchas like that coming up so what i can now do is type name into a1 so to do this i can say excel dot range.Value equals and i'll put my name in which is wally owl or at least it is for the moment actually it's then isn't it and that should now work so what i'm going to try doing is running that program um i'll just close down any other excel applications i may have open without saving them to make sure i've got a clean excel and if i run that program you can see it.
Does indeed create a new workbook and it types my name into the top left cell so that's worked um and so that's how you can create a basic uh program in vba from within python i've done an example using excel now let's create a program in python which will create a new document in word and type your name maybe or something similar to it in a new document.
So to do this i need again to import the win32com.client and what i'll then do is launch word so i'll create a new variable i'll call it wd and i'll take win32.com.client and i'll dispatch a very strange word for launching a program i always think and this time the unique name for the program is word dot application and then what i want to do is make that visible it's very easy to forget that and very.
Disappointing if you do and you'll notice it doesn't come up with intellisense auto completion it could do it just doesn't i've included in the useful links.txt file a link to a very useful site which tells you absolutely everything about python and com it's way beyond the scope of this little tutorial this humble little tutorial uh but it does explain how you can set uh python up to use early binding and to get into intellisense to appear it's not for the faint heart to be warned but it is.
Possible but i'm going to i'm going to be happy with not having intellisense appearing so make this visible by setting the visible property to be true i will add a document so the command to do that in word is documents.add so all i need to do is prefix that with the containing application but i also need to type in open and close brackets to make it into a method then i can type something in and the.
Command to do that is wd dot selection dot type text all of which you could get from intellisense within word and then just copy into python and i think what i'm going to type in is my alt alter ego of wilbur and you can see that because this is a function you have to put that in open and close brackets which you wouldn't have to do in vba.
Okay so let's try running that and what you should i hope see is it creates a new document and types your name and there it is so you've got complete control over all the different microsoft office applications so let's have a look at taking an existing excel program in vba and porting it into python and see what sort of obstacles you may overcome so what i'd like to do now is to explain the example we're going to translate from excel into python.
I've got my list of films here and this file is a dot xlsm file so it contains macros and it's attached to the list of files for this tutorial and what i want to be able to do is to write a macro which will allow me to type in a film name and it will go to it and change the number of oscars so for example if i type in b movie and the number five it will go to the b movie and what it will actually do is this it will go from there to the end of the row then it will go two cells back.
And then it'll change that number to whatever number of oscars you've typed in and the only reason i'm doing it that way is i want to show you going to the
End of a block and off setting as well otherwise i could have done it more simply okay so let's have a look at the code for this because i'm on windows 11 i can split the screen like this it's wonderful and there's vba so what i'm going to do is just make sure you can see both of them at the same time and let's try now.Running this so what it will do is display an input box and ask me how many oscars i want display another input box and ask me what film i want it will go to the film sheet the film sheet is just down here so you can see that's one it's going to go to and then it would set the film cell and this is why i probably need to actually give a bit more room to take the cells which is all the cells in the.
Spreadsheet and find the film name i've typed in so for example i've typed in b movie then film cell will refer to that cell i'm going to assume i type sensibly i don't type in the name of the film which doesn't exist there's no area trapping in this program what it would then do is set film cell to be not that cell but it will then go to the end to the right so we'll go over to the right like that.
And then it will set the value of the cell 0s down two columns to the left because i put a minus sign in so set the value of that cell to be the number of oscars i've typed in okay so let's try running this so if i press i'm going to press the f8 key to run it and what that would let me do is step through it line by one line i'll say seven oscars and then for the next question i think we'll go for be a wolf because i'm actually getting a bit sick of using the b movie as an example and then if i press f8 again.
It will go to the end of the block and what it will now do and i just need to convince you that this is going to work when i press f8 it will change that number to seven i don't if you saw that happening and then it will finish so that's my program and what i'm now going to do is copy it there's no point copying the sub ask update oscars because that won't work but i'll copy the rest of it to my clipboard and what we're going to do is have a go getting this to work within python.
So i've created a file a file called translated excel vba.py and the first thing we'll do is import the win32com.client because we're going to need that i can now do rather nervously is paste in the vba and unsurprisingly i get quite a few errors there's a fair amount of dude amount to do to clear this up i'll select all that and press shift tab and what that will do is move it over to the left margin there's no point whatsoever in declaring.
Variables because you can't do it in python if i select this apostrophe that denotes a comment in vba in python of course it's a hash symbol so i'll press shift ctrl l and type in hash space and then just press escape to clear that so i'm back in normal typing mode input box is just input in python so again i'll press shift control l type in input and then press escape to clear that.
You can't have more than one thing for the input as far as i know and if you can they don't mean what they mean in vba so i'll just get rid of that variables variables and lower case in python and it's a good idea to follow coding conventions so what i'll do is select that press shift control n again type in my new variable name which is number oscars with an underscore and then press escape to clear that i think i need to press it twice and then click somewhere else for the film name i'll do the same thing.
Shift ctrl l i'll type in film underscore name and then when i finished i'll press escape and clear it things are going well film cell likewise i probably need to rename so uh control l type in film underscore cell like that so you can see i'm creating some other problems as i go along what i now need to do is launch a new.
Excel copy of excel and open up the file i'm working within so to do that we launch excel so i'll set excel the variable called excel equals to win32com.client.dispatch and i'll open up an excel dot application i need to do that because in vba within excel i was already working within the application but now i'm writing this externally so i need to make sure i've opened up an excel application and i also need to open up the correct workbook.
So to do that i'll create a variable called book let's call it and i'll specify excel.workbooks.open so that's exactly analogous in vba you would just type workbooks.open and then in brackets i can specify the name of the file i'm opening and i hopefully it's in my clipboard there so that's the file i'm opening okay so i've opened the file i can get the number of oscars to get the film name.
I now try to select the film sheet that's not working because i need to say which book it's within so i need to put book dot worksheets and you can see the error disappears we're not getting where the film is you can't use the word set it doesn't exist so i need to just put film cell but i need to also prefix this with excel to say that i'm looking for the cells collection within excel rather than within python on this next line i need to set film cell equals.
And it doesn't like the word excel to write and there's a good reason for that so i think i've stopped most of the problems let's look at that word excel to write so this is a constant in vba now i've read some things on websites which suggest you can replace it with win32.com.cliend.org and whether you type constant with a little c or constants with a big c i cannot get it to then recognize.
The any version of excel to write so it may be possible to do that way but i can't get it to so instead of doing that what i'll do is go to a search bar and i'll type in excel vba and excel to write and if i go to the microsoft website for that you can see it says range end property represents a range object and here's what i wanted the directions i can go in if i click on that it's called in enumeration.
And there's four possible directions the one i want is to go right and that corresponds to the internal number -4161 so what i can do is copy that probably you wouldn't have guessed that paste it into my gap there and that will have exactly the same effect going to try running this once using the input function it will be a bit messy i think but let's give it a go so if i press ctrl f5 i can't use code runner because you can't have an input function with that and if i type in here the number of oscars let's go for 55.
And let's go for beowulf you can see i've got a few formatting issues with this um when i press return has it actually worked i can't see any evidence of it and i find it very very difficult to debug this so i'm actually going to cheat i'm going to take out the input statement and for the moment in fact let's leave it in but just comment it out i'll set this to 42 and i'll just put a hash in front of it and for the film name i'll set this to be beowulf.
And again i'll put a hash in front of that and i'm doing that because i want to use code runner because i find it very hard to debug otherwise so if i now try running that let's see if anything happens and the answer to that question is emphatically no i suspect a lot is happening i just can't see it what i've forgotten to do rather foolishly is to make it visible so let's try adding in excel dot visible equals true of course i didn't need to do that in vba in excel because excel is visible automatically and let's try.
Running that again now that looks more promising because at least i can see something in excel and you can actually see it's worked it's changed number to 42. except it's changed the wrong number so it found be a wolf it went to the right hand side and then it was meant to go two cells back to the left and on the same row and it certainly failed to do that and this is just the last piece of the jigsaw we need to solve the reason that happens is because for some reason which.
Completely escapes me and most other people instead of using offset you need to put get offset in so there's something which you probably might not have guessed and if you now try running that you can see that it puts 42 as the oscars for beer wolf what all this shows is it is possible to take a vba program and translate it into python but in doing so you're going to have to solve quite a few little glitches along the way but the good news is i think.
I've shown you the main ones you'll encounter so the last part or part of this tutorial are going to be devoted to working with outlook it's quite scary how easy it is in python to download emails and even attachments from your outlook pst file you don't have to have outlook open which is the scary thing about it so it seems to me to bypass all of the protection in place but hey if it's useful so i've created a junker folder within my inbox and i've copied three of the 7.
366 unwanted emails into that so i've got one which has an attachment which is a word document one which has no attachments and one which has two attachments which are pictures incidentally i'm not in any way meaning to promote these people i wish they'd start writing to me so to begin this process what i'm going to do is import a module into a new file i've created called outlook attachments.py so from the enum which is a built-in.
Module i'm going to import enum what on earth is enum well i've got something in my clipboard and for once i'm not going to type it in i'm just going to paste it in because this is something i haven't really explained but it'll be obvious how to use it this is called an enumeration and what it does is allows me to refer to numbers in a more meaningful way so for exactly for example i could refer to the outlook inbox as six.
But it's much easier to refer to just ol folder inbox because it makes the code easier to read and easier to interpret so as one example if i wanted to print something out i could print out the outlook folder print out folder.inbox dot value and what that will do when i run the program is print out the number six so that's what an enumeration is it's just a way of representing a built-in.
Number which would otherwise be meaningless by a more descriptive bit of text so what i'm going to do is to list out the messages in the folder the junk folder and then in a separate step i'll list out their attachments so i'll just get rid of my old print statement and i think i'll do this using a function because i think otherwise the code will get quite hard to read so i'll create one function which returns a list of the messages and then maybe another which returns a list of the attachments.
Within them so let's start with the one to get a list of the messages i'll call it get messages it seems a good good name and i'll pass in the folder name which will be the word junk in this case i'll assume it's within the inbox and that will be a string and the whole function will spit out a list of messages to be constructed so get or return rather a list of messages in given folder the first thing i need to do is import the win32com.client module.
And then i need to start outlook so i can do this in the same way as i did with excel and with word so i can set outlook equals win32com.client.dispatch whoops.dispatch and then it's called outlook.application up to that point outlook is just the same as excel and word but then it gets a bit murkier because i need to get to the email within that so outlet contains emails and calendars and tasks and other things and to do.
This i'll create a variable which i'll call mappy for reasons which may become apparent and i'll set the sequel to outlook and within that i will get a namespace called mappy i can't remember what to stand for stands for i did google it sometimes messaging application programming interface or something like that but basically it means you can get the emails within outlook so now i've got that i can get a reference to my folder so i'm going to do this in two parts.
Firstly i'll get to the inbox so i'll create a variable why not call it inbox which will take the mail part of outlook and get a default folder which default folder this one outlook folder dot or folder inbox dot value so i could have just put the number six in there to do the same thing so now i've got a reference to the inbox i can get at the junk mail folder within.
That see that's a very bad comment because i'm implying it's always going to be called junk but i've written the function in such a way i can refer to any subfolder of the inbox so i'll create a variable called subfolder and i'll set this equal to inbox dot it has a collection of folders and i want the item within that notice how strange the uh syntax is getting called not junk but uh folder name that's what i passed.