none
Missing Reference question RRS feed

  • Question

  • I wrote a simple access 13 application for a guy.  He installed it on his machine and ran it for a couple of weeks, it worked fine.  All the sudden, he got errors and it wouldn't run any more.  It still worked fine on my machine, so I asked him to send it back to me to test.  It was missing a reference, the "Microsoft Excel 15.0 Object Library".  I added that reference back, but it still didn't work quite right.  This app does a lot of manipulation, Import/export of table data to excel files and uses saved imports and exports.  Turned out that the saved imports and exports were now broken.  I recreated them and now everything works fine again.

    Couple of questions.  What might have caused the reference to suddenly be missing?  Would that missing reference have caused the saved import/exports to be corrupted?



    • Edited by tkosel Wednesday, May 23, 2018 2:15 PM
    Wednesday, May 23, 2018 2:14 PM

Answers

  • Hi,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    • Marked as answer by tkosel Thursday, May 24, 2018 8:18 PM
    Thursday, May 24, 2018 7:55 PM
  • To all,

    I have solved the issue, though not sure why.  Due to a offline suggestion by the DBGuy, I replaced the line DoCmd.RunSavedImportExport "Import-microsoftoffice365" with   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MicrosoftOffice365", "C:\Database\Office365Licensing\ImportFile\MicrosoftOffice365.xlsx", True

    This solved the issue on the office 365 machine, and it still works fine on mine too.

    Thanks to all of you for your suggestions, and I learned/am learning quite a lot from your input.

    --

    • Marked as answer by tkosel Thursday, May 24, 2018 8:07 PM
    Thursday, May 24, 2018 7:37 PM

