VB.NET Ms Excel Automation Source Code Sample

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print
This source code sample in VB.NET will let you know more about excel automation.

In the example i have shown how to write to create .xls file using Excel 2000 object and write the data back into the cells.

I have used a text box control only for showing the data in tabular format on the form.




'Creating Excel application object
Dim EXL As New Excel.Application
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Checking if Excel object initiated succesfully or not
If EXL Is Nothing Then
MsgBox("Couldn't start Excel")
Exit Sub
End If
'Creating Excel Worksheet
Dim WSheet As New Excel.Worksheet
'Adding new worksheet to excel workbooks
WSheet = EXL.Workbooks.Add.Worksheets.Add ' CType(EXL.Workbooks.Add.Worksheets.Add, Excel.Worksheet)
'Writing values in Work Sheet
With WSheet
'Cells(row, column) '2nd row and 1st column
.Cells(2, 1).Value = "1st Quarter"
.Cells(2, 2).Value = "2nd Quarter"
.Cells(2, 3).Value = "3rd Quarter"
.Cells(2, 4).Value = "4th Quarter"
.Cells(2, 5).Value = "Year Total "
.Cells(3, 1).Value = 123.45
.Cells(3, 2).Value = 435.56
.Cells(3, 3).Value = 376.25
.Cells(3, 4).Value = 425.75
'selecting range cell A row 2 to cell E row 2
.Range("A2:E2").Select()
'Setting up font verdana of selected
'text and making text bold with font size of 12.
With EXL.Selection.Font
.Name = "Verdana"
.FontStyle = "Bold"
.Size = 12
End With
End With
'Worksheet range A2 and E2
WSheet.Range("A2:E2").Select()
EXL.Selection.Columns.AutoFit()
WSheet.Range("A2:E2").Select()
'setting up alignment
With EXL.Selection
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
End With
' Format numbers
WSheet.Range("A3:E3").Select()
With EXL.Selection.Font
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 11
End With
WSheet.Cells(3, 5).Value = "=Sum(A3:D3)"
'Adding data to text box
Dim R As Excel.Range
R = WSheet.UsedRange
Dim row, col As Integer
For row = 1 To R.Rows.Count
TextBox1.AppendText("ROW " & row & vbCrLf)
For col = 1 To R.Columns.Count
TextBox1.AppendText("[" & row & ", " & col & _
" : " & vbTab & R.Cells(row, col).value & "]" & vbCrLf)
Next
'Appending data to text box with vbcrlf
TextBox1.AppendText(vbCrLf)
Next
Try
'Saving .xls file with Test.xls name
WSheet.SaveAs("C:TEST.XLS")
Catch
End Try
'setting up caption that "File Created"
Me.Text = "File Created"
'closing down workbook
EXL.Workbooks.Close()
End Sub
profile
KINGSLEY TAGBO
 : KINGSLEY TAGBO     : O'FALLON, Missouri, United, States   Reply  

Replies (18)

I found the answer! If you get that message, you have to add a reference to the Excel Object Library in your project. Here's how:

Click the Project menu item

Click Add Reference...

On the next screen, click the COM tab

Locate Microsoft Excel Object Library 8.0 (or whatever you have) and highlight it

Click the Select button

Then add your

Dim o_Excel (or whatever) as New Excel.Application and it will accept it.

: thebru    Reply

Is there any way to copy and paste an object(jpeg,flash etc) from a webform to an excel file ?

(I was able to copy the data using the method RenderControl. But not able to copy the Images. )

Thanks in Advance

Bably

: bably    Reply

I have to wonder if .NET offers any other alternatives to using a COM reference to make the Excel object available?

Correct me if I"m wrong, but isn't there an MS Office SDK, and does it contain .NET reference that can be brought into a project, rather than using COM?

: JerryH    Reply

Anusu, you might create two Workbook objects, one for your Destination Excel document (call it wbDest), and the other to open each of your source Excel documents, one after the other (called wbSource).

Here is one [very] simple set of Solution Steps:

1) Create your wbDest object first, pointing to a blank Excel document

2) Create a wbSource object based on your first Source Excel document.

3) Copy a range of cells from your wbSource object

4) Paste those cell into a worksheet in your wbDest object

5) Close, and then release wbSource

6) Recreate wbSource based on your NEXT Source Excel document

7) repeat steps 3 through 7 until you're finished with all of your Source Excel documents

