Using Excel Interop Objects

In the past I have worked with the new Open XML standards to create Word Documents. My most recent forray into the world of Microsoft Office was to manipulate an Excel spreadsheet or two to create new sheets, insert rows and columns, and even create a pivot table programmatically.



The first thing that I found was that when my program closed, Excel.exe was still running on my machine. I found this because the next time I tried running the program it popped up a message saying that the spreadhseet was locked for editing and that I could only open a read only copy. So I opened up Task Manager and found multiple instances of Excel.exe running.



So began the searching of the Internet tubes for information about why those processes remained. I found a number of articles that described cleaning up COM objects and that if you miss one of them then the Excel.exe process stays open.



After spending a good day listing all of the COM objects out and making sure I cleaned them all up, I had a program that did not leave behind any Excel processes.



How did I clean up those objects? Good question.



The first thing I did was to make a list of all of the comp objects that I created and here is the first trick:



Anywhere where you create COM objects in your code should not look like this:



Dim WorkBook = Application.WorkBooks.Open(path to file)



You need to create separate objects for the collections and the individual objects and then clean up all of those objects. The appropriate way to get a workbook object is as follows:



Dim oWorkBooks = app.WorkBooks

Dim WorkBook = oWorkBooks.Open(path to file)



Now you can clean up both of those COM objects.



Back to the actual cleanup of the objects:



I created a function to perform a couple of these steps since they are required of every COM object.



Release the COM object for that variable - System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook)



Call FinalRelease to make sure the object is freed up - System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkBook)



Set the variable equal to Nothing or Null - WorkBook = Nothing



This will clear the variables, but the objects could still be in memory until Garbage Collection happens. You can speed this process along by calling:



GC.Collect()

GC.WaitForPendingFinalizers()



The second one waits for any stragglers and then cleans then up.



This should get rid of any Excel.exe processes that were hanging around.



Note that if your program crashes and you have not handled that by cleaning up these objects, you will still have processes hanging out there.

Kalamazoo X Conference 2010

It's that time of year again. Time for the Kalamazoo X Conference. This year promises more soft skills and talks that are not offered at your typical tech conference. Registration is open and you can find out more information at www.kalamazoox.org

Changing Item colors on SharePoint calendar

Today we deployed a new calendar on our Intranet site that would display all employees birthdays and anniversaries. This would be automatically update once a month via a scheduled task to add any new employees or remove those that were terminated or retired.



We launched the new calendar and the first request we received was to color-code the anniversaries so that they stood out from the birthdays (at a company with almost 300 employees you can envision how many entries could be on the calendar).



I started doing some searching and came across this post on EndUserSharepoint and it was exactly what I was looking for. We implmented the process and had a couple of issues that Firefox was not rendering the divs but instead was writing out the tags, so we had to move to the Jquery to properly render it in both IE and Firefox.

Renaming the Title field in a list or site

One of the biggest mistakes you can make in a SharePoint installation is to dig down into the Site Columns and rename the Title column. At the time it might seem like the best thing in the world to do. The problem is that since the title field is hidden and included in every list in a SharePoint installation, you can have some unintended consequences. The next problem is that you will not be able to easily rename the column back to title. If you try to do this you will get an error message that says a column already exists with that name. That is because internally in SharePoint that column that you renamed is still called Title, no matter what you change the display name to.



The only way I have found to rename the Title column if you change it is to use SharePoint Manager. You can edit list columns in this program and rename the Title field back. I have not found any bad consequences by using this tool.

More Trouble with Incoming Email

Yesterday I happened to check an email enabled list and noticed that the most recent document added to the list was over 2 months ago. So I began tracking back and started with the person that sends out the emails that include the SharePoint list. She was still including the list address.

So then I sent an email to the list and logged onto the SharePoint server and watched the email arrive in the SMTP Drop box, and then disappear. Usually at this point, the email would have arrived in the list and the document should be there. It wasn't.

So then I begin hunting on the web and found a blog by Travis Lowdermilk. It explained the typical SharePoint setup but then towards the bottom in the comments I found someone that was having the same problem as me and when they changed the setting on the list to allow email from all users then their list worked.

So I tried it myself and the emails were getting dropped into the list. I am not sure what has changed in the last couple of months to change how that security trimming of the email-enabled lists works but for now I am ok with opening it up to all users since only one person in the company knows the address to send items to. I will be looking into this in the future and will find out if this is a bug from a recent update or what happened.

AJAX in SharePoint

I was surprised the other day at how easy it was to incorporate AJAX controls into my SharePoint installation. The hardest part is including all of the lines that are necessary in the web.config in order to get AJAX to work.

The worst Deployment Error Ever

The other day I got a call from someone out in the plant that they could not access our Timesheet program (written in .Net) from the Intranet. I walked out there to take a look since there were about 20 other computers that were all able to access it without any problems. I got out there and found the common deployment error dialog and clicked the more details button to get the full error and found this "Cannot handle redirect from HTTP/HTTPS protocols to other dissimilar ones." I had never seen this error before and so I searched for it on Google and found 3 links that dealt with the error but none of them were the same situation and none of them had a solution.



I ended up rebooting the machine and trying it again and everything worked fine, but I never did find any meaningful information about what the error really means or what could be causing the issue. The program works by clicking a link which goes to an ASPX page to validate that the user is inside our company and if they are it redirects to the .application file for the program. If they are not it redirects them to a page telling them they can't access the program from outside the company.



If anyone else runs across this issue, I would be interested to see if they find the cause of it or what they did to fix it.