locked
Updating an Excel file in VB.NET while it's open in Excel RRS feed

  • Question

  • I'm trying to add a functionality in an application developed using VB.NET where an Excel file has to be updated constantly. I followed the tutorials found on the internet and it works perfectly.

    Nevertheless, a problem occur when my VB application is trying to save that particular file on my harddrive while it is open in Excel. It tells me that the file could not be saved because it is used by another resource (in my case it's Excel).

    My question is, is it possible to show the updates on the opened Excel file in Excel while my application is updating it? In other words, constantly refreshing the Excel file in Excel?

    Can anyone give me some suggestions on how to tackle this problem?

    Thursday, November 19, 2015 3:25 PM

Answers

  • The only facility provided by Excel for multi-user access is the Share Workbook option under the Review tab in Excel. I don't believe that updates will be reflected dynamically though as Excel doesn't really support multiple concurrent usage in this way.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, November 21, 2015 1:02 PM

All replies

  • This is what Visual Studio Tools for Office (VSTO) would give you with an Excel add-in or template workbook.  How did you implement your Excel integration?  Are you automating an existing instance of Excel or opening a new instance while the user already has another instance running?  It sounds like the latter since the file appears to be opened twice.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, November 19, 2015 6:44 PM
  • Actually, this is is piece of code that I use.

    First of all, I'm using Microsoft.Office.Interop.Excel

    Private xlApp As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorkSheet As Excel.Worksheet
    Public Sub openExcel()
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open("planning.xlsx")
            xlWorkSheet = xlWorkBook.Worksheets("1")
    
    
    End Sub
    
    Public Sub closeExcel()
    
            xlApp.DisplayAlerts = False
            xlWorkBook.SaveAs("planning.xlsx")
    
            xlWorkBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
    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

    I'm opening an instance of the file in my application using the code above, while the user has already another instance running on his computer using the Microsoft Excel Application.




    • Edited by Moh Servida Saturday, November 21, 2015 10:26 AM
    Saturday, November 21, 2015 10:21 AM
  • The only facility provided by Excel for multi-user access is the Share Workbook option under the Review tab in Excel. I don't believe that updates will be reflected dynamically though as Excel doesn't really support multiple concurrent usage in this way.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, November 21, 2015 1:02 PM
  • The Workbook need to be shared for you to do this.

    https://bytes.com/topic/visual-basic-net/answers/380794-share-excel-spreadsheet

    http://blog.contextures.com/archives/2012/07/24/shared-workbook-limits-in-excel-2010/

    I wouldn't recommend doing this.  This will eventually cause Excel to crash and you'll lose some, but not all, of your data.   You should really use Access or, even better, SQL Server, for this kind of thing.

    Good luck!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, November 29, 2015 4:21 AM