Help: Create Excel 2003 workbook from VB..net

[sipchen]

Hi, I'm trying to create an excel 2003 workbook from my vb.net project
I referenced the code from this website
http://www.dotnetspider.com/resources/20382-To-create-Excel-file-vb-net.aspx

However i'm getting error on this line "objSheets = objBook.Worksheets"
Can any please tell me what's wrong?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
            
            ''Create the Excel object declaration

            Dim objExcel As Excel.Application = Nothing
            ' create a excel workbooks object
            Dim objBooks As Excel.Workbooks = Nothing
            ' create a workbook object
            Dim objBook As Excel.Workbook = Nothing
            ' create a excel sheets object
            Dim objSheets As Excel.Worksheets = Nothing
            ' create a excel sheet object
            Dim objSheet As Excel.Worksheet = Nothing
            ' create a excel range object
            Dim objRange As Excel.Range = Nothing

            ' create a excel application object
            objExcel = New Excel.Application
            objExcel.Visible = True
            objExcel.DisplayAlerts = False

            objBook = CType(objExcel.Workbooks.Add(), Excel.Workbook)
            objBooks = objExcel.Workbooks

            objSheet = CType(objBooks(1).Worksheets.Item(1), Excel.Worksheet)
            objSheets = objBook.Worksheets

            ' Adding multiple worksheets to workbook
            objSheets.Add(Count:=2)

            'Adding First Sheet
            objBook = objBooks.Item(1)
            objSheet = CType(objSheets.Item(1), Excel.Worksheet)
            ' Assigning the worksheet name and summary log file values
            objSheet.Name = "Result Summary"



        
            objRange = objSheet.CellsWriteDataToExcel(objRange)

          
            objExcel.Cells(2, 1).Value = "Product Name"
            objExcel.Cells(3, 1).Value = "Comments"
            objExcel.Cells(4, 1).Value = "Date and Time"


        
    End Sub

Thanks!

[Animal Mother]

Remember the "Imports" at the start of your code.

Imports Excel = Microsoft.Office.Interop.Excel

The code you published does not seem like the easiest way or even the standard to do what you want to do.

Try the following.

Dim excelOutputFile As String = "C:\MyExcelDir\MyExcelFile.xls"
If Not Directory.Exists("C:\MyExcelDir\") Then
   Directory.CreateDirectory("C:\MyExcelDir\")
End If
oExcel = New Excel.Application
oBook = oExcel.Workbooks.Add
oSheet1 = oBook.Worksheets(1)
oSheet2 = oBook.Worksheets(2)
oSheet3 = oBook.Worksheets(3)

When all is set and done (added the values to your cells) end your code with the following.

oBook.SaveAs(excelOutputFile)
oBook.Close()

Hope this works for You.

[sipchen]

Hi, thanks for the prompt reply. does the declaration format remain the same?
eg.

Dim oExcel As Excel.Application = Nothing
            
             Dim oBook As Excel.Workbook = Nothing
            
            Dim oSheet As Excel.Worksheet = Nothing
            ' create a excel range object
            Dim objRange As Excel.Range = Nothing

Also, if I want to further manipulate the format of a range of cells can I do something such as

'oSheet.Range("A2", "Z2").Font.Bold = True
            oSheet.Range("A2", "Z2").Font.ColorIndex = 5
            oRange = objSheet.CellsWriteDataToExcel(objRange)
            oSheet.Range("A2", "Z2").EntireColumn.AutoFit()

[sipchen]

I'm getting errors from this line. could you please explain? Thanks!

If Not Directory.Exists("C:\MyExcelDir\") Then
   Directory.CreateDirectory("C:\MyExcelDir\")
End If

[Animal Mother]

Your welcome.

Actualy now that you mention it yes the declerations should have been changed from the start.
It should be:

Dim oExcel As Excel.Application
Dim oBook As Object
Dim oSheet1 As Object
Dim oSheet2 As Object
Dim oSheet3 As Object

And also regarding your cell formatting code:
I wrote a application for a company that creates spin reports into excel from a database so they did not actually care about cell formats and colors so I can't give you well advised info on that, but when I look at the code it does not seem that there will be a reason for it not to work.
Maybe someone else will see this thread and help out with cell formatting.

Enjoy

[Animal Mother]

"Imports" on top of your code add:

Imports System.IO

Now you should not get errors in the directory create function.

[sipchen]

Thanks a lot!

[Animal Mother]

Your welcome, Please remember to mark this thread solved as soon as it is.

[sipchen]

Just for future reference. I found some good examples on Microsft support
To automate Excel using VB.net
http://support.microsoft.com/kb/301982

Selecting Cell & Range
http://support.microsoft.com/kb/291308