-
Nov 21st, 2016, 03:32 AM
#1
Thread Starter
New Member
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
-
Nov 21st, 2016, 12:05 PM
#2
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"
-
Nov 21st, 2016, 12:27 PM
#3
Thread Starter
New Member
Re: Multiple users updating Excel from windows form
Originally Posted by Jenner
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.
-
Nov 21st, 2016, 01:19 PM
#4
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|