Automatically Sizing Excel Columns

Today while trying to do some formatting on Excel using .Net I came up with an error.

One of the things I tried is to make Excel columns automatically size according to the content having on them. As all of you might know we can get this done in Excel by simply double clicking on the column’s right margin. While doing this in code I got the following exception.

ExcelSheet.get_Range("A1", "E10")’ threw an exception of type ‘System.Runtime.InteropServices.COMException’
base {System.Runtime.InteropServices.ExternalException}: {"Exception from HRESULT: 0x800401A8"}

The code involved in generating this error is as below.

1. (ExcelSheet.get_Range("A1", "E10")).EntireColumn.AutoFit();

Later I found the reason for this error. Error will occur when we use AutoFit () on empty cells. Because initially I did not have anything in my excel sheet I kept on getting this. So to overcome this error use the same code to auto fit the cell contents simply after the cells are populated with values.

If you cannot get AutoFit () to work the reason might be the same thing. make sure the cells you apply auto fit have some values on them.

The best thing is to use AutoFit () after all data are entered into Excel sheet.

Advertisement

TechEd Australia – Videos

As you might know TechEd Australia ended few weeks back. I am sure lot of you might not have the chance to visit it. The good thing is you can watch the recorded sessions online now by visiting the following site.

http://channel9.msdn.com/events/teched/australia/tech-ed-australia-2011

I think this will be mostly useful to people who didn’t visit TechEd and also to people who visited it to refresh their minds.

Sending the Same Letter to Different People with their Details

Today a friend of mine needed some help from me on sending the same letter to different people while addressing the letters differently. This can be easily achieved by using Word Mail Merge feature.

Since I am having Word 2010 installed in my machine this post will list the steps required in Word 2010.

1. To start the process type in your letter and click on Mailings on the Word 2010 ribbon.

2. Click on Start Mail Merge and select Letters.

3. Then you need to select the people you need to send the letter to. To do that you have three options Type List, Use Existing List or Select from Outlook contacts. In this post I will use the first option.

4. When you click Type new List… you will get a window in which you can create a list of users with their details as shown in the following screenshot. For simplicity I will only create 2 users.

When you are done creating the list of users click ok so you will be prompted with a save window. Save it for later use since you may need the list for future use.

5. Then in your letter go to the appropriate places you need to place the differencing information, for example I need to put the address of the receiver after the text The Manager. To do so place the cursor on the required location and press on Address Block icon on the ribbon.

From the Insert Address Block window you can customize the way the address is printed on the letter. For example if you do not want to print the company name of the recipient then you can remove the checkmark on the Insert company name field. Preview pane will show you a preview of the actual data you have in your contact list. For the simplicity I will use the default options.

When you press ok you will see something similar to the following appearing in your letter, which will be the placeholder for the address block.

Following similar method but using the Insert Merge Field icon I did add the title and the last name of the recipient into the body section of the letter as you see below.

6. Preview your work by clicking Preview Results icon, by clicking the navigation buttons on the preview results pane you can navigate through your contacts to see the actual letters that will be created. For example below screenshot shows the letter for Mr. Withana with the changes for him.

7. When you are satisfied you can finish the merge with three options, in my case I will use the first option again.

As the final output of the merge I got one new document with the 2 letters addressed to the recipients i had in my contact list as shown in the following screenshot.

Installing Office 2007 on Windows

Hi all,

This is my second post on installing Office 2007. The reasons behind this post are that I have seen that installing Office 2007 is a problem not only in Vista but also in all other operating systems and also to mention you a tried and tested way (by me :-)) to install Office 2007 in one shot.

 

As my hint above you can use this method to install Office 2007 in any operating system.
Before starting the setup I recommend you ti update windows by running Windows Update.

 

1. Run a Disk Cleanup on the partition that you are going to install Office by getting the drive properties by right clicking on the drive and clicking Properties.

 

2. Create a folder named ‘Office’ in one of the drives in your hard disk and copy the full contents of Office setup CD/DVD in to this folder.

 

3. Start the installation by double clicking on the Setup.exe.

 

