none
Automating Excel in VS2013 using Visual Basic RRS feed

  • Question

  • I have a Visual studio 2010 application that opens, edits, saves, and closes excel documents frequently while running. These excel documents are on a network drive and may be accessed by the same program on more than one computer at the same time. you would never actually see the excel documents open up, it just happened in the background without any issue. Once I upgraded to Microsoft Excel 2013 I could actually see the excel documents open up on the screen and it slowed the operation down and is very annoying. Also, the excel documents wouldn't always close properly. I figured this was due to lack of compatibility with VS2010 and Excel 2013. So I now have VS2013 Professional with the updated tools package needed to automate Office 2013 programs. The same code I used to use for opening and closing excel does not work now though. This is an example:

        Public Sub writeTest()
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application
            Dim xlBook = xlApp.Workbooks.Open(TEST_SHEET)
            Dim xlSheet = xlBook.Worksheets("Sheet1")

            xlSheet.Cells(1, 1) = Write.Text

            closeExcel(xlApp, xlBook, xlSheet, True)
            xlApp.Quit()
            xlApp = Nothing
            xlBook = Nothing
            xlSheet = Nothing
        End Sub

    The closeExcel line causes a runtime error. Does anyone have a suggestion for a better way to do this??

    • Moved by Cindy Meister MVP Wednesday, March 18, 2015 7:07 PM not using VSTO technology. Split from http://social.msdn.microsoft.com/Forums/vstudio/en-US/2df0e430-4d93-416e-89a0-56f8ad5dc988/seting-position-of-a-floating-custome-task-pane
    Thursday, March 12, 2015 7:59 PM

Answers

  • Hi Cjsykes,

    Based on the code in orignal post, you were making changes in the workbook and saved the changes.

    Since the workook was saved on a net work and could be opened by others at the sametime, I suspect the workbook may be opened by others before you opened it. Would you mind shareing the exect error message you got?

    Also you can copy this workbook and test the code with the workbook copied to see whether the issue is relative to the workbook opened by others.

    >>. you would never actually see the excel documents open up, it just happened in the background without any issue. Once I upgraded to Microsoft Excel 2013 I could actually see the excel documents open up on the screen and it slowed the operation down and is very annoying<<

    I am trying to reproduce the issue however failed, here are the test code:

     Dim ExcelApp As New Application
            'ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Open("C:\Users\UserName\Desktop\Book1.xlsx")
            aWorkBook.Worksheets("Sheet1").Range("A3") = "test"
            aWorkBook.Save()
            ExcelApp.Quit()

    To see whether the issue relative to the format to Office, I suggest that you test the code with a new workbook created by Office 2013.

    Also I would suggest that you test the code above to see whether the issue was relative to the code. If yes, would you mind sharing with us a code demo to help us narrow down this issue? You can upload it via OneDrive.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 16, 2015 2:02 AM
    Moderator
  • Hi Chris Sykes,

    >>Is there a way to check if the excel document is already open with code before trying to open it again?<<

    Based on my understanding, we can loop all the Excel application to see whether the specific workbook exited in the workbooks collection of application object. However it seem this way works only for the application is visible, since we need to create the application instance from a window by using AccessibleObjectFromWindow.

    And a simple way is to check whether the file that named like ~$Book1.xlsx exists since when Excel open a workbook, it will create a template file on the disk.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 17, 2015 9:28 AM
    Moderator
  • Hi Chris Sykes,

    >>This is new to me, but I am wondering if there is just a command of some sort to check if a certain file is open.<<
    As far as I know, Excel object doesn't provide feature to achieve the goal.

    >>Do you have an example of code that would check if an instance of an excel sheet is open.<<

    Yes, it is very simple like check whether the file exits on the specific path. For example, we want to check that the whether C:/Users/UserName/desktop/Book1.xlsx is opened, we can refer to code below:

       If My.Computer.FileSystem.FileExists("C:\Users\UserName\Desktop\~$Book1.xlsx") Then
                MsgBox("Book1.xlsx is opened.")
            Else
                MsgBox("Book1.xlsx is not opened.")
            End If

    Note: this is a workaround and the file ~$Book1.xlsx only exits when the workbook opened not in read-only model.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by cjsykes Friday, March 20, 2015 12:53 PM
    Wednesday, March 18, 2015 12:29 PM
    Moderator

