0

I've been struggling with this problem for the last few hours and tried multiple solutions to no avail. I'm trying to open an excel 2016 (64-bit) report in MS Access, the default excel is 2003 and must stay that way for now. My code is:

Dim xlTmp As excel.Application
Shell ("C:\Program Files\Microsoft Office\root\Office16\EXCEL.exe")
Set xlTmp = GetObject(, "Excel.Application")

This code does exactly what I want only when I am stepping through in debug mode, because excel doesnt fully start up before it tries to grab the object. If run normally it throws up an error:

Run-Time error '429' ActiveX component can't create object

I've tried some of the following solutions to no avail:

Function OpenExcel()
   x = Shell("C:\Program Files\Microsoft Office\root\Office16\EXCEL.exe", vbNormalFocus)
   OpenExcel = x
End Function

Function GetOpenExcel() As excel.Application
    Set GetOpenExcel = GetObject(, "Excel.Application.16")

TryAgain:
    On Error GoTo NoExcel
    Set xlTmp = GetObject(, "Excel.Application.16")
    On Error GoTo 0
    xlTmp.Visible = True
    Set GetOpenExcel = xlTmp
Exit Function

NoExcel:
    Resume TryAgain
End Function

And this

Set ie = Nothing
cnt = 0

cnt = xlTmp.Windows.count
sh = Shell("C:\Program Files\Microsoft Office\root\Office16\EXCEL.exe",     vbNormalFocus)
Do Until xlTmp.Windows.count = cnt + 1
Loop
Set xlTmp = GetObject("excel.Application.16")
Set sh = xlTmp.Windows(xlTmp.Windows.count - 1)

I've also tried switching around my references a bit, with a little confusion, but here is what I have at the moment: MS Access References

Thanks in advance and any help would be appreciated.

Thomas Kelly
  • 3
  • 1
  • The approach you probably should take is similar to [this answer](https://stackoverflow.com/a/35343847/7296893). Also, I'd build in a substantial wait time between opening Excel and trying to get the class, else the application probably hasn't finished loading yet. A more mature solution would open up the Excel application using WinAPI too to be able to detect it's state and act when subwindows open. – Erik A Nov 08 '18 at 12:08
  • Thanks for the feedback, could you give some more direction for your mature solution. I would like to be able to take advantage of the windows API but not sure where to start with this. Any suggestions on where I can start with this? thanks – Thomas Kelly Nov 08 '18 at 14:51
  • Well... what I had in mind is: use [CreateProcess](https://learn.microsoft.com/en-us/windows/desktop/api/processthreadsapi/nf-processthreadsapi-createprocessw) to create an Excel process and get the process information, then [set up a hook to get notified when windows are created](https://stackoverflow.com/a/1024774/7296893). Then, when windows get created you can check if they belong to the just opened Excel process, and you can use the info from the other answer to identify the window that provides the `Excel.Application` class and retrieve it after creation. – Erik A Nov 08 '18 at 15:04

1 Answers1

0

Just use

Set xlTmp = CreateObject("Excel.Application")

instead of GetObject

UPDATE

This code worked for me, I have default 2016 and not default 2010:

Private Sub Command0_Click()
Dim xlTmp As Object
Shell "D:\Program Files (x86)\Microsoft Office 2010\Office14\EXCEL.EXE", vbNormalFocus

TryAgain:
    On Error GoTo NoExcel
    Set xlTmp = GetObject(, "Excel.Application")
    On Error GoTo 0
    'here I received correct xlTmp
Exit Sub

NoExcel:
    Resume TryAgain
End Sub
Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • With excel 2003 being the default excel, this just opens the 2003 version. – Thomas Kelly Nov 08 '18 at 11:34
  • Sorry, didn't get the question correctly. I tried your second variant with error trapping loop and it worked for me. What do you receive there? – Sergey S. Nov 08 '18 at 12:32
  • No problem, thanks for the suggestion. When I try the updated solution it opens the excel 2016 as desired but then infinitely loops trying to get the object and never succeeds. If I remove the error catch it comes up with the same ActiveX error as previously. It's worth noting that even stepping through in debug mode it now brings up this error – Thomas Kelly Nov 08 '18 at 13:45
  • Try to remove reference to Excel library. – Sergey S. Nov 08 '18 at 14:49