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!

Recommended Answers

All 8 Replies

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.

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()

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

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

"Imports" on top of your code add:

Imports System.IO

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

Thanks a lot!

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.