Ask A Data Miner - 75,000+ Members
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
Replies (18)
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!
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!
[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.
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!
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.
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.
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?
Follow On Twitter
Request More Information
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.