none
GetObject does not work with Excel 2016 RRS feed

  • Question

    • You cannot vote on your own post
      0

      The following code works fine on my Windows 10 64 computer, running Visual Basic 2013 and Excel 2010

              Dim oExcel As Excel.Workbook = Nothing

              oExcel = GetObject(path + filename)

              oExcel.Application.Visible = True

              oExcel.Windows(1).Visible = True

      However, when I run the same code after having installed Office 2016, when the filename workbook is not opened, it opens two Excel windows, which are blank  (no worksheets are displayed) and unresponsive. They have to be closed ending the task in Task Manager. 

      The code throws an error message saying index out of range, which is due to Windows.Count being 0.

      I have added to my project the Microsoft Office 16 and Microsoft Excel 16 references, removing the Office 14 and Excel 16 previous references. 

      I reported this fault over a year ago and have avoided Office 2016 since in the hope that it would get fixed, as Microsoft told me they would. However, after installing Office 2016 again I see that the problem persits.

    • Any solution?

      Many thanks.

    Thursday, August 3, 2017 10:38 AM

All replies

  • Hi AntSalc,

    I could reproduce your issue. It seems you want to open a workbook file. I suggest you get/create an excel application instance, and then use the application instance to open a workbook file. Here is the example.

    Dim app As Object

    On Error Resume Next

    Set app = GetObject("excel.application")

    If app Is Nothing Then

    Set app = CreateObject("excel.application")

    End If

    app.Visible = True

    Dim oExcel As Object

    FileName = "C:\Users\Desktop\ForTest.xlsx"

    Set oExcel= app.workbooks.Open(FileName)

    Best Regards,

    Terry

    Friday, August 4, 2017 6:50 AM
  • Hello Terry,

    Yes, there are workarounds, yours is one, I have also found other ways.

    The point is that GetObject does not work.

    It would be good to make it work. It would simplify the coding and would allow us to use our previous code.

    I find it surprising that after all this time the problem has not yet been rectified.

    Thank you.

    Antonio


    • Edited by AntSalc Sunday, August 6, 2017 2:06 PM
    Friday, August 4, 2017 11:36 AM
  • hello,

    after the install did you rebuild your application with the latest version of excel?

    Friday, August 4, 2017 1:42 PM
  • Yes, with the latest Excel reference: Microsoft Excel 16.0 Object Library
    Sunday, August 6, 2017 2:04 PM
  • Hi AntSalc,

    Due to the limitation of community support channel, I suggest you pay attention to the progress of your bug report or you could re-report the bug again to prompt for fixing. Thanks for your understanding.

    Best Regards,

    Terry

    Monday, August 7, 2017 7:48 AM
  • Terry,

    What's the best channel to report the bug?

    I did through Microsoft Connect and it did not help much.

    I am keen to report again.

    Thanks.

    Monday, August 7, 2017 8:09 AM
  • Hi AntSalc,

    You could use Excel to feedback your report. Open Excel and select File->Feedback and then select a option  for feedback.

    Best Regards,

    Terry


    Wednesday, August 9, 2017 11:01 AM
  • Hi

    The point is that GetObject does not work......!!!

    ...YES  , YOU ARE RIGHT , WRONG.... EXCEL 2016


    dBase,FoxPro,MS Access 2003,(2010=Not rec.),Office 2010+ACC.2013 ,Symbian C++, AC.2013.SystemResource.GetCurrentFreeSize=?

    Thursday, January 9, 2020 1:54 PM
  • In Excel 2019 it does not work either

    It is unbelievable that this basic bug has not been corrected for so many years

    Sunday, April 12, 2020 3:28 PM
  • Even if GetObject(file-name) did work I would use the Workbooks.Open approach as suggested by Terry Xu in any version. It's not a workaround, as you described, but the way to go.
    Monday, April 13, 2020 3:55 PM
    Moderator
  • I think I know now what has happened.

    Microsoft has decided to change the way GetObject works.

    Now, by design, if there is no instance of the application running an error is thrown.

    This is explained in https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/getobject-function

    Before, https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2008/e9waz863(v=vs.90)?redirectedfrom=MSDN explained that if no instance of the application was running a new one would be created.

    Now, if Excel is already running, then

            Dim oExcel As Excel.Workbook = GetObject(PAdir + filename)

    works just fine, even if the workbook is not open, it is opened.


    It is a change in behaviour, we can live with it, but I wish they had communicated it better.




    • Edited by AntSalc Wednesday, April 29, 2020 5:54 PM
    Sunday, April 26, 2020 7:37 PM