Hey, Scripting Guy! How Can I Set the Default File Path in Office Excel to a User’s Home Directory?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I set Office Excel’s default file path to a user’s home directory?

— LL

SpacerHey, Scripting Guy! AnswerScript Center

Hey, LL. We hope that the script we’re about to show you will answer your question; if it doesn’t, you might have to wait awhile before we can get back to you on it. That’s because the Scripting Guy who writes this column isn’t even at work today; instead, he’s spending the day at King County Superior Court in Seattle.

You know, that’s what everyone thinks when they first hear the news; as it turns out, though, it’s not illegal to write a daily scripting column that talks about pretty much everything except scripting. (Whether that should be illegal is, of course, a different question.) As it is, instead of being on trial himself the Scripting Guy who writes this column received the “honor and privilege” of being selected for jury duty. Consequently, he’ll be spending the day hanging around with hardened criminals, miscreants, and other low-life types.

Which, now that we think about it, pretty much does describe a day spent hanging around with the other Scripting Guys, doesn’t it?

But don’t worry. While the Scripting Guy who writes this column is off battling for truth and justice, he did leave a little something for everyone to remember him by:

Set objUser = GetObject("LDAP://cn=Ken Myer,ou=Finance,dc=fabrikam,dc=com")
strHomeDirectory = objUser.homeDirectory

Set objExcel = CreateObject("Excel.Application")
objExcel.DefaultFilePath = strHomeDirectory
objExcel.Quit

As you can see, what we have here is a two-part script: in part 1 we retrieve the user’s home directory, and in part 2 we set Excel’s default location to that home directory. To determine the user’s home directory we start out by binding to the appropriate user account in Active Directory. We then use this line of code to grab the value of the homeDirectory attribute and store it in a variable named strHomeDirectory:

strHomeDirectory = objUser.homeDirectory

You know, come to think of it, part 1 was pretty easy, wasn’t it? That’s good. And the good news just keeps on coming: part 2 is equally easy. In the second section of the script we start out by creating an instance of the Excel.Application object. As soon as Excel is up and running we then use this line of code to assign the value of the variable strHomeDirectory to the DefaultFilePath property:

objExcel.DefaultFilePath = strHomeDirectory

That’s it; all we have to do now is call the Quit method and terminate our instance of Excel.

And you’re right: in most of our Excel scripts we use the command objExcel.Visible = True in order to make Excel visible onscreen. Why didn’t we do that here? Well, for one thing, the script takes only a second or two to run; no sooner would Excel appear on screen then we’d be calling the Quit method to get rid of it. On top of that, all we’re doing is setting an Application object property; as a result, there’s really nothing to see onscreen anyway.

You say you have another question: how will the Scripting Guy who writes this column decide whether someone is innocent or guilty? To be honest, he doesn’t know; after all, he’s never served on a jury before. He does know one thing, however: whoever the accused happens to be had better hope that he or she gave the Scripting Guy who writes this column a good evaluation score during Windows PowerShell Week.

Not that we Scripting Guys keep track of that sort of thing, mind you.

0 comments

Discussion is closed.

Feedback usabilla icon