none
Data drom VB.net Text box to and OPEN excel spread sheet cel RRS feed

  • Question

  • I have developed an application in VB.net that records data from various measurement tools with Rs232. The applications determined If the measurement is good or bad and stores the data in a CSV file. This part works well.

    Now, the desire is to run an Excel spread sheet (at the same time) that will display SPC data for the process being run, With the data going into a spread sheet, this is functioning but the operator has to follow a process that will update the spread sheet after each part is tested and this is cumbersome.

    Is there an example of writing from VB.net to and already open Excel spread sheet?  In the examples I have found, VB.net opens/created the spread sheet.

    For the application were doing, the spread sheet will be open independently.

    Thank you for any information you may have.

    -J

    Monday, September 10, 2018 2:03 PM

All replies

  • Hi,


    In order for us to understand your question well, can you tell us if you want to open Excel and write data directly based on a known path or find an already open Excel in an existing process and then write the data?

    We look forward to hearing from you.

    Best regards,

    Xiu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, September 11, 2018 9:31 AM
  • Good Morning,

    The Excel file is open.

    Is there an example of writing from VB.net to an already open Excel spread sheet?  In the examples I have found, VB.net opens/created the spread sheet.

    After the VB application writes to the Excel spread sheet, The spread sheet remains open and visible to display the graph.

    -J

    Tuesday, September 11, 2018 9:53 AM
  • Thank you for your reply. Since your issue is about VB development and our forum is to discuss the VS IDE, we will help you move this to the appropriate forum to seek for a more professional support, thank you for your understanding.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 12, 2018 3:00 AM
  • Hi,

    Do you want to write data to an already opened csv file?

    I think you can set the way to open the file to be exclusive, so that other programs will report an error when accessing the file.

    Using error trapping, create a temporary file in the catch and then write the data.
    Of course, you should save the file name with a global variable.

    With this mechanism, you need to be able to access each file.
    First, check if the temporary file exists. If it exists, write the temporary file data first, then write the data to be written this time, and then delete the temporary file.
    Of course, the file opening method for writing data should also be exclusive.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 12, 2018 7:49 AM
  • Thank you, I will re ask in the form you mentioned.

    -J

    Wednesday, September 12, 2018 6:39 PM
  • Hi,

    This case has been moved to the VB forum.

    Have you tried the solution I gave?

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 18, 2018 5:39 AM
  • The following is a template to get started, create an instance of this class say in your form at form level. Pass in the file name and sheet name into the new constructor which opens the file and sheet. Use the public properties to work with the Excel file. Use SaveAndClose method when it's time to save. 

    Excel can be problematic at times, know that up front that if you end up finding objects not releasing come back here and I can make suggestions.

    Imports System.Runtime.InteropServices
    Imports Microsoft.Office.Interop
    ''' <summary>
    ''' Open Excel, keep it open in sub new.
    ''' Public properties expose objects to write to cells for one sheet.
    ''' Method to save/close.
    ''' </summary>
    Public Class ExcelOperations
        Public Application As Excel.Application = Nothing
        Public WorkBooks As Excel.Workbooks = Nothing
        Public WorkBook As Excel.Workbook = Nothing
        Public WorkSheet As Excel.Worksheet = Nothing
        Public WorkSheets As Excel.Sheets = Nothing
        Public Cells As Excel.Range = Nothing
        Public FileName As String
    
        Public Sub New(pFileName As String, pSheetName As String)
            Application = New Excel.Application
            WorkBooks = Application.Workbooks
            WorkBook = WorkBooks.Open(pFileName)
            Application.Visible = True
            WorkSheets = WorkBook.Sheets
    
            FileName = pFileName
    
            For x As Integer = 1 To WorkSheets.Count
                WorkSheet = CType(WorkSheets(x), Excel.Worksheet)
    
                If WorkSheet.Name = pSheetName Then
                    Exit Sub
                End If
                Marshal.FinalReleaseComObject(WorkSheet)
                WorkSheet = Nothing
    
            Next
    
        End Sub
        Public Sub SaveAndClose()
    
            WorkSheet.SaveAs(FileName)
    
            WorkBook.Close()
            Application.Quit()
            ReleaseComObject(Cells)
            ReleaseComObject(WorkSheets)
            ReleaseComObject(WorkSheet)
            ReleaseComObject(WorkBook)
            ReleaseComObject(WorkBooks)
            ReleaseComObject(Application)
    
        End Sub
        Private Sub ReleaseComObject(excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 18, 2018 10:12 AM
    Moderator