All replies

  • Did the user remove (or repair) his install of Excel or Office? Sounds like you're using Access 2013. What version of Access was the client using?
    Wednesday, May 23, 2018 2:45 PM
  • " it wouldn't run any more."
    What happened exactly?  Did he receive any messages, which ones?

    The issue with "missing a reference, the "Microsoft Excel 15.0 Object Library"" is caused by your client having a more recent version of Office which will automatically upgrade such references and then you trying to run it on your older version that does not have that version of the reference so you need to replace it (as you did).


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    • Edited by Daniel Pineault (MVP)MVP Wednesday, May 23, 2018 2:51 PM Issues with the site again, had to make my entry in 2 parts.
    Wednesday, May 23, 2018 2:49 PM
  • It sounds like you have different versions of Microsoft Office - perhaps he has version prior to yours. This is the disadvantage of using "early binding" which depends upon a compatible version of the Office application being installed on a machine where the application is running.

    The workaround is to use "late binding" and CreateObject instead, which doesn't rely on a specific version of the Office application library (in this case Excel) to be installed. The below article will help explain the issue:

    Using early binding and late binding in Automation

    I would check with your user to see which version of Office he is working with. Messed up References can cause all sorts of problems.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 2:56 PM
  • No, he did not remove or repair.   He is using access 2007.
    Wednesday, May 23, 2018 2:59 PM
  • He got the error below.

    Code in debug was:

      DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    Wednesday, May 23, 2018 3:01 PM
  • No, he did not remove or repair.   He is using access 2007.

    And if you're using Access 2013, then he should get a missing reference because 2007 uses 12.0 objects.

    If you must develop using a higher Access version than your clients, I would also recommend using late binding as Paul mentioned.

    Wednesday, May 23, 2018 3:02 PM
  • To all,

    Thanks for all your input, this is all new to me.  I don't understand the early or late binding as Paul referenced, but would like to.  The referenced article for some reason does not let me understand exactly how to do it.  I don't know from the article which type of binding to use or how to do it.

    I did get a bit more information.  I had the user look at references and he doesn't have the "Microsoft Excel 15.0 Object Library" available.  He has "Microsoft Excel 16.0 Object Library".  Can I get that to work?  How would I do it?  I think my easiest solution since I don't understand it, would be to create the saved imports exports on his machine?

    Any help or alternative ideas will be appreciated.

    Wednesday, May 23, 2018 4:04 PM
  • Hi,

    What version of Access are you using? What version of Access is he using? If you're using 2013 and he's using 2016, then you should be fine. It's when you use a later version than your clients where you could run into these issues.

    So, in other words, you can select the 15.0 Object Library on your machine, and when he runs the db on his machine, Access will automatically replace it with the 16.0 Object Library.

    "Saving" the import/export specs on his machine doesn't make sense since those specs should be saved within your database. So, if you create an import spec on your machine and then give him a copy of your database, that spec should be available to him as well.

    Wednesday, May 23, 2018 4:23 PM
  • With regards to Early/Late Binding see if the following help:

    http://www.devhut.net/2016/11/08/vba-early-binding-and-late-binding/

    http://www.devhut.net/2017/02/16/vba-early-binding-and-late-binding-part-2/


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, May 23, 2018 4:37 PM
  • I am using access 2013 .  He is using Office 365 Pro Plus.

    I developed it on my machine.  When developed, I think it uses early binding by default.  At least that is what I gather from reading I have been able to understand.  So, on my machine the reference gets set to "Microsoft Excel 15.0 Object Library".  When he runs the app, it produces very meaningless errors and breaks on a line of code as follows.

    Code in debug was:

      DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    We looked at his references and the reference to "Microsoft Excel 15.0 Object Library" has been replaced with "Microsoft Excel 16.0 Object Library".  However, it will not run.  I suspect that it has to do with the import specifications that were created using the 15.0 object library.  Perhaps they are not compatible with the 16.0 library?  My thought is that if I create those specs on the machine using the 16.0 library, they will work?

    Wednesday, May 23, 2018 6:28 PM
  • Daniel,

    I very much appreciate your efforts to help me, but I am still not understanding this concept.  Am I correct to assume that by default, Access uses early binding?  Am I correct in assuming that if I use late binding, it will solve my problem?  I do not see where/how I should use code to make it use late binding.

    Wednesday, May 23, 2018 6:31 PM
  • Daniel,

    I very much appreciate your efforts to help me, but I am still not understanding this concept.  Am I correct to assume that by default, Access uses early binding?  Am I correct in assuming that if I use late binding, it will solve my problem?  I do not see where/how I should use code to make it use late binding.

    Here is an example that may help:

    Early binding:

    'Add Excel reference to project

    Dim x As Excel.Applicaton 'note explicit typing - object determined before runtime
    Set x = New Excel.Application

    Late binding:

    'No Excel reference added to project

    Dim x As Object 'note type of object will be determined at run time
    Set x = CreateObject("Excel.Application")

    With late binding you do not use explicit typing, but variables defined as Object instead. Note the main disadvantage of using late binding is that you lose Intellisense and type checking when compiling the VBA code.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 7:21 PM
  • Paul,

    Where do I put this code?


    • Edited by tkosel Wednesday, May 23, 2018 7:51 PM
    Wednesday, May 23, 2018 7:50 PM
  • Hi,

    I am not completely sure failing on a line of code like:

    DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    would be a early/late-binding issue.

    What did the "meaningless" error message say?


    • Edited by .theDBguy Wednesday, May 23, 2018 8:11 PM
    Wednesday, May 23, 2018 8:11 PM
  • When I choose Debug, this is the line that erred.  DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    Wednesday, May 23, 2018 8:34 PM
  • Hi,

    This error has nothing to do with References issue; rather, it has something to do with the Excel file you're trying to import. Some potential problems include illegal characters in the headers (like leading space characters) or your back end file is close to the 2GB file size limit.

    Just my 2 cents...

    Wednesday, May 23, 2018 9:02 PM
  • Except,

    How come it works fine on my machine using the same file?  The size of the file is 320 K and there are no leading spaces in the header row data.

    When I created the import spec, it works just fine, no import errors. When I call the import spec on my windows 7 machine using access 3013, it works fine.   It is only on the office 360 machine that I get errors and it won't work.

    • Edited by tkosel Wednesday, May 23, 2018 9:57 PM
    Wednesday, May 23, 2018 9:22 PM
  • Paul,

    Where do I put this code?



    You have to modify your own code for late-binding. Did you write the Excel automation code? I think we're going to need to see it in order to help you further.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 9:53 PM
  • Except,

    How come it works fine on my machine using the same file?  The size of the file is 320 K and there are no leading spaces in the header row data.


    Hi,

    I said "some" problem causes include those things. Besides, if you're importing multiple files, you'll have to find out which one is causing the issue.

    I still think the error message has nothing to do with the difference in the Access version numbers.

    Do you and him have the same access to the same files for importing?

    Wednesday, May 23, 2018 9:55 PM
  • Leo,

    I am not nit picking at your posts, I appreciate all the help.

    I am only importing one file.  I am parsing that file and exporting many files, but it never gets past the initial attempt to import the initial file.

    Yes, we are using the same file for the import.

    Wednesday, May 23, 2018 10:18 PM
  • Paul,

    I do not believe I have any excel automation code for the import portion.  I simply used Access to build a import spec; External Data, Excel, and used the wizard to import the data and saved the import steps.  It worked fine and I was able to repeat the process using this code in a on click event of a button..

    DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    It works fine until I run it on the office 365 machine.

    Wednesday, May 23, 2018 10:25 PM
  • Leo,

    I am not nit picking at your posts, I appreciate all the help.

    I am only importing one file.  I am parsing that file and exporting many files, but it never gets past the initial attempt to import the initial file.

    Yes, we are using the same file for the import.

    Hi,

    No worries. Can you share a copy of the Excel file? I'd like to test it, if possible.

    Wednesday, May 23, 2018 10:28 PM
  • Paul,

    I do not believe I have any excel automation code for the import portion.  I simply used Access to build a import spec; External Data, Excel, and used the wizard to import the data and saved the import steps.  It worked fine and I was able to repeat the process using this code in a on click event of a button..

    DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    It works fine until I run it on the office 365 machine.

    In that case you don't need the Reference to the Excel object library and can remove it from your project. Otherwise, it will just cause you headaches in the future.

    No need to worry about early-binding or late-binding at this point. :-)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 10:32 PM
  • Leo,

    Where would you like me to put it, or do you want to get it from my web site?

    Wednesday, May 23, 2018 10:47 PM
  • Paul,

    Perhaps I was too hasty.  I guess I do have Excel automation code in the project as I am later manipulating data in access spreadsheets with VBA code.  I have the following in my Startup module.

         Public MySheetPath As String
         Public Xl As Excel.Application
         Public XlBook As Excel.Workbook
         Public XlSheet As Excel.Worksheet
         Public V_rng

    I later, then use this to create/modify additional excel spreadsheets after the initial import is successful.  It appears to me that the initial import is what is failing.


    • Edited by tkosel Wednesday, May 23, 2018 10:53 PM
    Wednesday, May 23, 2018 10:52 PM
  • Here is what your code would look like using late binding:

         Public MySheetPath As String
         Public Xl As Object
         Public XlBook As Object
         Public XlSheet As Object
         Public V_rng
    
         Set X1 = CreateObject("Excel.Application")
    Pretty much anything else that uses the New keyword for Excel objects would require CreateObject instead.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 11:28 PM
  • Leo,

    Where would you like me to put it, or do you want to get it from my web site?

    Hi,

    Yes, either way. You can email it to me or post a link to your website.

    Also, can your client successfully import the problem Excel file manually (not using code)?

    Thursday, May 24, 2018 1:35 AM
  • Leo,

    I don't think I have your e-mail address.  I will have him test it today.

    Thursday, May 24, 2018 2:16 PM
  • Leo,

    I don't think I have your e-mail address.  I will have him test it today.

    Hi,

    It should be in my profile, but it's thedbguy[at]gmail.com

    Thursday, May 24, 2018 2:36 PM
  • Here is what your code would look like using late binding:

         Public MySheetPath As String
         Public Xl As Object
         Public XlBook As Object
         Public XlSheet As Object
         Public V_rng
    
         Set X1 = CreateObject("Excel.Application")
    Pretty much anything else that uses the New keyword for Excel objects would require CreateObject instead.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul, I believe I am making some progress.  I replaced the code you suggested and it ran fine.  I then removed the reference to Excel 15.0 objects and it Broke.  My code is below, awkward as it may be.

    ' ==================================  Section for  Summary ========================================================
         If Dir("C:\Database\Office365Licensing\ExportFile\Summary.xls") = "Summary.xls" Then
              Kill "C:\Database\Office365Licensing\ExportFile\Summary.xls"
         End If
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "4-SummaryFormLiscensesByDomainName_Crosstab", "C:\Database\Office365Licensing\ExportFile\Summary.xls"
        
         MySheetPath = "C:\Database\Office365Licensing\ExportFile\Summary.xls"
         Set Xl = CreateObject("Excel.Application")

         Xl.Visible = True
         Set XlBook = Xl.Application.Workbooks.Open(MySheetPath)
         Set XlSheet = XlBook.Worksheets(1)

         XlSheet.Rows(1).EntireRow.Insert
            
         XlSheet.Range("A1") = "Totals:"
         XlSheet.Range("B1") = "=Sum(B3:B1000)"
         XlSheet.Range("C1") = "=Sum(C3:C1000)"
         XlSheet.Range("D1") = "=Sum(D3:D1000)"
         XlSheet.Range("E1") = "=Sum(E3:E1000)"
         XlSheet.Range("F1") = "=Sum(F3:F1000)"
         XlSheet.Range("G1") = "=Sum(G3:g1000)"
         XlSheet.Range("H1") = "=Sum(H3:H1000)"
         XlSheet.Range("I1") = "=Sum(I3:I1000)"
         XlSheet.Range("J1") = "=Sum(J3:J1000)"
         XlSheet.Range("K1") = "=Sum(K3:K1000)"
         XlSheet.Range("L1") = "=Sum(L3:L1000)"
         XlSheet.Range("M1") = "=Sum(M3:M1000)"
         
         XlSheet.Range("N1") = "=Sum(N3:N1000)"
         XlSheet.Range("O1") = "=Sum(O3:O1000)"
          
          With XlSheet.Range("A1", "O1") ' Format A1:O1 as bold
             .Font.Bold = True
             .HorizontalAlignment = xlRight
          End With
         
          With XlSheet.Range("A2", "O2") ' Format A2:O2 as bold
             .Font.Bold = True
          End With
        
         Set V_rng = XlSheet.Range("A2", "O2")
         V_rng.EntireColumn.AutoFit
         
         XlBook.Save
         XlBook.Close
         Xl.Quit
         
         Set Xl = Nothing
         Set XlBook = Nothing
         Set XlSheet = Nothing
        

    It breaks on the bold line below

    With XlSheet.Range("A1", "O1") ' Format A1:O1 as bold
             .Font.Bold = True
             .HorizontalAlignment = xlRight
          End With

    Thursday, May 24, 2018 3:26 PM
  • To all,

    I have solved the issue, though not sure why.  Due to a offline suggestion by the DBGuy, I replaced the line DoCmd.RunSavedImportExport "Import-microsoftoffice365" with   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MicrosoftOffice365", "C:\Database\Office365Licensing\ImportFile\MicrosoftOffice365.xlsx", True

    This solved the issue on the office 365 machine, and it still works fine on mine too.

    Thanks to all of you for your suggestions, and I learned/am learning quite a lot from your input.

    --

    • Marked as answer by tkosel Thursday, May 24, 2018 8:07 PM
    Thursday, May 24, 2018 7:37 PM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    • Marked as answer by tkosel Thursday, May 24, 2018 8:18 PM
    Thursday, May 24, 2018 7:55 PM

  • It breaks on the bold line below

    With XlSheet.Range("A1", "O1") ' Format A1:O1 as bold
             .Font.Bold = True
             .HorizontalAlignment = xlRight
          End With

    What is the error?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 24, 2018 8:23 PM
  • Paul,

    Sorry, I did not include it.

    With XlSheet.Range("A1", "O1") ' Format A1:O1 as bold
             .Font.Bold = True
             .HorizontalAlignment = xlRight
          End With

    It breaks on the :

    .HorizontalAlignment = xlRight

    Thursday, May 24, 2018 8:44 PM
  • Paul,

    Sorry, I did not include it.

    With XlSheet.Range("A1", "O1") ' Format A1:O1 as bold
             .Font.Bold = True
             .HorizontalAlignment = xlRight
          End With

    It breaks on the :

    .HorizontalAlignment = xlRight

    Since you are no longer using the Excel library you can't use the built-in enumerated Constant of xlRight. Instead, you would need to specify the value, or create your own constant.

    Const xlRight = -4152

    With XlSheet.Range("A1", "O1") ' Format A1:O1 as bold
             .Font.Bold = True
             .HorizontalAlignment = xlRight
    End With



    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 25, 2018 2:08 AM
  • Paul,

    As usual, I am appreciative of your feedback. As you may note, I have a solution that I do not understand, but it does work.  Your posts have led me to believe there is another solution that you are pointing to.  I would like to pursue your thread for future use.

    However I do not understand why

    Const xlRight = -4152

    is the value I need to use.  What is the significance of the number

    Const xlRight = -4152

    How come the

     .Font.Bold = True

    works ok?  I do not understand why the DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MicrosoftOffice365", "C:\Database\Office365Licensing\ImportFile\MicrosoftOffice365.xlsx", True

    Works when the

    DoCmd.RunSavedImportExport "Import-microsoftoffice365"

    Does not.

    • Edited by tkosel Friday, May 25, 2018 4:01 AM
    Friday, May 25, 2018 3:47 AM
  • I was just addressing the early/late binding issue and the fact that you are coding for a different version of Excel. The constant xlRight is built into the Excel library. If you were to add the Excel library to your project you could view it in the Object Browser:

    Now if you use late binding, which generally means that the Excel library would not be added to your project, then all of the built-in constants for Excel would not be available. That is why you would need to define your own - one of the disadvantages of using late binding. BTW, the purpose here was to provide you with a way to write the code (using late binding) so that it functions irrespective of the version of Office that is being used, avoiding the missing References issue.

    I don't know why the save imports stopped working. It could be the user experienced some type of database corruption, one of the reasons why Microsoft recommends compacting the database periodically. In any event, this would be rather difficult to diagnose without knowing what happened on the user's end when he first experienced the issue.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 25, 2018 10:50 AM