Results 1 to 4 of 4

Thread: Multiple users updating Excel from windows form

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2016
    Posts
    5

    Multiple users updating Excel from windows form

    I have created a form in Visual Studio 2013 which is used to enter data in Excel by multiple users at same time. It works perfectly fine except if two users open form at the same time or if one has clicked submit and the form is still processing. Then it shows the following error for the other person:
    Unhandled exception has occurred in your application. Exception from HRESULT: 0x800A03EC
    How do I disable the command button so that when one user clicks on submit, for other users the form shows the button disabled till the data is being processed?

    Code:
    Imports excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    
    Public Class Form1
        Dim xlapp As New excel.Application
        Dim workbook As excel.Workbook
        Dim worksheet As excel.Worksheet
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            workbook = xlapp.Workbooks.Open("2.xlsx")
            xlapp.Visible = False
            worksheet = workbook.Sheets("sheet1")
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If workbook.MultiUserEditing Then
                workbook.AcceptAllChanges()
                workbook.Save()
            End If
    
            Dim row As Long
            Dim alpha As Long = 0
            row = 5
            With worksheet
                Do While .Cells(row, 4).value IsNot Nothing
                    row = row + 1
                Loop
    
                .Cells(row, 2).value = Me.fname.Text
                .Cells(row, 3).value = Me.lname.Text
                Me.fullname.Text = Me.fname.Text + Me.lname.Text
                .Cells(row, 4).value = Me.fullname.Text
            End With
            xlapp.DisplayAlerts = False
            workbook.SaveAs("2.xlsx", AccessMode:=excel.XlSaveAsAccessMode.xlShared)
        End Sub
    End Class

  2. #2
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Multiple users updating Excel from windows form

    Doing what you are describing is difficult, because you would need a "server" type program to "tell" all your users using your program when the form is being worked on, and when it's clear.

    What would be much easier to do is to identify and handle the error you're getting on the small chance a user DOES click the "Go" button while the file is open in someone else's program.
    As a side note, this is EXACTLY the reason databases were invented.

    If you use a Try...Catch...End Try block to handle the error, when a user clicks the button and the program hits that error, instead of crash, you can instead "handle" it by making a popup with something like "The file is currently being updated by another user, please wait a little while and try again"
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2016
    Posts
    5

    Re: Multiple users updating Excel from windows form

    Quote Originally Posted by Jenner View Post
    Doing what you are describing is difficult, because you would need a "server" type program to "tell" all your users using your program when the form is being worked on, and when it's clear.

    What would be much easier to do is to identify and handle the error you're getting on the small chance a user DOES click the "Go" button while the file is open in someone else's program.
    As a side note, this is EXACTLY the reason databases were invented.

    If you use a Try...Catch...End Try block to handle the error, when a user clicks the button and the program hits that error, instead of crash, you can instead "handle" it by making a popup with something like "The file is currently being updated by another user, please wait a little while and try again"
    I can't use any database as my users only want excel and this visual studio form. Can you pleas help me with the code of try catch? I am unable to understand.

  4. #4
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Multiple users updating Excel from windows form

    Here: https://lmgtfy.com/?q=try+catch+vb.net

    After you've taken twelve seconds to try to understand it yourself (because you didn't even try apparently), come back if you have any problems with your modified code.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width