All replies

  • Could you please be more specific? What line of code exactly throws an exception?

    Where is the definition of the closeExcel method?

    Thursday, March 12, 2015 8:12 PM
  •    

    the closeExcel(xlApp, xlBook, xlSheet, True) throws a runtime exception.

    Private Sub closeExcel(ByRef app As Application, ByRef book As Microsoft.Office.Interop.Excel.Workbook,
                           ByRef sheet As Microsoft.Office.Interop.Excel.Worksheet, ByVal save As Boolean)
            book.Close(SaveChanges:=save)
            sheet = Nothing
            book = Nothing
        End Sub

    These are my imported namespaces

    Imports System.Runtime.InteropServices
    Imports System.Drawing
    Imports System.Drawing.Printing

    Thursday, March 12, 2015 8:18 PM
  • Hi Cjsykes,

    Based on the code in orignal post, you were making changes in the workbook and saved the changes.

    Since the workook was saved on a net work and could be opened by others at the sametime, I suspect the workbook may be opened by others before you opened it. Would you mind shareing the exect error message you got?

    Also you can copy this workbook and test the code with the workbook copied to see whether the issue is relative to the workbook opened by others.

    >>. you would never actually see the excel documents open up, it just happened in the background without any issue. Once I upgraded to Microsoft Excel 2013 I could actually see the excel documents open up on the screen and it slowed the operation down and is very annoying<<

    I am trying to reproduce the issue however failed, here are the test code:

     Dim ExcelApp As New Application
            'ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Open("C:\Users\UserName\Desktop\Book1.xlsx")
            aWorkBook.Worksheets("Sheet1").Range("A3") = "test"
            aWorkBook.Save()
            ExcelApp.Quit()

    To see whether the issue relative to the format to Office, I suggest that you test the code with a new workbook created by Office 2013.

    Also I would suggest that you test the code above to see whether the issue was relative to the code. If yes, would you mind sharing with us a code demo to help us narrow down this issue? You can upload it via OneDrive.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 16, 2015 2:02 AM
    Moderator
  • Fei Xue,

    Your code worked but there are a few things I need to work out. If the excel file is NOT open then the code you supplied opens and saves properly but does not close the instance of excel until the program is closed out. I had task manager open while running the code and found this to be the result. So I would like a way to close the instance of excel immediately after opening and reading/writing to it. If the excel file IS open and then I run the code it will run through it without errors, but will not actually update the file. 

    Is there a way to check if the excel document is already open with code before trying to open it again?

    Thanks

    Chris Sykes

    Monday, March 16, 2015 4:07 PM
  • Hi Chris Sykes,

    >>Is there a way to check if the excel document is already open with code before trying to open it again?<<

    Based on my understanding, we can loop all the Excel application to see whether the specific workbook exited in the workbooks collection of application object. However it seem this way works only for the application is visible, since we need to create the application instance from a window by using AccessibleObjectFromWindow.

    And a simple way is to check whether the file that named like ~$Book1.xlsx exists since when Excel open a workbook, it will create a template file on the disk.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 17, 2015 9:28 AM
    Moderator
  • Hi Fei,

    Thanks for your help so far. I am not sure I follow your last suggestion on how to check if an excel file is currently open. This is new to me, but I am wondering if there is just a command of some sort to check if a certain file is open. Do you have an example of code that would check if an instance of an excel sheet is open. 

    Thanks

    Chris Sykes

    Tuesday, March 17, 2015 7:26 PM
  • Hi Chris Sykes,

    >>This is new to me, but I am wondering if there is just a command of some sort to check if a certain file is open.<<
    As far as I know, Excel object doesn't provide feature to achieve the goal.

    >>Do you have an example of code that would check if an instance of an excel sheet is open.<<

    Yes, it is very simple like check whether the file exits on the specific path. For example, we want to check that the whether C:/Users/UserName/desktop/Book1.xlsx is opened, we can refer to code below:

       If My.Computer.FileSystem.FileExists("C:\Users\UserName\Desktop\~$Book1.xlsx") Then
                MsgBox("Book1.xlsx is opened.")
            Else
                MsgBox("Book1.xlsx is not opened.")
            End If

    Note: this is a workaround and the file ~$Book1.xlsx only exits when the workbook opened not in read-only model.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by cjsykes Friday, March 20, 2015 12:53 PM
    Wednesday, March 18, 2015 12:29 PM
    Moderator
  • Thank you Fei, this solved my issue. 
    Friday, March 20, 2015 12:53 PM