[RESOLVED] Link to Any Excel version

[GrimmReaper]

Hello everyone.

I have Office 2007 on my system. The client has Office 2003 on his system.
Is it possible to "link" to both versions of Office from VB? It only gives me the capability to reference to my Office version, I'd like to be able to make it fully compatible with both versions of Office

[amrita]

I would prefer to use LATE BINDING...create the objects of Office components inside code.

Like CreateObject("Word.Application") etc.

[kareninstructor]

What are you attempting to do, read cells or ranges, write cells or ranges, formatting cells?

If you want to get away from Office compatibility issues there is Aspose Cells

If you want to read/write, no formatting you can do these operations using OleDb.

Partial code to use for connection to Excel (method extensions) to setup a connection to an Excel file.

Module ExcelConnectionHelpers
   Public Enum ImportExportMode
      Ignore = 0
      Honor = 1
   End Enum

    Private OleExelVersion As String = "12.0"

   <Runtime.CompilerServices.Extension()> _
   Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, ByVal Source As String)

      sender.ConnectionString = _
      <Connection>
         provider=Microsoft.Jet.OLEDB.4.0; 
         data source='<&#37;= Source %>';
         Extended Properties=Excel <%= OleExelVersion %>;
      </Connection>.Value

   End Sub
   <Runtime.CompilerServices.Extension()> _
   Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                 ByVal Source As String, _
                                 ByVal MixType As ImportExportMode)

      sender.ConnectionString = _
      <Connection>
         provider=Microsoft.ACE.OLEDB.12.0; 
         data source='<%= Source %>'; 
         Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;"
      </Connection>.Value

   End Sub
End Module

    Private Sub ReadSheet()
        Dim Excel2003File As String = "test.xls"
        Dim Excel2007File As String = "Book1_2007.xlsx"

        Dim MyConnection As New System.Data.OleDb.OleDbConnection()
        MyConnection.ConnectionStringEx(Excel2007File, ImportExportMode.Honor)

        MyConnection.Open()

        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", MyConnection)
        Dim dt As New DataTable
        dt.Load(cmd.ExecuteReader)
        DataGridView1.DataSource = dt
    End Sub

[MarMan]

Another option if you do not wish to use late binding would be to install Office 2003. Not sure if that would work with Office 2007 installed.

What I would do is to create a virtual machine with both Office 2003 and 2007 installed. Then you can early bind to whichever version is available and default to 2007 if you find them both.

[kareninstructor]

Another option if you do not wish to use late binding would be to install Office 2003. Not sure if that would work with Office 2007 installed.

What I would do is to create a virtual machine with both Office 2003 and 2007 installed. Then you can early bind to whichever version is available and default to 2007 if you find them both.

That is possible, I have Office 2003 installed with Office 2007 libraries. Still this means the poster will need to mess with references for each install for different versions of Office.

[GrimmReaper]

Thanks for all your efforts guys.

I'm actually using Excel primarily to Export information from my DataGrid into an excel Sheet. There may be an import functionality in the pipeline as well. But, I'd really love to be able to export to any version of Excel 2003 and upwards.

[jmcilhinney]

As suggested, you should use late-binding. The standard process is to start out with a reference to the appropriate Excel object library and build your project until the application works as it should. Then, you remove the reference and fix all the resulting errors by using late-binding.

You need to turn Option Strict Off, preferably at the file level and not for the project. You would move any code that requires late-binding into its own file(s) and turn Option Strict Off only for that/those file(s). Next, you replace any Office-specific types with Object. Finally, you replace any constructors with late-bound object creation as demonstrated in post #2.

[GrimmReaper]

OK, based on your suggestion I did this :

    Public Sub AppXportToXcel(ByVal dt As DataTable)
        Try
            Dim XApp As Object
            XApp = CreateObject("Excel.Application")
            Dim XBook As Object
            XBook = CreateObject("Excel.Workbook")
            XBook = XApp.Workbooks.Add
            Dim XSheet As Object
            XSheet = CreateObject("Excel.Worksheet")
            XSheet = XBook.Worksheets(1)

            'Dim XApp As New Excel.Application
            ' Dim XBook As Excel.Workbook = XApp.Workbooks.Add
            ' Dim XSheet As Excel.Worksheet = CType(XBook.Worksheets(1), Excel.Worksheet)

            XApp.Visible = False

            With XSheet

                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
                    XSheet.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
                        XSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

                    Next
                Next

                XSheet.Columns.AutoFit()
                Dim strFileName As String = Application.StartupPath & "\Abacus_ExpData"
                Dim blnFileOpen As Boolean = False
                Try
                    Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
                    fileTemp.Close()
                Catch ex As Exception
                    blnFileOpen = False
                End Try

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

                XBook.SaveAs(strFileName)


                XBook.Close()
                'to correctly close excel seems as if we have to use
                'System.Runtime.InteropServices.Marshal.ReleaseComObject()
                'on each object of the excel family we create (ranges included!)
                '...do the same as the following for range references if any, and all the excel stuff you've used...
                System.Runtime.InteropServices.Marshal.ReleaseComObject(XSheet) 'the worksheet
                System.Runtime.InteropServices.Marshal.ReleaseComObject(XBook) 'the workbook

                XApp.Quit()

                System.Runtime.InteropServices.Marshal.ReleaseComObject(XApp) 'the application
            End With
        Catch ex As Exception
            MessageBox.Show("Source [" & ex.Source & "] Description [" & ex.Message & "]")
        End Try

    End Sub

And called it like this :

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        AppXportToXcel(dgSummary.DataSource)
    End Sub

But I get an error stating that it cannot create ActiveX component. What am I doing wrong?

[GrimmReaper]

Seems like the problem lies with either of these objects :

 Dim XBook As Object
            XBook = CreateObject("Excel.Workbook")            XBook = XApp.Workbooks.Add
            Dim XSheet As Object
            XSheet = CreateObject("Excel.Worksheet")            XSheet = XBook.Worksheets(1)

[GrimmReaper]

OK, I was doing it wrong. This is the correct way :

            Dim XApp As Object
            XApp = CreateObject("Excel.Application")
            Dim XBook As Object
            XBook = XApp.Workbooks.Add
            Dim XSheet As Object
            XSheet = XBook.Worksheets(1)

Thank you everyone!!