Random Numbers
At times, we need to generate random numbers to simulate a scenario. It can get a little irritating to keep on typing random digits all the time. To generate a random number, use the RAND() command.
For example, to generate a number between 0 and 500, type:
=RAND()*500
random number for multiple cells
Similarly, randomly generating numbers between any two numbers can be done using RANDOMBETWEEN().
For example:
=RANDOMBETWEEN(50,100)
will generate numbers between 50
and 100.
Quick Graph
One expects to go through a dozen steps in a Wizard to make a graph. Assuming that the graph data is basic, simply pressing [F11] will create a graph with the selected cells instantaneously.
Graph creation is a single key-press process
That’s not all! In case you don’t like the graph that’s been created for you because it’s chosen the wrong fields or the style doesn’t suite it, you can right-click on the graph and change the graph type or the cells selected.
Insert The Current Date And Time
This can be done using shortcuts. Pressing [Ctrl] [;] will add the date; pressing [Ctrl] [Shift] [:] will add the time.
Quick-sort Data
If you end up creating a huge table filled with tons of data and want to easily sort the data according to a certain column or row, select the cells that you want to sort. Then, click on the Data ribbon and click Sort. You will then be shown a Sort window where you can choose the columns you would like to sort by.
The Quick Access Toolbar
This is a toolbar that can enabled by right-clicking on the Ribbon and choosing Show Quick Access Toolbar Below the Ribbon. This toolbar is a space where you can choose to place your most commonly-used tools. To add a certain button to the Quick Access Toolbar, right-click on the particular feature and choose Add to Quick Access Toolbar.
Navigate Through Tables
Assume you have a big table of data on a worksheet and you want to easily move through it without going beyond its boundaries. Here’s a way to do that: first you select the region of cells you want to set as the table. Press [Enter] to move vertically downwards. Pressing [Shift] [Enter] will let you move vertically upwards.
Limit movement within a highlghted selection
Similarly, [Tab] will move horizontally across to the right, and [Shift] [Tab] will do the opposite: move to the left.
Moving to and from to each extreme end of the table-the corners-can be done by pressing the [Ctrl] [.]
Replicate Worksheets
There may be cases where a copy of an existing worksheet is required as a blueprint to work on; you can make a copy of it by pressing [Ctrl] and left-clicking on it and dragging it to the location where you need it.
Delete Blank Rows
Blank rows make their way into spreadsheets whether we like it or not. Sometimes it could be because of missing information. The quickest way to delete blank rows is to highlight the cells and click on the Sort button. All the blank spaces are removed leaving only rows with data on them in the table.n off this option to save memory.
Formatting Cells
Very often, users come to a point where their values take a different form. Usually, the formatting is at fault. It could be decimals or the format used to display dates and times. You can change this by selecting the cells and then right-clicking on them, and clicking Format Cells. A wide range of choices is available for every type of number.
A Mouse-free Experience
Moving a mouse to the different menus and toolbars very often during your work can be a little inefficient at times. The easy-to-use shortcuts are highly recommended. One does not have to remember three key combinations to do a particular task. All the shortcuts are displayed on the ribbons.
Shortcuts to features in the ribbon
Pressing [Alt] [R], for example, will show you the Review Ribbon along with the additional keys for each of the tools on it. So pressing [C] now will create a new comment.
You can also be using a particular Ribbon, and pressing [Alt] will show up the shortcuts for that Ribbon. Once you get used to this technique and slowly start remembering the keys it can save a lot of time.
Move Cells Around
Moving a certain cell around would mean cutting and pasting the value onto another cell. Another way one can move chosen cells around is by moving the mouse pointer over the border of a particular cell till it becomes an arrow. Then move the cell to the chosen destination and let go of the left-click.
Resize Scrollbars?
When there is only a single worksheet to work on, the extra tools meant for navigating between worksheets become useless. This space can be made useful by clicking the edge of the horizontal toolbar and then dragging it.
Show All Formulas
Normally, viewing the formula used in a cell includes clicking on each and every cell. Instead of doing this, a view for looking at all the formulas on the worksheet at one glance can be enabled by clicking on the Formula ribbon and then on Show Formulas. All the formulas in each cell on the worksheet are immediately displayed.
The Same Old Gridlines
Gridlines is probably what most Excel users are staring at most of the time. Try some other colours for the gridlines to lighten things up a bit.
The option to change gridline colours
In Excel 2007, you can change the colours by clicking the Office button and then on Excel options. Click on Advanced and then scroll down till you come across Gridline Colors.
If you find that many of your toolbars and options are missing, they are most probably hidden away. Each of the toolbars only appears when the related type of action is done. For example, if you select a graph, the Ribbons such as Design, Layout, Format, and Analyze appear.
Multi-sheet Working
Multiple worksheets can be allowed to receive the same data being added to a particular sheet. Press [Ctrl] and go on left-clicking on the worksheets that you want to add data to. Then enter the text or numbers you want to replicate onto all the sheets.
Saving Paths For Images
While inserting images for headers and footers, it’s important to save the paths for header and footer images. To do this, click on the Page Layout Ribbon. Click on the arrow at the right bottom of the Page Setup section. Click on the Header/Footer tab and then on Custom Header OR Custom Footer. Here you can manually enter the path of the image-which might be on a network, for example.
Divide Worksheet Space
Worksheets are bound to get very large, and navigating through one can get hectic. The best way to reduce the large amount of scrolling required to move from one corner of the table to another is to split the worksheet. It makes a window, so to speak, which lets you view one area of the worksheet while you use the main region as well.
Creating virtual partitions help in navigation
To do this, look for a tab at the right bottom of the screen where the two scrollbars meet, click on it, and drag it. The newly-created division can also be disabled by clicking on the divider and moving it to the extreme end of the screen.
Selecting A Certain Type Of Data
To quickly select a type of data in a spreadsheet, press [F5], which is the shortcut for Go To. Choose Special and choose the type of data you want selected. Excel will automatically highlight that data type.
Tracking Changes
If you’re working on an important spreadsheet and are not sure whether the changes you’re about to make are the right ones, the Track Changes feature will alert you of the cells on which any modifications were done. To enable Track Changes in Excel 2007, click on the Review Ribbon and then click Track Changes.
Once the changes are done, you can move the mouse over the now-highlighted cells and a pop-up shows the original and the current value of the cell.
Copying Elements To Other Office Applications
Very often, one might need to move, say, a spreadsheet table or a graph to another Office application such as Word or PowerPoint. These applications work well hand-in-hand, so like you would normally copy and paste text within Windows applications, you can select a image or table, press [Ctrl] [C] to copy and then [Ctrl] [V] in the other program.
Quickly Select
When doing loads of work with several sets of smaller tables, the easiest way to select a table is click the cell at one corner of the table, then press [Shift], and then click the other corner of the table.
Merge And Center
Excel’s new Merge and Center feature lets you align and format text with ease. You first enter your text, such as your heading or name of the columns, and then select these cells. Simple go to the Home Ribbon and click Merge and Center under the Alignment section.
Pivot Tables
Pivot tables are a way to easily access information entered into vast tables. The Pivot Table checks through the various cells in the rows and columns and figures out the headings.
Pivot table creation help in accessing data
To create a Pivot Table, click the Insert Ribbon and then click PivotTable > PivotTable. Highlight the area of cells containing the table. The next step will involve telling Excel what fields to use in the Pivot Table. Your Pivot Table is ready. You can also choose to make a Pivot Chart in a similar fashion.
Widen The Audience
Excel spreadsheets are no longer limited to local drives and then mailing them to your recipients. With the Publish to Excel Services, people on the Internet or a network can access the spreadsheet. To do this, click on the Office Button at the left top corner of your screen and click Publish > Excel Services.
External Data Sources
The latest Office suite allows the user to grab information from external sources such as the Internet. An example of a connection is the MSN MoneyCentral Investor Currency Rates. To make a connection, click on the Data Ribbon and click Connections. Click Add and choose one of the available connections, or choose to load a third-party data source. Click Open, and Excel will retrieve the information from the source and dump it onto the spreadsheet.
In the same manner, data can be acquired from other sources such as SQL servers. This option is available when you click on Data > From External Sources.
Access files can be used to import data from by clicking on the From Access button on Get External Data. Similarly, the From Web button opens up a Web query where you can select a table and have it imported into Excel.
Visual Relations
When formulas are used for calculating numbers, there is no clear way to find out how a particular value is derived. To look at the cells affecting the value in a visual representation, first click on the result cell, then click on the Formulas Ribbon, and finally click Trace Precedents. Lines and highlighted boxes will be drawn displaying the various elements involved in the calculation.
Evaluating Existing Formulas
Chances are you might wonder if the calculations made to get a certain result are incorrect. It could be human error, and you would want to see at what step the calculations go wrong. The Evaluate Formula option, which can be accessed from the Formulas Ribbon, lets you do just that. Click on the Evaluate Formula button while highlighting the cell with the formula. The next window will show you all the steps involved in the calculation.
Lighten Up Your Worksheets
Using a plain black and white table can be boring, so themes are available which can be used to colour up tables in a workbook. Office 2007’s themes are applied as you move your mouse pointer over them. So click on the Page Layout Ribbon and then click on Themes, and select a theme from the list. Themes are add-ons available on Microsoft’s Excel site.
The Name Manager
The Name Manager is like a database of certain tables in the various spreadsheets in your book. It is very useful to quickly gain access to these tables. You can add entries by first starting the Name Manager, which can be found under the Formulas Ribbon. Click the Name Manager button and give a name for the selection you’re about to add.
Maintaining groups of selected cells is easy
Next, for the Refers To entry, enter the range of the cells manually, or highlight them using the mouse. Click OK and the selection will be added to the database.
Add-ons For Excel
Excel supports add-ons, which can be loaded and unloaded according to your needs. There are even add-ons which let you read data from PDF files and get this data into Excel.
To enable or disable add-ons, click on the Office Button and then on Excel Options. Click on Add-ins, and a large list of available add-ons will be displayed. Additional Add-ins are being developed and can be downloaded from Microsoft’s Excel site.
The Office 2007 Open XML Format
The latest Office suite stores data in its files in a different manner. Excel inherits the same characteristics. An Excel file, for example, when renamed to .zip, can be opened by any file compression application. All the media-such as images and audio used-can be accessed. The clear advantage to this is that the files are obviously smaller, as they are compressed. The other advantage could be, in case of data corruption, it would be possible to extract the undamaged content. Corrupted older Office documents would mean complete loss.
Changing Names
During the installation of Office 2007, you are required to enter your name. The same name will be used as you go on creating new workbooks and spreadsheets.
Changing the name from the licensing name
There is an option available to change that name. Click on the Office Button, and in the Popular section, at the bottom you will find a text entry space to change the name.
When the default dictionary that comes along with Microsoft Office isn’t doing its job as you want it, you can always refer to other dictionaries.
Adding your own customdictionaries
Dictionary files can be set up for Excel in a few easy steps. In Office 2007, first click on the Office Button. Then click on the Excel options. Click on Proofing and you will find a button saying Custom Dictionaries. Clicking on it will load a small window with options to create your own, new dictionary of words or even load existing, downloaded dictionaries.
Embedding Fonts
Excel usually doesn’t let you embed fonts into your documents, so mostly likely the people opening up the spreadsheets won’t see them either. The trick is to embed your Excel elements into a normal Word file and send it to people. This way, people can finally see the Excel spreadsheet with the fonts you intended to show, but they’ll have to use Word to view them.
Multi-core Power For Excel?!
Yes, Excel 2007 gives you the option to use your dual-core processors to speed up your work. Sure, the performance gain won’t be very noticeable. But if you do have a dual-core processor, you might as well take some advantage of it.
Excel actually “supports” multi-core!
Head off to the Office Button. Then, scroll down till you see a Formulas section. You will find an option to enable multi-threaded calculations, or manually choose the number of processors on your machine.
No More Blank Spreadsheets!
Every time you load up Excel, you stare at a blank workbook which you then close and open your existing workbook. If you want no workbooks starting up by default, you can do so by adding a “/e” parameter to your Excel startup.
To do this, you can create a new shortcut. Choose the location where you want to create the shortcut, the Desktop for example. Right-click on the Desktop and click on New and then on Shortcut.
Click Browse and locate your Office installation. We use Office 2007, so our Excel EXE was located in C:Program FilesMicrosoft OfficeOffice12. Click on EXCEL.EXE and Click OK. Add a “/e” at the end of the path such that the location is “C:Program FilesMicrosoft OfficeOffice12Excel.exe” /e.
Double-click on the shortcut, and Excel will load up without any open worksheets.
Everyone is familiar with Excel, the most popular spreadsheet program ever. However, what makes Excel truly stand out is its ability to support a wide range of formulas. If it weren’t for Excel supporting formulas, we would still be stuck spending half the time [Alt] [Tab]-bing back to Calculator, all because you made a minor change in your spreadsheet.
Most use only the most basic formulas; few are aware of the rather wide range Excel supports. We’ll be covering most formulas you can use with Excel; some you will use often, while some you will use rarely, depending on your needs. These formulas are tested thoroughly on Excel 2007, and though just a few, we have included Excel 2007-specific formulas as well.
Basic Formulas
These are formulas which aren’t too complicated and are easy to remember. Also, these are what you will be using the most in your spreadsheets. The first few, while being rather simple, are included to help you get a feel of things.
Addition / Subtraction
These functions, while not being exactly formulas, are the most basic functions used in Excel and have been included here for the sake of those new to formulas. Needless to say, any Excel file with formulas will contain these basic functions the most. You can perform addition or subtraction between data located in cells. For example, if you want the total of all numbers in cells A1, A2, A3 and D6, and you want to subtract the total with a number situated in cell D8, and you want the grand total to be displayed in cell F10, you can do so by going to cell F10 and typing in =A1 A2 A3 D6-D8
Note that when you change the data in any of the cells F10 mentions, then the result will change on its own without requiring you to recalculate the result. Like in the example above, you can use mathematical functions randomly, that is, you can add the data in cells A1 and A2, and subtract the result with the data in cell D6-just change the ” ” sign to “-” when you need it. A small tip: if your formula consists only of additions, you can use the SUM function instead of putting in the plus sign between the cell numbers:
=SUM(A1:A6)
This will give the sum of the values entered in the cells A1 to A6. Should you need to add in another cell that isn’t sequential, for example, you also need the add the data in cell D7 in addition to the above, you can enter in the following:
=SUM(A1:A6, D7)
There, a comma is all that’s needed if you need to add more cells to the formula. This method of including cells is valid for any other formulas you work with.
A thing to remember is that you can also use constants at any time when needed in your formulas. For example, you might want to subtract the total of the numbers in cells A1 to A6 by a specific number, say, 50, which is not in any cell. Simply edit the formula and stick in your constant like in the following:
=SUM(A1:A6)-50
Multiplication And Division
These functions are quite simple to use at the outset, but there was a reason we didn’t stick it in with the addition and subtraction functions. We’ll look at the reason soon. First the regular functions:
=A1*A2/A3*D6
Simple enough: use the required signs when necessary. However, the answer can differ depending how the calculation is done. You can choose how it’s done with the power of the brackets:
=(A1*A2/A3)*D6
Or
=(A1*A2)/(A3*D6)
Also similar to our SUM function, we have a function for multiplication called “PRODUCT”. You can use it like so:
=PRODUCT(A1:A6)
You should have figured out that this function will give you the product of all cells from A1 to A6. However, there’s a chance that you might encounter a problem should one of the cells you included in the above formula contain a value of 0 (which you don’t want to include in the calculation), in which case your result will end up in a straight zero. You can use this solution to avoid the problem:
=PRODUCT(IF(A1:A3,A1:A3))
Careful when entering the brackets here; this formula should give you the product of the cells A1 to A3 while ignoring the zeroes.
Calculating Percentage
Again, one of the most basic, yet useful functions: you can calculate what percentage an amount is out of the total, assuming you have both values. For example, you can see scores in percentages instead of the actual scores. The formula:
=E4/E5
As usual, enter it in the cell you want the result to come into. E4 pertains to the amount, out of the total, which is E5. While this looks like a simple division formula, you can change the view to percentage by clicking on the Percentage icon on the formatting toolbar.
Increase or Decrease a Number By A Specified Percentage
There are more interesting things you can do with percentages, like increasing or decreasing your values by a set amount of percentage. For example, you can use this function if you want to increase someone’s salary by 35%:
=E4*(1 35%)
When increasing the percentage by a value situated in another cell, the “%” isn’t needed as long as the cell itself contains the “%” sign. You can simply use
=E4*(1 E3)
Here you need to make sure that E3 contains a “%” after the value, like “30%”. Or you can simply change the formula itself to look like
=E4*(1 E3%)
For subtracting by a certain percentage, simply replace the ” ” operator with a “-” sign.
Calculating Averages
A slightly more complicated formula but still useful and easy to understand. The “AVERAGE” formula will display the average of the numbers located in several cells. This comes in most handy when calculating averages for students’ mark-sheets.
=AVERAGE(E1,E2)
If your data is in a sequential line of rows or columns, you can simply enter something like:
=AVERAGE(E1:E3)
This will include all the values in the cells E1 to E3 to calculate the average. Remember, you can do this for just about any formula as long as the data is aligned sequentially. Again, similar to multiplication, you can edit this formula accordingly so that it doesn’t include the zeroes or blanks that may be located in any of the cells E1 to E3:
=AVERAGE(IF(E1:E3,E1:E3))
Note: In such formulas, including the ones that follow, you might have to press {Ctrl] {Shift] [Enter] after typing the formula into the cell.
Interests
Interest is a function used regularly. This formula will allow you to know the interest you have to pay every month when you have access to the info of the total loan received, the interest rate, and the number of payments you have to make. You can do this with the following formula:
=PMT(E2/12,E3,-E1)
E2 is the rate of interest, 12 indicates a monthly payment, E3 denotes the number of payments in months, and -E1 is the total amount of loan received.
Root And Square Root Functions
These functions can be useful occasionally. The syntax for this function / formula is rather simple. For square root: =E1^(1/2), and for cube root, =E1 ^(1/3)
The above formulas will give you the square and cube root results of cell E1. However there’s also another way to write these formulas, by directly using the square and cube functions, which is, for square root, =SQRT(E1), and for cube root, =POWER(E1;1/3).
Amount of Time Passed Since a Specified Date
On we move to the date and time formulas. This one will display, in a cell, the amount of time since a date specified in another cell. An example:
=YEAR(TODAY())-YEAR(E9)
Be careful when entering the brackets. The date entered in cells is in MM / DD / YYYY format (Month, Date, and Year) by default in Excel 2007, so make sure you get the format right. (Or else mess around with the International settings in the Control Panel.)
If you need to display the number of months or days instead of years, use the following examples. For displaying the amount of time passed in months:
=DATEDIF(E9,TODAY(),”m”)
In days:
=DATEDIF(E9,TODAY(),”d”)
Calculating the Number of Days in a Specified Month
Suppose you have the months listed in column A, while the year is specified in column B, and you want to find out the number of days a month contains. This formula helps you find exactly that. Move to the cell you want the result to come in and enter:
=DAY(DATE(B5,A5 1,0))
Edit your formula accordingly. In the above example, B5 is the cell containing the year, such as “2007”, while A5 contains the month, such as “02”. remember that Excel works with numbers, and the formula will not work if you entered “February” in cell A5 instead of “02”.
(We continue this series next month.)
EWhen it was released a year ago, Oblivion quickly became one of those titles that brought graphics cards to their knees. While the expansive game itself along with the open-ended gameplay have their attractions, it definitely doesn’t hurt that the fourth instalment of The Elder Scrolls is one heck of a visual experience!
The downside to all the amazing scenery and abundance of HDR effects is, of course, the nosedive in recorded frames per second. Yes, Oblivion kills most cards off right away; the few that survive stutter along. Of course, turning down all effects-ditto the resolution-cures this, but then you miss out on the visual element.
We can’t work miracles here. Oblivion will not run satisfactorily on anything less than GeForce 6600GT and ATI x800 class cards. While the GeForce 4/ATi 9xxx series aren’t supported, the GeForce FX series will stutter at all but the most basic of resolutions. We’re assuming you have a decent graphics card.
Low LOD, textures low, grass off and 250 fps
Max details, notice the castle (fog is due to clouds and not textures) 82 fps
Besides the numerous options in Oblivion’s in-game menu itself, here are some hidden options that can be tweaked. All these are available in the oblivion.ini file in your My DocumentsMy GamesOblivion directory. We suggest you back up
the file!
GRAPHICS SETTINGS
bAllowConsole
Setting the value of this key to 1 will allow access to the in-game console. There’s nothing to be gained from keeping it at 0, so you may as well!
ShadowMapResolution
This key determines the resolution of shadows in the world of Oblivion. Since shadows can tax older systems a lot, we suggest you keep this value down if you experience framing. Try experimenting with values between 64 and 256. For those with latest-generation cards, go with 512 or 1024. You can change this number in multiples of eight.
bDoCanopyShadowPass
When this key value is set to zero, it will remove shadows from trees, which can boost fps especially in forested areas.
bUseRefractionShader
Setting this value to 0 will greatly boost frames in areas where transparency effects are used, such as around Oblivion Gates, or the shimmering effects around invisible characters.
bAllowPartialPrecision
This specifies whether the shaders run in partial precision DX 9 mode or not. For the best possible image quality, set this to 0. For a decent compromise, set it to 1.
iMinGrassSize
This one controls the density of grass clumps, which are visible pretty much all over Oblivion maps excepting interiors of course. The fps will experience a nice boost in areas of dense grass; however, lowering this value too much will thin out the grass too much.
MEMORY SETTINGS
Oblivion is a memory hog too! Here’s what you can do to help:
uInterior Cell Buffer and uExterior Cell Buffer
This tweak determines the number of cell buffers for interior and exterior areas of the game. Obviously, exterior areas need more memory. The default setting, 3 and 36, works well for less than 1 GB of memory. In case you have 1 GB, set the values to 6 and 72 respectively. In case of 2 GB, double that-12 and 144, and so on. This tweak works well in conjunction with the next one.
iPreloadSizeLimit
The value here represents, in bytes, the maximum value useable by the game for preloading data. For a system with 1 GB of memory, try doubling the default value. In case of 2 GB, redouble it. However, setting this value too high will cause crashing, owing to extra game data uselessly lying around in memory.
Pretty-pretty Mods:
Here’s a little collection of mods to make Oblivion look even better, if your rig is up to the challenge. We’re assuming you are already running the game at maximum visual settings and a resolution of at least 1024 x 768.
1. Landscape LOD Replacement 1.1-Visually better distance objects and LOD, this mod is around 28 MB, and is available at www.tessource.net/files/file.php?id=2182
2. Beautiful Meadows-The regular grass texture in Oblivion shows very obvious tiling. This mod reduces the tiling and grassy hillsides look much better as
a result: www.tessource.net/files/file.php?id=2415
3. Higher-resolution landscapes-This mod is large (325 MB) but it effectively updates all the landscape textures with higher-resolution samples, making scenery look that much better. The downside is, it’s very resource-intensive, and so only recommended for higher cards in the GeForce 7 and ATI X1800 and X1900 series cards. Available at http://planetelderscrolls.gamespy.com/View.php?view=OblivionMods.D
etail&id=708
4. Coloured map mod-This mod adds colour to the regular drab-looking Oblivion world map. Only 3 MB, this one is a must-try! Get it at www.elderscrolls-oblivion.com/index.php?option=com_remository&Itemid=16&func=fileinfo&id=126
More power to the double-click!
Few are aware of scripting with batch files, but people don’t use batch files much unless when working with mountainous tasks. Even fewer are aware of Windows Scripting Host (WSH), a scripting environment that comes integrated with Windows to help automate your tasks, and what’s more, unlike batch scripting, WSH has very few restrictions; you can create scripts to create, edit, and delete text files, Registry entries, create users, change passwords… you name it.
Scripts alone can be so powerful that one can even create viruses using it: the dreaded ILOVEYOU virus is, at its core, just a text file containing a script. Don’t despair-creating and executing scripts in Windows doesn’t require you to install any software or compile anything; instead, it requires code-a lot of code. OK, despair a bit, but in the end it’ll be worth it.
To be clear, first and foremost, this is not a tutorial to WSH itself, but rather a collection of scripts for automating certain tasks. So if anyone wants to jump straight to creating and using the script, you can simply type it in and save it as a file. For those hungry for detail, we’ll be explaining each script as it comes. Hopefully, this should be help enough for you to start writing your own scripts.
About the only thing you will need to create and run the scripts is the trusty Notepad. Enter the script text and save the file with a .vbs extension. Whenever you want to use the script, simply double-click the file to execute your script. That said, let’s get down to the scripts.
Start multiple programs and documents at the same time
This one will allow you to start several programs and open several documents upon opening a single file. You can start any number of programs you want in this manner. Individual files will open as if you’d double-clicked on them. This means the files will open the program associated with them. Remember that this function, at its core, is a simple Run command. Anyhow, here it is:
Set WshShell = WScript.CreateObject(“WScript.Shell”)
WSHShell.Run “Notepad”
WSHShell.Run “C:ReportsReports.doc”
WSHShell.Run “C:PresentationPresentation.ppt”
WSHShell.Run “C:PresentationPresentation.pps”
WSHShell.Run “www.google.com”
WSHShell.Run “C:MP3Dragula.mp3”
Simple, isn’t it? This one uses only a single function-“run”. The line at the top indicates we want access to the Wscript.Shell object. The following lines, like they suggest, tell Windows that we want to use the Run function. Remember that these programs will open in order from top to bottom, and you can enter as many run functions as you want. This means you will be able to open your Word file, your presentation document, Notepad, and also browse to your favourite site, all the while listening to an MP3-all with nothing but a simple double-click of a file. No more hunting around for different icons!
Take care not to make little mistakes when it comes to the spaces, quotes, commas, and dots, though the script text is not case sensitive.
Copy and move files and folders
On we come to the main part. This is what you will find yourself using the most. Performing daily tasks like copying / moving files and folders to and fro from one folder to another can be a painful task. Not anymore, not when you have this script at your disposal:
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.CopyFile “C:text*.*”, “C:text1”
FSO.MoveFile “C:test*.*”, “C:test1”
The first line declares the filesystem object, since we want to make changes to the filesystem itself. FSO.CopyFile and FSO.MoveFile are the functions used to copy and move files respectively. In this case it’s all files in the folders “text” and “test”; the *.* characters indicates we want to copy all files from their specified directories on to the destination, whose path is declared at the end. Again, you can use these commands however many times you want in the script for multiple tasks.
Delete files
You probably saw this coming after the last one: you can use the “deletefile” object to delete any files. This is useful when you want to make sure that certain directories are empty or certain files are deleted before you shut down Windows, or simply to clear out all temporary files. The script:
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.DeleteFile “C:test1temp.doc”
Rather self-explanatory. You’re already familiar with the first part by now; the next line has an easy-to-understand function, “FSO.DeleteFile”, followed by the path to the folder or file you want to delete. If you want to nuke the entire directory, use the *.* variable to include all files in the directory specified in the script.
Map a network drive using a shortcut
Ever wanted a simple shortcut to map your network shares to a drive? You can do so using this script. Remember that you can also add as many drives you want to map, similar to WSHShell.Run. Here it is:
Set WSHNetwork = WScript.CreateObject(“WScript.Network”)
WSHNetwork.MapNetworkDrive “S:”, “networkshare 1”
WSHNetwork.MapNetworkDrive “T:”, “networkshare 2”
WSHNetwork.MapNetworkDrive “U:”, “network 2share 3”
Again, the first line asks WSH to declare the “WSHNetwork” object. As mentioned before, since we are making changes to the network subsystem-a network drive in this case-we’ll need to declare it, like we have done in the first line. Further down, we see “WSHNetwork” using the “MapNetworkDrive” function, this is the actual command for creating a network drive. Take note that the drives themselves are in their own set of quotes followed by the network shares. Similar to the Run function earlier, the drives will be mapped in order from top to bottom.
Disconnect network drives
Yes, similar to how you can map your network drives together, you can also disconnect any number of network drives you mapped, thus not requiring you to manually do it every time you shut down your computer. Remember, simply because this is the opposite of the last script, it won’t need your drives to have been previously created by WSH. Here’s the script.
Set WshNetwork = WScript.CreateObject(“WScript.Network”)
WshNetwork.RemoveNetworkDrive “S:”, True
WshNetwork.RemoveNetworkDrive “T:”, True
The first line is used to declare that we want to make changes to the network subsystem. This is followed by the function used in the network object, which is to remove a network drive. This function is followed by the network drive you want to disconnect. Enter this command however many times you want to disconnect more drives; just remember to replace the drive letter. Disconnect commands are executed in order from top to bottom, as usual.
The “True” Boolean at the end of the script indicates to WSH to disconnect the drive even if it’s still in use. You can change this Boolean to “False”, or not enter it at all, to prevent WSH from disconnecting drives in use.
Create or edit Registry values
This script will allow you to create one or more Registry values using a single file. This can be useful when dealing with tweaks concerning the registry, which require you to create values. The script:
Set WshShell = WScript.CreateObject(“WScript.Shell”)
WshShell.RegWrite “HKLMSoftwarenewtext”, “Normal text”, “REG_SZ”
WshShell.RegWrite “HKLMSoftwarenewNumbered value”, 543, “REG_DWORD”
As always, the first line declares the object. The function in the next line is RegWrite, which indicates we want to make changes to the Registry. Next comes the path to the values you want to create, followed by the data you want to input. Finally comes the value type: REG_SZ indicates that it be a string value, REG_DWORD indicates a DWORD number (the value will be stored in decimal format), REG_BINARY indicates a binary value, and so on.
Also, don’t be confused by the “HKLM”; this expands to HKEY_LOCAL_MACHINE. For HKEY_CURRENT_USER, you can use HKCU, and so on. Remember that the steps to create and edit values are the same. This script can create a single file full of all your custom tweaks for the Registry with a mere double-click.
Delete Registry keys
Using this script, you can delete multiple registry entries using a single file. This should allow you to delete even entire keys. Here’s the script for it. Enter it in Notepad and save it with the .vbs extension.
Set WSHShell=WScript.CreateObject (“WScript.Shell”)
WSHShell.RegDelete “HKCUSoftwareEAG”
WSHShell.RegDelete “HKCUSoftwareEAGusername”
The first line declares it’s going to make changes to the shell. The second line-WSHShell.RegDelete-is self-explanatory and asks WSH to delete a Registry key. The key to be deleted is entered after this command in double quotes. You can add as many entries as you want.
We have added two commands for deleting; this is for the sake of explaining the difference between deleting values (the entries on the right-hand pane in the registry) and keys (the folder-like entries on the left-hand pane). The only difference between the two is that you have to include a “” at the end of the path when deleting a key. For example, “HKCUsoftwarevalue” will remove a value called “value”, while “HKCUsoftwarevalue” will delete a key called “value”.
Open important documents located in network shares
As you probably are now aware of the general format of the scripts, this one is an example of how you can combine two scripts to make tasks easier. This script will first map a network share to a drive and then open a document located on it.
Set WSHNetwork = WScript.CreateObject(“WScript.Network”)
WSHNetwork.MapNetworkDrive “S:”, “networkshare 1”
WSHShell.Run “S:reports.doc”
WSHShell.Run “networkshare 1”
The first line is what we explained earlier, declaring the network object and then including the “MapNetworkDrive” function. The second line should map your network share to a drive letter, and the other lines, containing WSHShell.Run, will open a document located in your network shares. Note that one of the lines containing the Run function has S:, a drive letter that was mapped earlier in the script. Were you to put this line on the top, WSH wouldn’t be able to determine where S: is located. This example is solely to explain that you should indeed plan out your scripts as they occur in order from top to bottom. (Yes, we said that already, but it needed mentioning once more.)
Start the Registry editor without remembering the last used key
How often do you hate it upon starting the Registry editor, that the path you were last working on shows up directly in the window? With this script, you can start regedit without opening the last window used. It works by first removing an entry in the Registry that contains the last-used key path, thus clearing it, and then starting regedit.
Set WSHShell=WScript.CreateObject (“WScript.Shell”)
WSHShell.RegDelete “HKCUSoftwareMicrosoftWindowsCurrentVersionAppletsRegeditLastKey”
WSHSHell.Run “REGEDIT”
The first line declares the “WSHShell” object; this is necessary whenever you’re working with scripts that make changes to the Registry or to individual files.
The second line contains the major change, which is to delete the value storing the path to the last-used key; it naturally uses the “regdelete” function, which was explained earlier.
Finally, once the last-used path in the Registry is cleared out, the simple “WSHShell.Run” function explained earlier will start regedit at its root folder.
Mix it up
That’s pretty much enough to get the feel of scripting; you can also combine several scripts together to form a single one, like we’ve seen in the last two examples. Remember that scripts run in order from top to bottom: So make sure you plan out your scripts properly since the earlier functions won’t be aware of the commands below them and won’t be able to execute if they depend on the functions at the end.
If you wish to go deeper into scripting, check out msdn.microsoft.com and search for Windows Scripting Host to get a rather lengthy manual containing all the functions possible. It won’t be long before you become so addicted that you will end up writing lengthy scripts for simple tasks instead of executing them normally-just for the sake of it!