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