MISSING: Excel 16.0 Object Reference Library

[Paul H]

Hello fellow travelers. My application has a reference to the Excel 16.0 Object Reference Library. I believe it was already referenced when I inherited it, but I may be mistaken. I'm not sure what I need it for. I do have some exports to Excel though. That's about it. This app has been distributed to 25 or so different facilities around the state. It is 100% standalone. There is not Back End. I've posted about it many times. It is in UAT right now. A few facilities have reported a problem with a command button not working. Most have not and I have not experienced that either. After remoting in I found the MISSING tag in the references. I unchecked the box and the button worked again. This app's been in use for years. This is the first time this has come up.



Is it safe to remove that reference or will it produce a different set of errors?

Thanks in advance,

Paul H

[Gicu]

Hi Paul,

Make sure you the code uses late binding (declare your variables as object as opposed to as Excel.Work....) and you should be OK to remove the reference.

Cheers,

[Paul H]

I'm not sure what that means. I found some code that I don't think is being used. Could this be the culprit?
EDIT: I see the evidence right in front of me. I don't think I need either the code or the reference if it is not being used.

Public Function OpenExcelFile(strFilePathName As String)    'Required: Tools > Refences: Add reference to Microsoft Excel Object Library
    '    Form_frmMain.tabDataQualityTools.Pages(4).SetFocus


    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
 
    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open(strFilePathName)
    appExcel.Visible = True
    
    'Do Something or Just Leave Open
    
    Set appExcel = Nothing
    Set myWorkbook = Nothing
 
End Function

[Micron]

AFAIK, you need the Excel reference to do automation. If not, that will be enlightening to say the least.
One thing that could be done is to have a table of references and check those against the references in the project. If one is missing, you could either attempt to re-establish it (which would likely fail if versions have changed) or provide a message. What you do there would depend on circumstances, but allowing an email to be sent to you might be an option - or simply asking the user to report the message. You don't have to shut down the db because of a missing ref, but it might be better to know about it before you start some process that will fail.

[ranman256]

different pc have different excel version, so just set the ref the one they have.

[Gicu]

Late binding:

Public Function OpenExcelFile(strFilePathName As String)    'Vlad 11/6/2020 NOT Required: Tools > Refences: Add reference to Microsoft Excel Object Library
    '    Form_frmMain.tabDataQualityTools.Pages(4).SetFocus




    Dim appExcel As Object 'Excel.Application
    Dim myWorkbook As Object 'Excel.Workbook
 
    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open(strFilePathName)
    appExcel.Visible = True
    
    'Do Something or Just Leave Open
    
    Set appExcel = Nothing
    Set myWorkbook = Nothing
 
End Function

Cheers,

[ranman256]

if you set the reference on a PC with the oldest version, then when you give it to a pc with newer version, it will self-install the new version. no errors, no manual work needed.
but you cant go backwards. (give new ver. to old pc)

[Paul H]

Vlad et al,

I'll take one more whack at this. If I delete these functions and any reference to them. Could I safely remove the reference to the Excel 16.0 Library?
Wouldn't that not be the simplest way since they are no longer in use? I'm not familiar creating Excel objects in Access and I'd just as soon not use them.

[Micron]

Sure, removing the code is enough. Commenting it out would be another way.
Removing the reference is a 'bonus'. Hope you're right that the function(s) are no longer used!

[Paul H]

I've scoured the code. I think I've got it covered. It's going out for testing so I'll find out one way or the other.

Thanks to all for your replies. This forum has been invaluable.