none
Add Reference browser does not see .olb file RRS feed

  • Question

  • I have an application which needs a reference to an old Excel object library, excel9.olb. When I need to install the app on a new computer I first add the excel9.olb file to c:\windows\system32 and the app sees the reference with no problem.

    However, I just added the app to a computer that already had a newer version of Excel. When I open the vba editor in Access, and click on Tools>References, the reference to Excel 9 is replaced with a reference to Excel 14. If I try to manually add the Excel9.olb reference by clicking Browse, the file picker window doesn't even see c:\windows\system32\excel9.olb. I've double checked that the file is in that folder, but the Add Reference browser cannot see it.

    Is there a way around this?

    TIA for any suggestions.

    Thursday, December 29, 2011 7:07 PM

Answers

  • On which line does it fail? If the last, looking at Excel 2010, xlText isn't a valid file format: you need either xlTextMSDOS (which has a value of 21) or xlTextWindows (which has a value of 20). I don't have access to an older version of Excel at the moment: what is the value of xlText in Excel 2000?

    To be honest, I'm shocked that you can get that code to work on a computer that doesn't have Excel previously installed. I wouldn't have thought that simply copying the .olb file would work at all!

    Since all you're doing is creating a delimited text file, why bother going through Excel? You should be able to simply concatenate the values for a single row with a delimiter between them, then write them out to a text file using built-in VBA commands like Open, Print and Close.

     


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 29, 2011 8:40 PM

All replies

  • .olb files cannot simply be copied like that. They must be installed by installing the associated application (in this case, Excel 2000)

    What are you hoping to accomplish? It sounds as though Excel 2010 already exists on the machine: you cannot have two different versions of Excel on a single machine.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 29, 2011 7:55 PM
  • Thanks for your response, Doug. I have been able to do this before, on computers that do not have any version of Excel installed -- that is, I simply copy the excel9.olb file to the system32 folder and the app sees the reference and runs fine. You're right that the problem this time is related to the fact that this computer already has a newer version of Excel installed.

    There is some code in my app that works only with Excel 2000. Later versions of Excel changed something that breaks that code. That is why I need the reference to the old Excel olb.

    But you've given me an idea. I will delete Excel 2010 from the problem computer and see if the app can then see the excel9 reference.

    TIA for any other ideas or suggestions.

    Thursday, December 29, 2011 8:05 PM
  • Even if it does work when you copy the .olb file, it's not legal.

    Now that you've got the code written, you should be able to use Late Binding instead. When you instantiate the Excel application instance, try:

      Set xlApp = CreateObject("Excel.Application.9")


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 29, 2011 8:10 PM
  • Thanks for the suggestion. I didn't mean to do anything illegal. I just want to create a file in Excel spreadsheet format that can then be imported into QuickBooks.

    Below is part of the code that uses Excel references. I don't know which reference actually breaks with later versions of Excel. (It's very old code and I would have to debug it again to find the exact breaking point.) But if you could give this a glance and let me know what you think. Is there a better way to do this (legally)? Do you see where/why this code doesn't work with later versions of Excel?

    Thanks for your time,

    Mike

    =============

    Set objSheet = CreateObject("Excel.Sheet")
        Set objXLS = objSheet.worksheets(1)

        objXLS.Cells(1, 1).Value = "!TRNS"
        objXLS.Cells(1, 2).Value = "TRNSID"
        objXLS.Cells(1, 3).Value = "TRANSTYPE"
        objXLS.Cells(1, 4).Value = "DATE"
        objXLS.Cells(1, 5).Value = "ACCNT"
        objXLS.Cells(1, 6).Value = "NAME"
        objXLS.Cells(1, 7).Value = "AMOUNT"
        objXLS.Cells(1, 8).Value = "DOCNUM"
        objXLS.Cells(2, 1).Value = "!SPL"
        objXLS.Cells(2, 2).Value = "SPLID"
        objXLS.Cells(2, 3).Value = "TRANSTYPE"
        objXLS.Cells(2, 4).Value = "DATE"
        objXLS.Cells(2, 5).Value = "ACCNT"
        objXLS.Cells(2, 6).Value = "INVITEM"
        objXLS.Cells(2, 7).Value = "AMOUNT"
        objXLS.Cells(2, 8).Value = "MEMO"
        objXLS.Cells(3, 1).Value = "!ENDTRNS"

        intRow = 3
        strMemo = "Carry Forward from DZAP Invoice for Period " _
                & Format(Me.txtFromDate, "mm/dd/yyyy") & " to " & Format(Me.txtToDate, "mm/dd/yyyy")

        rstI.MoveFirst

        Do While Not rstI.EOF
            intRow = intRow + 1
            objXLS.Cells(intRow, 1).Value = "TRNS"
            objXLS.Cells(intRow, 3).Value = "INVOICE"
            objXLS.Cells(intRow, 4).Value = rstI!TransDate
            objXLS.Cells(intRow, 5).Value = "Accounts Receivable"
            objXLS.Cells(intRow, 6).Value = rstI!ClientName
            objXLS.Cells(intRow, 7).Value = rstI!Total
            objXLS.Cells(intRow, 8).Value = rstI!InvoiceNo
            intRow = intRow + 1
            objXLS.Cells(intRow, 1).Value = "SPL"
            objXLS.Cells(intRow, 3).Value = "INVOICE"
            objXLS.Cells(intRow, 4).Value = rstI!TransDate
            objXLS.Cells(intRow, 5).Value = "DZAP CHARGES"
            objXLS.Cells(intRow, 6).Value = "DZAP CHARGES"
            objXLS.Cells(intRow, 7).Value = 0 - rstI!Total
            objXLS.Cells(intRow, 8).Value = strMemo
            intRow = intRow + 1
            objXLS.Cells(intRow, 1).Value = "ENDTRNS"
            rstI.MoveNext
        Loop

        objXLS.Columns("G:G").NumberFormat = "0.00"

        objSheet.SaveAs Filename:="F:\QBExport\" & Format(Me.txtFromDate, "yyyymmdd") & "_LA.xls"
        objSheet.SaveAs Filename:="F:\QBExport\" & Format(Me.txtFromDate, "yyyymmdd") & "_LA.iif", FileFormat:=xlText

     

    Thursday, December 29, 2011 8:26 PM
  • On which line does it fail? If the last, looking at Excel 2010, xlText isn't a valid file format: you need either xlTextMSDOS (which has a value of 21) or xlTextWindows (which has a value of 20). I don't have access to an older version of Excel at the moment: what is the value of xlText in Excel 2000?

    To be honest, I'm shocked that you can get that code to work on a computer that doesn't have Excel previously installed. I wouldn't have thought that simply copying the .olb file would work at all!

    Since all you're doing is creating a delimited text file, why bother going through Excel? You should be able to simply concatenate the values for a single row with a delimiter between them, then write them out to a text file using built-in VBA commands like Open, Print and Close.

     


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 29, 2011 8:40 PM
  • I'd have to debug it again to find the line that fails. This is very old code. I was hoping to find a quick fix for the reference problem and not have to visit the code again. It was a user requirement to have an Excel spreadsheet. That's the way they wanted to import the data into Quickbooks. I'll see if they're willing to revisit that.

    Thanks again for all your help on this.

    Thursday, December 29, 2011 8:55 PM