Automation of Excell from Visual Basic 2010

RGuillermo
RGuillermo used Ask the Experts™
on
Hi Experts,
I need to open a new excell file from my vb10 app and be able to control it..
Can you send me a simple example on how to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Something like this perhaps.
Dim xlApp As New Excel.Application
Dim xlWB As New Excel.Workbook

xlWB = xlApp.Workbooks.Add

Open in new window

This will create a new workbook, xlWB, in Excel and you can reference xlWB to manipulate the workbook in the rest of the code.

Note, this will create a workbook with the default no of worksheets, which is usuallly 3.
RGuillermoProject Manager

Author

Commented:
Imnorie, I get the message " Type 'Excel application' is not defined"
NorieAnalyst Assistant

Commented:
Try adding this at the top of the module.
Imports Excel = Microsoft.Office.Interop.Excel

Open in new window

RGuillermoProject Manager

Author

Commented:
Imnoria,
I added the Import line at the very top
then I get the following message

"Namespace or type specified in the imports "Microsoft.office.Interop.Excel" Does not contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases."
Meir RivkinFull stack Software Engineer

Commented:
here's sample code:

Dim stream As FileStream = File.Open(filePath, FileMode.Open, FileAccess.Read)

'1. Reading from a binary Excel file ('97-2003 format; *.xls)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)
'...
'2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
'...
'3. DataSet - The result of each spreadsheet will be created in the result.Tables
Dim result As DataSet = excelReader.AsDataSet()
'...
'4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = True
Dim result As DataSet = excelReader.AsDataSet()

'5. Data Reader methods
While excelReader.Read()
    'excelReader.GetInt32(0);
End While

'6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close()

Open in new window


from http://exceldatareader.codeplex.com/

also check here for example:
http://www.dotnetperls.com/excel-vbnet
Meir RivkinFull stack Software Engineer

Commented:
@rguillermo

this is warning and not an error, try to re-reference them using the COM tab in "add references" .

make sure to reference the right dll (office 2003\2007\2010)
RGuillermoProject Manager

Author

Commented:
Sedgwick:
I copieds the code, and I get "filestream not defined" message,
I think I may be missing a little detail?
Meir RivkinFull stack Software Engineer

Commented:
which line throws the error?
NorieAnalyst Assistant
Commented:
rguillermo

Add a reference to the Excel Interop to the project - I assumed you had that.

To add the reference:

Goto Project>Add Reference... and you should find Microsoft.Office.Excel Interop on the list in the .COM tab.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Download and install correct version of Office PIAs before you can reference them

http://www.microsoft.com/download/en/details.aspx?id=3508

http://www.microsoft.com/download/en/details.aspx?id=18346

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial