Using Excel with VB.net - Page 2


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27

Thread: Using Excel with VB.net

  1. #16
    Join Date
    May 2010
    Posts
    2
    I just joined this forum because of the explanation JBourgeois gave on arguments being passed to a function. That was the best explanation I have seen I am just starting out and just happen to be doing some of the same stuff infundibulum1 that you are doing. If you were to write a book JBourgeois I would buy it. Now for my question. Knowing that I am no coding genious and have only taken the minimum VB.NET class my school gives. I am looking for a way to take all the data in a excel sheet and generate a report from it with maybe a pie graph and just have it reorganized in a easy and fast method so he doesn't have to make one himself every time he gets this excel sheet from the higher ups. I have looked into a few things just lead me in the right path please. Either ADO.NET or OleDb which would you recommend using if either ADO.NET looked like it had much more capabilities to format data in a way I might want to use it. I don't want to sound to much like an *** kisser it just really helped the explanation you gave. Thanks!

    Curry

  2. #17
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    Hi curryjl.

    Thanks for the good words.

    Actually, I have written a few books. But they are in French and you have to get my training classes in order to get them. They are not very good as stand alone books to learn by the way, only as support and follow up for the training.

    For what you want to do, personnally I would not go the .NET route. Without having all the variables, it is always difficult to really tell somebody what would be best for a project, but given what you tell us, I would rather use VBA inside of Excel, even if VBA is a little "dépassé".

    You might also want to try the Office project route in .NET, but since VBA is the native language to program in Office, since it is easy to record a macro that writes the core of the code and can then be edited for the details, and since a VBA project is easier to install and distribute than a .NET Office project, that is where I would go myself.

    But do not count on me to help you on the matter (that would have to be discusses in another forum anyway). In order to be a good programmer in Excel, one needs to know Excel well as a user, and in your case, be at ease with charts and Excel reports. Although I have dabbled in VBA Excel, it was years ago, I do not like Excel, have never done a report in Excel, and would not be much help.

    Good luck
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  3. #18
    Join Date
    May 2010
    Posts
    2
    Well if at some time you decide that you want to translate them into English you will have found yourself a support seeing as I don't know french. And I should have figured that because my boss use to have an employee that did it all with VBA. Thanks for the input!

  4. #19
    Join Date
    Jul 2010
    Posts
    2

    Great Forum

    Are you all running with Option Strict On &
    Option Explicit On? The reason I ask is in my apps I have a need to pass a worksheet to different Subs and they have to be passed ByRef and the way the code on here is written will not work. Also just to get range and cell references for formatting the colums/rows/cells with Strict/Explicit on I have to use the following for the WorkSheet object>>

    Code:
    xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)  '"Sheet1" or the index number of the sheet you are referrng to
    As far as releasing excel from memory I use
    Code:
     Private Sub releaseObject(ByVal obj As Object)
    
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing  
            End Try
    
        End Sub
    Called like this

    Code:
      oQryTable = Nothing
                xlWorkBook.Close()
                xlApp.Quit()
    
                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
    I hope this helps.

  5. #20
    Join Date
    Jul 2010
    Posts
    2
    Does any one code EXCEL with Option Strict On? None of these examples have it set to ON. The following is an Excel spreadsheet created to break a received delivery of goods into their respected categories. I have found CTYPE is need to make the option strict quit nagging me but I didn't get many errors or bugs during development.


    Code:
    Option Strict On
    Option Explicit On
    
    Imports Microsoft.Office.Interop
    Imports System.IO
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    
    ''' <summary>
    ''' Creates the Excel file for the Book Keeper 
    ''' summarized by bar values
    ''' </summary>
    ''' <remarks></remarks>
    Public Class Excel_Truck_Order
    
       Public Sub New(ByVal toInvoice As Integer, _
                  ByVal toDate As Date, _
                  ByVal toInvoiceTotal As Decimal, _
                  ByVal toVendor As String, _
                  ByVal toTax As Decimal, _
                  ByVal toFuel As Decimal, _
                  ByVal hotbar As Decimal, _
                  ByVal coldBar As Decimal, _
                  ByVal bakery As Decimal, _
                  ByVal specialty As Decimal, _
                  ByVal paper As Decimal, _
                  ByVal condiments As Decimal, _
                  ByVal spice As Decimal, _
                  ByVal drinks As Decimal, _
                  ByVal oil As Decimal, _
                  ByVal smallWares As Decimal)
    
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWkbNew As Excel.Workbook = Nothing
            Dim xlWkshtMain As Excel.Worksheet = Nothing
            Dim xlRngData As Excel.Range = Nothing
            Dim xlWksht2 As Excel.Worksheet = Nothing
            Dim xlWksht3 As Excel.Worksheet = Nothing
    
    
    
            'Instantiate a new Excel session.
            xlApp = New Excel.Application
    
            'Add a new workbook.
            xlWkbNew = xlApp.Workbooks.Add
    
            'Reference the first worksheet in the workbook.
            xlWkshtMain = CType(xlWkbNew.Worksheets(Index:=1), Excel.Worksheet)        
           xlWkshtMain.Name = "Truck_Order" 'Name appears on the tab at the bottom of workbook
    
            'Delete the other 2 worksheets
            xlWksht2 = CType(xlWkbNew.Worksheets(Index:=2), Excel.Worksheet)
            xlWksht2.Delete()
    
            'must re-index  to 2 because #2 has been deleted and #3 is now #2 Go figure
            xlWksht3 = CType(xlWkbNew.Worksheets(Index:=2), Excel.Worksheet)
            xlWksht3.Delete()
            'End Delete Worksheets
    
    
            'Reference the range to which we will write some data to.
            xlRngData = CType(xlWkshtMain.Range("A1:C1"), Excel.Range)
    
            'Set user friendly settings
            With xlApp
                .Visible = False
                .DisplayAlerts = False
                .Iteration = False
                .UserControl = False
            End With
            xlWkbNew.Activate()
            xlWkshtMain.Activate()
    
    
            'Set the main titles
            Dim headRange As Excel.Range = Nothing
            headRange = xlWkshtMain.Range("A1", "B1")
            With headRange
    
                .VerticalAlignment = Excel.Constants.xlCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Interior.ColorIndex = 51
                '.Font.Underline = True
                .Font.Size = 14
                .Font.Bold = True
                .Font.ColorIndex = 40
                .ColumnWidth = 18
                .MergeCells = True
                .Value = "Sirloin Stockade Truck Order"
    
            End With
    
    
            'Column Headers
            xlWkshtMain.Cells(2, 1) = "Category"
            xlWkshtMain.Cells(2, 2) = "Amount"
            Dim colHeadRange As Excel.Range = xlWkshtMain.Range("A2", "B2")
    
            With colHeadRange
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Size = 12
            End With
    
    
            'Format the money range
            Dim moneyRange As Excel.Range = xlWkshtMain.Range("B3", "B13")
            moneyRange.NumberFormat = "$##,##0.00" ' Format as Currency
    
    
            'Data Rows
            xlWkshtMain.Cells(3, 1) = "Hot Bar"
            xlWkshtMain.Cells(3, 2) = hotbar + toFuel
    
            xlWkshtMain.Cells(4, 1) = "Cold Bar"
            xlWkshtMain.Cells(4, 2) = coldBar
    
            xlWkshtMain.Cells(5, 1) = "Bakery/Dessert"
            xlWkshtMain.Cells(5, 2) = bakery
    
            xlWkshtMain.Cells(6, 1) = "Specialty"
            xlWkshtMain.Cells(6, 2) = specialty
    
            xlWkshtMain.Cells(7, 1) = "Paper / Cleaning"
            xlWkshtMain.Cells(7, 2) = paper + toTax
    
            xlWkshtMain.Cells(8, 1) = "Condiments"
            xlWkshtMain.Cells(8, 2) = condiments
    
            xlWkshtMain.Cells(9, 1) = "Spice"
            xlWkshtMain.Cells(9, 2) = spice
    
            xlWkshtMain.Cells(10, 1) = "Drinks"
            xlWkshtMain.Cells(10, 2) = drinks
    
            xlWkshtMain.Cells(11, 1) = "Oil"
            xlWkshtMain.Cells(11, 2) = oil
    
            xlWkshtMain.Cells(12, 1) = "Small Wares"
            xlWkshtMain.Cells(12, 2) = smallWares
    
            xlWkshtMain.Cells(13, 1) = "Total:"
    
    
            'Formula for the Total Row
            ''Add formula for summing the Amount column
            Dim formCell As Excel.Range = xlWkshtMain.Range("B13")
    
    
            'activate the single cell you want the formula in
            formCell.Activate()
    
    
            'Write the formula for the selected cell
            formCell.Formula = "=Sum(B3:B12)"
    
    
            Dim totalRange As Excel.Range = xlWkshtMain.Range("A13", "B13")
            'totalrange formatting
            With totalRange
                .Font.Bold = True
                .Font.Size = 12
                .Font.ColorIndex = 3
                .BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, 1)
            End With
    
    
            Dim trkOrder As Excel.Range = xlWkshtMain.Range("A16", "B18")
            With trkOrder
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
    
            'Invoice Info
            xlWkshtMain.Cells(16, 1) = "Vendor:"
            xlWkshtMain.Cells(16, 2) = toVendor
    
            xlWkshtMain.Cells(17, 1) = "Invoice #"
            xlWkshtMain.Cells(17, 2) = toInvoice
    
            xlWkshtMain.Cells(18, 1) = "Date:"
            xlWkshtMain.Cells(18, 2) = CDate(toDate)
    
            Dim xlFileName As String = Friends.workingFolder & "\Truck\" & Year(CDate(toDate)) & "\Truck Order Summaries\" & Month(CDate(toDate)) & "\" & toInvoice & ".xlsx"
    
            xlWkbNew.SaveAs(xlFileName)
    
            Do While Not xlWkbNew.Saved
                xlApp.EnableEvents = True
            Loop
    
            'select some cell off the main sheet
            xlWkshtMain.Range("A22", "A22").Select()
    
    
            'Close and Release the Objects
            xlWkbNew.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWkbNew)
            releaseObject(xlWkshtMain)
    
    
            Call Friends.CloseExcel() 'Loacted in Module1
            GC.Collect()
    
    
        End Sub
    
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
    
    
    End Class
    
    
    'MAKE SURE ALL EXCEL INSTANCANCES ARE CLOSED
    'From the Friends Class
     Public Shared Sub CloseExcel()
            Try
    
                If Process.GetProcessesByName("EXCEL").GetLength(0) > 0 Then
                    For Each xlProcess As Process In Process.GetProcessesByName("EXCEL")
    
                        xlProcess.Kill()
                        Threading.Thread.Sleep(1100)
                    Next
    
                End If
    
            Catch ex As Exception
                'Do nothing
            End Try
    
        End Sub

  6. #21
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    Every professional programmer works with Option Strict On.

    It might appear as if is only an annoyance, but it enables the compiler to catch a lot of possible bugs at compile time, and even right there when you type your code.

    When you get an error because of Option Strict, this might be for code that works otherwise, but it is code that is is badly written and can crash on you in unexpected circumstances.

    You will be annoyed more often when working with Excel (or any other old program), because Excel is a COM application. COM is what came before .NET and Microsoft made a lot of bad decisions when they developped COM, in order to enable amateurs to use VBA to write macros and VB6 to develop simple applications.

    They left Option Strict as a feature in VB.NET so that those people could continue to work in a lazy way as they did in the older VB. Other .NET language such as C#, which are directly geared toward professional programmers, do not have that option. They are always strict.

    What do you prefer in the following 2 statements?

    1. Taking 10-30 seconds correcting a possible bug during compilation.

    2. Taking 2 or 3 days trying to figure out why some users report getting an exception from time to time?

    If your answer is 1, then leave Option Strict On.

    Note that you do not have to write it in all your files as was the case in the old VB. You can set it for the whole project in the Compile tab of the project properties window. You set it in the file only when you want a file to have a different option than the one set for the project.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  7. #22
    Join Date
    May 2012
    Location
    Bandung Indonesia
    Posts
    1

    Thumbs up Great help for code

    Quote Originally Posted by partyk1d24 View Post
    For all of you that have been having trouble getting VB.net to work right with the Excel interops it can be a bit of a bear so I will post the code I got made to get it to work right. It is a simple example but it may help som people

    Code:
    Imports Excel = Microsoft.Office.Interop.Excel...
    
        Private Sub getInfo()
            'declares the variables
            Dim excelApp As New Excel.Application
            Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)
            Dim partySheet As Excel.Worksheet = Nothing
            'try to open the workbook and a worksheet
            Try
                excelBook = excelApp.Workbooks.Open("C:\Program Files\DISClient\Templates\TemplateMerge.xls")
                partySheet = excelBook.Worksheets("MergeData")
            Catch ex As Exception
                MsgBox(ex)
            Finally
                'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this
                'The service (excel.exe) will continue to run
                NAR(partySheet)
                excelBook.Close(False)
                NAR(excelBook)
                excelApp.Workbooks.Close()
                NAR(excelApp.Workbooks)
                'quit and dispose app
                excelApp.Quit()
                NAR(excelApp)
                'VERY IMPORTANT
                GC.Collect()
            End Try
        End Sub
        Private Sub NAR(ByVal o As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
            Catch ex As Exception
            Finally
                o = Nothing
            End Try
        End Sub
    Thanks to the forums for helping me to get it to work
    Great help for me, my project is using Microsoft.Office.Interop.Excel and The service (excel.exe) is continue running after closing my project
    thanks for code

  8. #23
    Join Date
    Apr 2013
    Posts
    1
    but can we turn the excel into an image, can you find the code for me. thank you in advance, that would be fabulous.

  9. #24
    Join Date
    May 2013
    Posts
    1
    Quote Originally Posted by toecutter View Post
    excellent ..i was looking for some code like this a while back
    cheers m8
    check this one...its has a lot of easy lessons regarding excel vb.net

    Excel VB.Net Samples

  10. #25
    Join Date
    Jun 2013
    Location
    United States
    Posts
    4
    It is really good one code. And thanks for sharing this code it is very helpful for me.

  11. #26
    Join Date
    Sep 2013
    Posts
    1
    For anybody like me who couldn't get any of these methods to work:

    I've searched and searched for this and even Microsoft's own solution doesn't work. I have a vb.net application that exports data to an Excel template. Ideally when the user closes the Excel window it would kill the process but it doesn't because, as stated in the Microsoft article, vb.net is still referencing it.

    You need to kill the process yourself, there is a procedure to do this as below:

    Code:
    For Each p As Process In Process.GetProcesses
         If p.ProcessName = "EXCEL.EXE" Then p.Kill
    Next
    However, this would kill all instances of Excel and the user may have other Excel windows open that would get shutdown without saving, so I've come up with this (the workbook I'm using is called "Top 5 Issues Template"):

    Code:
    For Each p As Process In Process.GetProcesses
         If InStr(p.MainWindowTitle, "Top 5 Issues Template") <> 0 Then p.Kill
    Next
    This looks by the window name, not process name, and kills only the process that is related to it. This is the only way I could get Excel to close properly without messing anything up.

  12. #27
    Join Date
    Mar 2014
    Posts
    1
    here is a detailed tutorial on vb.net excel

    vb.net excel tutorial

    Steve

Similar Threads

  1. Use function Solver of Excel in VB.NET 2003
    By Pliroforikarios in forum .NET
    Replies: 1
    Last Post: 01-12-2006, 08:05 PM
  2. Replies: 0
    Last Post: 07-08-2002, 12:37 PM
  3. Excel and VB.NET
    By Dave Doknjas in forum .NET
    Replies: 0
    Last Post: 05-27-2002, 12:17 AM
  4. Excel & Vb.Net
    By Depechie in forum .NET
    Replies: 1
    Last Post: 03-22-2002, 10:51 AM
  5. How is a Excel file to read in VB.NET
    By Emilia in forum .NET
    Replies: 1
    Last Post: 10-15-2001, 11:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center