Printable View
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
I would prefer to use LATE BINDING...create the objects of Office components inside code.
Like CreateObject("Word.Application") etc.
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.
Code: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='<%= 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
Code: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
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.Quote:
Originally Posted by MarMan
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.
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.
OK, based on your suggestion I did this :
And called it like this :Code: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
But I get an error stating that it cannot create ActiveX component. What am I doing wrong?Code:Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
AppXportToXcel(dgSummary.DataSource)
End Sub
Seems like the problem lies with either of these objects :
Code:Dim XBook As Object
XBook = CreateObject("Excel.Workbook") XBook = XApp.Workbooks.Add
Dim XSheet As Object
XSheet = CreateObject("Excel.Worksheet") XSheet = XBook.Worksheets(1)
OK, I was doing it wrong. This is the correct way :
Thank you everyone!!Code:Dim XApp As Object
XApp = CreateObject("Excel.Application")
Dim XBook As Object
XBook = XApp.Workbooks.Add
Dim XSheet As Object
XSheet = XBook.Worksheets(1)