[LibertyUser]

I have been trying to export a DataTable to an excel file and have been hitting a few walls. I am using Visual Studio 2010, VB.Net, Windows 7 64-bit, Office 2010 32-bit.

1) I first had trouble with not closing the Excel.exe *32 process and solved that by using the FinalReleaseComObject(ExcelFile).

2) When I use the FinalReleaseComObject, it re-checks the "Ignore other applications that use Dynamic Data Exchange" under Excel's Options-->Advanced. Then any time I try to double-click on an Excel file, I get the error: "There was a problem sending the command to the program." Excel opens, but the worksheet I want does not. The only solution I have found to this problem is to have the DDE unchecked.

Things I've noticed:
1)If I remove the FinalReleaseComObject, the DDE problem goes away, but doesn't release the process.
2)If I use FinalReleaseComObject and already have Excel open when I export the file, the DDE remains unchecked.

Below is my code, I am just sending this sub a DataTable and a string for the FileName. Any suggestions?

Private Sub PrintTable(ByVal data As DataTable, ByVal FileName As String)

      Try
         Dim priorSum As Integer = 0
         Dim newSum As Integer = 0
         Dim xlProcID As Integer = 0
         For Each proc As Process In Process.GetProcessesByName("excel")
            priorSum += proc.Id
         Next proc

         Dim ExcelFile As New Excel.Application
         Dim wBook As Excel.Workbook = ExcelFile.Workbooks.Add()
         Dim wSheet As Excel.Worksheet = CType(wBook.Worksheets(1), Excel.Worksheet)

         ExcelFile.Visible = False
         ExcelFile.IgnoreRemoteRequests = True
         ExcelFile.ErrorCheckingOptions.EvaluateToError = False
         ExcelFile.DisplayAlerts = False

         For Each proc As Process In Process.GetProcessesByName("excel")
            newSum += proc.Id
         Next proc
         xlProcID = newSum - priorSum

         Dim dt As System.Data.DataTable = data
         Dim dc As System.Data.DataColumn
         Dim dr As System.Data.DataRow
         Dim colIndex As Integer = 0
         Dim rowIndex As Integer = 0

         For Each dc In dt.Columns
            colIndex = colIndex + 1
            ExcelFile.Cells(1, colIndex) = dc.ColumnName
         Next

         For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
               colIndex = colIndex + 1
               ExcelFile.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
         Next

         wSheet.Columns.AutoFit()

         Dim strFileName As String = "C:\Temp\" & FileName & "_" & DateTime.Now.ToString("yyyy-MM-dd") & ".xlsx"
         Dim blnFileOpen As Boolean = False
         Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
         Catch ex As Exception
            MessageBox.Show(ex.Message)
            blnFileOpen = False
         End Try

         If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
         End If

         wBook.SaveAs(strFileName)
       
         System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wSheet)
         wBook.Close(SaveChanges:=False)
         System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wBook)
         ExcelFile.Quit()
         System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelFile)

       
         ShowMSG("Your file has been saved in your C:\Temp folder.", "Export successful", 1, "")

      Catch ex As Exception
         MessageBox.Show(ex.Message)
      End Try

   End Sub

[sela007]

I was doing same thing few days ago ,in a little different way but it works. This sub exports data table in xlsx format. This is Excel Microsoft Office Open XML format spreadsheet. If you don't find your bug you can use this:

Public Sub ExportToExcel(ByVal tbl As DataTable, ByVal fPath As String)

        ' Choose the path, name, and extension for the Excel file
        Dim myFile As String = fPath

        ' Open the file and write the headers
        Dim fs As New IO.StreamWriter(myFile, False)
        fs.WriteLine("<?xml version=""1.0""?>")
        fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
        fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")

        ' Create the styles for the worksheet
        fs.WriteLine("  <ss:Styles>")
        ' Style for the column headers
        fs.WriteLine("    <ss:Style ss:ID=""1"">")
        fs.WriteLine("      <ss:Font ss:Bold=""1""/>")
        fs.WriteLine("      <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
            "ss:WrapText=""1""/>")
        fs.WriteLine("      <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
        fs.WriteLine("    </ss:Style>")
        ' Style for the column information
        fs.WriteLine("    <ss:Style ss:ID=""2"">")
        fs.WriteLine("      <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
        fs.WriteLine("    </ss:Style>")
        fs.WriteLine("  </ss:Styles>")

        ' Write the worksheet contents
        fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
        fs.WriteLine("  <ss:Table>")
        For i As Integer = 0 To tbl.Columns.Count - 1
            fs.WriteLine(String.Format("    <ss:Column ss:Width=""{0}""/>", _
             100))
        Next
        fs.WriteLine("    <ss:Row>")
        For i As Integer = 0 To tbl.Columns.Count - 1
            fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""1"">" & _
                "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
                tbl.Columns(i).ColumnName.ToString))
        Next
        fs.WriteLine("    </ss:Row>")

        ' Check for an empty row at the end due to Adding allowed on the DataGridView
        Dim subtractBy As Integer, cellText As String
        ' If tbl.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
        subtractBy = 1
        ' Write contents for each cell
        For i As Integer = 0 To tbl.Rows.Count - subtractBy
            fs.WriteLine(String.Format("    <ss:Row ss:Height=""{0}"">", _
                22))

            For intCol As Integer = 0 To tbl.Columns.Count - 1
                cellText = tbl.Rows(i).Item(intCol).ToString
                ' Check for null cell and change it to empty to avoid error
                If cellText = vbNullString Then cellText = ""
                fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""2"">" & _
                    "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
                    cellText.ToString))
            Next
            fs.WriteLine("    </ss:Row>")
        Next

        ' Close up the document
        fs.WriteLine("  </ss:Table>")
        fs.WriteLine("</ss:Worksheet>")
        fs.WriteLine("</ss:Workbook>")
        fs.Close()
        fs.dispose()
    End Sub

This post has been edited by sela007: 10 February 2012 - 01:55 PM