Microsoft Access Reports and MS Word

Recently I had a request from a client with a legacy Access database. This database produced some nicely formatted reports, complete with graphics, which they needed to export to MS Word for further editing.
I was amazed to find that this feature is not supported within Access. You would thin that two products such as these being part of the same suite, and such mature products at that, would support such a seemingly basic feature. It is common for report add-ins for Visual Studio to have Word as an export option. But, sadly, not Access.

The best I could do for the client was to export to RFT, which lost the grapics and most of the formatting. The full answer would be to rewrite the system to create the reports in Word via automation, but this is never an easy solution, and with 23 complex reports to convert, not a quick one either.

Visual Studio 2010 Missing Configuration Manager

One strange problem I had with VS 2010 was locating the configuration manager, in order to switch between debug and release modes.

To get this visible off the Debug menu, I had to go to Tools\Options
Choose Projects and Solutions, and then the General Tab
Check the "Show Advanced Build Configurations"

VB.Net Convert Automate Excel to convert csv file to xlsx

I needed to export 9000 rows from SQL to a xlsx file. I had no choice over the output format, because this was required by another system.

At first I tried automating a xlsx file directly, and outputing the data one cell at a time. This was very slow.
In the end, moved all the data into a CSV string, wrote this to the file system as a CSV file, then used Automation to open this file in Excel 2010, and save it as xlsx.

This was actually a lot harder than one might have expected, especially getting Excel to save in the right format, and to cleanup after itself.

Below is the code that finally worked. A simplified version of this would do the job, but I found all of this is required to cleanup Excel afterwards. Every part of Excel that is used must have its own variable assigned, so that .Net can do its garbage collection successfully, and to leave no orphaned processes running.

        Dim Excel As New Microsoft.Office.Interop.Excel.Application
        Dim WorkBooks1 As Microsoft.Office.Interop.Excel.Workbooks
        Dim WorkBook1 As Microsoft.Office.Interop.Excel.Workbook
        WorkBooks1 = Excel.Workbooks

        WorkBook1 = WorkBooks1.Open(TempPath)

' XlFileFormat.xlWorkbookDefault = 51 - this is correct, although a lot of forums recommend
        WorkBook1.SaveAs(Filename:=TempXLSXPath, FileFormat:=XlFileFormat.xlWorkbookDefault)

        'CLEANUP

        WorkBook1.Save() 'trying to find a way to get this to close without a prompt
        WorkBook1.Close(False)
        WorkBooks1.Close()
        Excel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook1)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBooks1)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
        WorkBook1 = Nothing
        WorkBooks1 = Nothing
        Excel = Nothing

Word Normal.dot template corruption

When automating MS Word, one of the problems that can be encountered is corruption of the normal.dot template. When this happens, the template can be deleted, because it will automatically be recreated with default settings. It would have been nice after all these years, and thousands of features that have been added into Word, if they had a built in feature to do this.

The manually process is not entirely straight-forward, because the normal.dot template can exist in many places.


Here are the basic steps to delete it.

1. Close all word documents. Run task manager, and check applications and processes to make sure there are no instances of winword.exe running. 
2. Close Outlook too.
3. Click on the Start option, click Search, then Files or Folders.
4. In the Name box, type the name of the global template for your version of Word. For Office 2007/2010 this will be normal.dotx or normal.dotm. For older versions of Office, this will be Normal.dot
5. In the Look In box, select your local hard drive
6. Click Search to locate the file.
7. For each occurrence of global template that appears in the Find dialog box, click the right mouse button on the file and then click Rename. Add a new file name, such as OldNormal.dot or Normal-1.dot.
8. Retest the application that is creating the Word document.

Completed Course on Lynda.com

Today I completed the Introduction to SharePoint 2010 course on Lynda.com.
I found the course to be very helpful, very well presented.
It suffered from none of the hesitant mumbling that in my experience often mark on-line software course, but was fluent, enthusiastic and informative.

I learnt a lot in a a few hours, and I can see that SharePoint is a powerful product with a lot of potential, and should make conventional web designers very nervous.

Am very interested in what a developer can add to SharePoint. It works with Visual Studio and SQL Server, so their are many development options with it. 

Introduction to Sharepoint 2010

Sharepoint can be hard to grasp as a concept, because it does not do one thing. Exchange handles emails, calendars, SQL Server is a database server, IIS is an internet server, but we cannot apply such a simple description to Sharepoint.

Sharepoint is not an application, but it is a platform, which is capable of hosting a variety of integrated solutions.

Sharepoint started life as Microsoft SharePoint Portal Server in 2001

These days several products may carry the Sharepoint label.

The server products are

/H

1. SharePoint 2010 Foundation
2. Sharepoint Server 2010 - Standard
3. Sharepoint Server 2010 - Enterprise

Each of these server products includes all the functionality of the version below it.

SharePoint Sites

SharePoint enables the creation of websites.
These are all based on a standard pattern, and all share certain features in common. ALL SharePoint sites are built on collections of lists and libraries.

So even a brandnew  blank websites have certain features as standard, such as "Like" buttons.
One big difference in Sharepoint 2010 from previsous versions, and from WebSites generally is the ribbon. Similar to the Ribbon in office, this allows you to edit SharePoint sites.

The Ribbon is access by clicking on Edit on a SharePoint page.

If you have permissions to edit a site, the Site Actions menu is very important. This will allow you to make modifications to the Sharepoint site.

The elements of Sharepoint are available individually available elsewhere, such as shared calendars and todist list, colaboration on documents - such as Google Documents.

It is the ability to combine them all together, without needing developer experience, which makes it very powerful. Add together SharePoint in the cloud, and it looks a very useful business tool.

Visual Studio 2010 Datasets with SQL Server 2000

It is possible to have a Visual Studio 2010 running aginst SQL Server 2000

I have a client running SQL Server 2000 and I have a UI developed in Visual Studio 2010. When trying to create reports for this I got the message "This server version is not supported.  You must have Microsoft SQL Server 2005 or later".

The client is not going to move to a new version of SQL Server just yet, so I had a problem.

The work around I came up with is to copy the database to SQL 2005 in my development environment, create the dataset and reports against this version at design time, then at run time, switch back to SQL2000. This worked fine.

Another problem I had to overcome was using temp tables when working with VS datasets.
The answer is to put this statement right at the beginning of the stored procedure:


 IF 1=0 BEGIN
     SET FMTONLY OFF
 END