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.

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.

Reading and Writing to Excel Files

Even though I posted similar 2 articles in 2008, the code of that article was having few errors. So thought to put more complete post on this.

The code will read an Excel file using an OleDbConnection and will write the same data back to another Excel file. This will explain how to read and write to Excel files. One thing to remember when running this code is to make sure the source Excel file is open. Otherwise you will get an error similar to “External table is not in the expected format.”

Also note that there are few connection string parameters you can use while opening Excel files.

HDR = Yes – Use when first row contains column headers.

HDR = No  - Use when first row contains data.

Excel xx.x – Use the following Table as a guide.

Parameter Value	Excel Version
Excel 12.0	Excel 2007 (Released in 2007)
Excel 11.0	Excel 2003 (Released in 2003)
Excel 10.0	Excel XP (Released in 2001)
Excel 9.0	Excel 2000 (Released in 1999)
Excel 8.0	Excel 97 (Released in 1997)

 

IMEX=1 – Use this when you want to treat all your data in the file as text.

For example - >

Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

To make the code to work properly you need to refer the Microsoft.Office.Interop.Excel assembly by adding a reference to your project.

1. using System;
2. using System.Data;
3. using System.Windows.Forms;
4. using System.Data.OleDb;
5. using Microsoft.Office.Interop.Excel;
7. namespace TestApp
8. {
9.     public partial class Form1 : Form
10.     {
11.         public Form1()
12.         {
13.             InitializeComponent();
14.         }
16.         private void button1_Click(object sender, EventArgs e)
17.         {
18.             openFileDialog1.ShowDialog();
19.             // Create an OLEDBConnection to connect to the Excel file.
20.             // I'm getting the required file by using a file dialog.
21.             // The @ symbol makes the string to contain any special characters inside the string without breaking the string.
22.             OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName.ToString() + @";Extended Properties=""Excel 8.0;HDR=Yes;""");
23.             // Open the connection.
24.             dbConnection.Open();
25.             // Create a command object to work on the data.
26.             // Note that I have given the sheet name as [Sheet1$] to retrieve data from that named sheet in the particular Excel file.
27.             OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
28.             // Creating a data reader to read data.
29.             OleDbDataReader dbReader = dbCommand.ExecuteReader();
30.             // If needed you can get the position of any column (e.g. Age), this will only work if you use HDR=Yes.
31.             int SearchingItem = dbReader.GetOrdinal("Age");
33.             // Create the Excel Application object.
34.             ApplicationClass ExcelApp = new ApplicationClass();
35.             // Set the visibility of the application.
36.             ExcelApp.Visible = true;
37.             // Create a new Excel Workbook.
38.             Workbook ExcelWorkbook = ExcelApp.Workbooks.Add(Type.Missing);
39.             // Create a new Excel Sheet.
40.             Worksheet ExcelSheet = (Worksheet)ExcelWorkbook.Sheets.Add(ExcelWorkbook.Sheets.get_Item(1), Type.Missing, 1, XlSheetType.xlWorksheet);
41.             // Will keep the current row index. This should start from 1 since the first row is 1.
42.             int CurrentRowIndex = 1;
43.             try
44.             {
45.                 // Read through the data.
46.                 while (dbReader.Read())
47.                 {
48.                     // Traverse through all the data columns.
49.                     for (int i = 0; i < dbReader.VisibleFieldCount; i++)
50.                     {
51.                         ExcelSheet.Cells[CurrentRowIndex, i + 1] = dbReader.GetValue(i);
52.                     }
53.                     CurrentRowIndex++;
54.                 }
55.                 // Save the Excel sheet.
56.                 // The @ symbol makes the string to contain any special characters inside the string without breaking the string.
57.                 ExcelApp.Save(@"C:\Projects\Ex.xls");
58.             }
59.             catch (Exception ex)
60.             {
61.                 MessageBox.Show(ex.ToString());
62.             }
63.         }
64.     }
65. }
66. 
    

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.

Newest Member of Microsoft Office - Groove

Check out the newest member of Microsoft Office product line. Microsoft Office Groove 2007. Groove is a team collaboration tool which will facilitate information sharing. This is what Microsoft is saying about Groove.

Microsoft® Office Groove® 2007 is Internet software for making direct connections with the people who are important to you. With Office Groove 2007, you can bring together team members from both inside and outside your company, with no IT assistance required and no need to waste time thinking about firewalls, servers, security, or network access. Additionally, you can enjoy the efficiency of always knowing each other's virtual location, or online presence, thus allowing for organic and quick conversation and collaboration.

If you are interested in knowing more visit http://office.microsoft.com/en-us/groove/HA101672641033.aspx. There you will be able to find an introduction animation from which you will be able to get an idea of Groove.