Thread: embedding a excel attachment using vb.net

The contents of a datagrid (which is actually the contents of a datatable) can be output and written to an Excel file using Oledb with no need of automating Excel.

The easiest way to find such code is to start "record macro" in Excel and do the action manually, then look at the VBA code, the generated code will be much like the code you have to write in VB when automating the Office objects, at least you can in most cases figure out the "trick" about how it is done. In this case inserting an object from file into the Excel book will generate code like this:

Code:

ActiveSheet.OLEObjects.Add(filename and some arguments...)

Automating Excel from VB is pretty basic, add the reference to the Excel object library, import the namespace for easier coding, create Excel application object, create/open the book, manipulate it and close down. Here's a sample that does that, since I use Option Strict I do some type casts, but notice the similarities with the VBA code.

Code:

Imports Excel = Microsoft.Office.Interop.Excel

Code:

Dim app As New Excel.Application
Dim book As Excel.Workbook = app.Workbooks.Add
Dim sheet As Excel.Worksheet = CType(book.ActiveSheet, Excel.Worksheet)
Dim oleobj As Excel.OLEObjects = CType(sheet.OLEObjects, Excel.OLEObjects)
oleobj.Add(Link:=False, DisplayAsIcon:=False, Filename:="D:\path\Word.doc")
book.SaveAs("D:\path\Excel.xls")
book.Close()
app.Quit()

If you're a "late-binder" then you can reduce three of the code lines and the casting to this:

Code:

book.ActiveSheet.OLEObjects.Add(..same params)

That's really close to the starting point, right?

Despite the naming I don't think you can add Ole objects with a OleDB data export, usually it is only for tabular string/numeric data and none of the formatting or application specific features.