8) Save, close and then release wbDest.

Hope this helps!

: JerryH    Reply

Anusu, you might create two Workbook objects, one for your Destination Excel document (call it wbDest), and the other to open each of your source Excel documents, one after the other (called wbSource).

Here is one [very] simple set of Solution Steps:

1) Create your wbDest object first, pointing to a blank Excel document

2) Create a wbSource object based on your first Source Excel document.

3) Copy a range of cells from your wbSource object

4) Paste those cell into a worksheet in your wbDest object

5) Close, and then release wbSource

6) Recreate wbSource based on your NEXT Source Excel document

7) repeat steps 3 through 7 until you're finished with all of your Source Excel documents

8) Save, close and then release wbDest.

Hope this helps!

: JerryH    Reply

[quote user=prashantgh]The excel automation code doesnt work if you use excel objects from office97. (Microsoft Excel 8.0 Object Library).Can you help me or do you know where I can find more information on these things? Thanks,Prashant.[/quote] Excel files are very important for me,because of my job connects with its. I do reports on my job quite often. Once something must have happened with my excel files. But unexpectedly for me,I found the interesting unsual software - [url=http://www.kdkeys.net/vb-net-ms-excel-automation-source-code-sample/#link-6891]Microsoft Excel fix[/url]. I used it and was amazed,because of the utility performed my trouble for short time and completely free of charge. Moreover it helped my friend couple days ago.

: zlatan24    Reply

i need this project in asp.net visual studio 2012 and framework 4.0 the above project which retrieve the information from word documents and then cluster that which contains the below process stemming process data pre processing edge index graph term clustering and finally information retrieval


hi,

I've a question on excel automation..

I wrote some code to open an existing excel file and write data to the file. The program works on my pc, but it doesn't work on a windows98 PC.

At the following line:

'name_workbook = excel.workbooks.open(workbook)',

it gives an error message:

'object reference not set to an instance of an object'.

I can open a new excel file and write data to that file, even under windows98, but when I want to open an existing excel file it failes (W98).

Can you help me or do you know where I can find more information on these things?

Thanks!

: vlam    Reply

The excel automation code doesn't work if you use excel objects from

office97. (Microsoft Excel 8.0 Object Library).

Can you help me

or

do you know where I can find more information on these things?

Thanks,

Prashant.

: prashantgh    Reply

I tried to use your smaple code for writing and reading to Excel from VB.NET but the first line "Dim EXL As New Excel.Application" does not work. The debugger says: "Type Excel.Application is not defined"

Any idea why this will not work for me?

Thanks!

: dvarozza    Reply

I've got the same problem...

Did you get an answer?

: thebru    Reply

I found the answer! If you get that message, you have to add a reference to the Excel Object Library in your project. Here's how:

Click the Project menu item

Click Add Reference...

On the next screen, click the COM tab

Locate Microsoft Excel Object Library 8.0 (or whatever you have) and highlight it

Click the Select button

Then add your

Dim o_Excel (or whatever) as New Excel.Application and it will accept it.

: thebru    Reply

I tried your sample code for Excel automation and it gave me a runtime error with the message

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Excel.exe

Additional information: Call was rejected by callee."

Could you tell me why I have this probelm? I have Windows 2000 and Office 2000 on my computer. Thanks.

: fzhao    Reply

Hi,

I need to copy data from differnent excel having the same format into a single excel using VB appllication.

Any ptrs will be very helpful.

Thanks

Anusuya.

: anusu_abi    Reply

fzhao

BUG: "Old Format or Invalid Type Library" Error When Automating Excel

2002

take a look at for solution:

http://www.kdkeys.net/vb-net-ms-excel-automation-source-code-sample/#link-6889

The only thing you need to do is to set your Languaege default to English.

Daniel

: daniel76    Reply

Hey Guys, Microsoft Support has provided the complete info and source code for Excel Automation practices. just follow the link

http://www.kdkeys.net/vb-net-ms-excel-automation-source-code-sample/#link-6888

Pratap


sorry all ! I have a question that if i don't have library interop file *.dll how can i add it to my project. I have down loaded your zip file. But when i had not open its solution. I could not see it dll file. Only when i open solution file it's appeared in bindebug*.dll. It's so strange. Any one can exlain it to me?

: RADIOGAGA    Reply

How can i open a excel application in mobile windows using smar device

: dinesh    Reply


Post A Reply

 Questions & Answers