4. Select the customized option of the setup and right click on the top node and select ‘Install on first use’. This will make all the items install when you try to use them.5. Now continue with the installation.

6. If there are any problems the setup will bring up a window and tell you to browse for files that it is unable to find or sees as corrupt. If this happens to you,

i. Try pointing to the ‘Office’ directory that you created in the point number 2 and press Ok.

If the window again comes up then,

ii. Copy the entire folder which setup reports as having problems from the CD/DVD to the ‘Office’ directory again. (For example if the setup saying it is unable to process ExcelLR.cab, copy all the folders having the name Excel to the ‘Office’ directory again from the CD/DVD.)

iii. Point the setup again to the ‘Office’ directory. This time the setup will continue.

7. When the setup finishes, start one or two applications (Word, Excel, …) and make the installer install the necessary files. If at this point it asks for any missing files, follow the same procedure as at point number 6.

 

8. After you have successfully opened at least two applications of Microsoft Office family of products, start the Office installer again from the ‘Add or Remove Programs’ section in Windows Control Panel. Select ‘Add or Remove Features’ and click Continue. Then choose the applications you want to run and proceed with the setup. (If the setup is asking for any missing files follow the same procedure as at point number 6.)

 

9. When the setup finishes successfully, you can carry out a Repair on Office by starting Office setup again from ‘Add or Remove Programs’ to fix any missing files.

Installing Office 2007 Ultimate on Windows Vista Ultimate

As it always happens, last few days I was busy with a curious error while trying to install Office 2007 Ultimate edition on my newly installed Windows Vista.

The errors I got were.

I have seen many posts on the internet but many were regarding upgrading to the released version from beta. But my case was different because it was a fresh copy of Vista and it was a DVD received from Microsoft.

After some struggle I got Office installed and would like to share how I succeeded. What I suspect was that it was having a problem copying files from source to destination at a stretch. The way I did was like this.

The first advice I give is, If you are installing office to some other directory than default given directory, please create that exact directory in your hard disk and point the setup to that directory.

Then try normal installation of Office 2007. If you are in my category it will fail after giving one of the above errors. Let it fail by pressing Abort button because we have no other option.

Again start the installation, this time select the top node, right click and select "Install on first use" (This will make all the software’s install at the first attempt made to use them.) and continue with the setup.

In our earlier setup, it failed saying that one of the cabinet file was corrupt so cannot continue with the setup. This time because we used the option install on first use this error will not appear.

After successfully completing the setup. Try running Word for the first time. This will start the setup process. Sometimes you may be required to insert or point to the installation source during setup. If the same above mentioned error comes click Abort. Even you click Abort Word will start. Then after closing Word do the same on PowerPoint and let PowerPoint also start.

Now you have Word and PowerPoint up and running. Close all opened software’s and insert you Office installation source and start the installation again. This time select "Run from Computer" option (or what ever options you want to select). Then start the installation. When Office setup starts the progress bar remove your installation source (CD, DVD,…). Because the new installer will keep the required files for the setup, it will run normally for sometime. If any files for the selected options are missing then installer will ask at that place to point to the source.

After sometime the setup will request for the files which are missing (My case it asked for ExcelLR.cab). This time it will allow you to browse for the file. Be careful this will allow you only once to browse, if you select the wrong location then it will give setup fail message and you have to restart setup after pressing Abort. When browsing for the file remember to just point to the folder above the actual file not to the actual file it is requesting. (In my case I directed to the folder CD:\English\OfficeSystem2007\ULTIMATE2007\Excel.en-us.)

If you properly followed everything setup will now continue without errors.

If you are still having problems then I recommend you to find another edition of Office 2007 (Enterprise, Professional) and try installing Excel with that edition. After successfully completing that setup run the Ultimate setup again. Then when the progress bar starts remove the installation source. You may have to browse for the installation source after sometimes. After you installed ultimate edition successfully remove the other edition using add remove programs. And perform a repair operation on the ultimate setup. This will fix any edition clashes and will configure ultimate edition properly.