1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

DAO problems with excel 2016

Discussion in 'Business Applications' started by CMIreland, Oct 29, 2017.

Thread Status:
Not open for further replies.
Advertisement
  1. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    Hi

    I converted a large addin from excel 2003 using DAO to access several workbooks.

    Now using excel 2016.

    The addin works in both Update and read-only modes. A 2nd user logging on will open the workbooks in read only mode automatically.

    The conversion to 2016 went well but DAO will not work when the workbooks are opened in read-only mode. I get the error "external table is not in the expected format".

    The exact same code works fine when the workbooks are opened in update mode.

    Very puzzling - can anybody help?

    I am really frustrated as I have a lot of users depending on the outcome. They have to continue using 2003 for the moment.


    The line in the code that fails is ...

    Set db = OpenDatabase(myfile, False, False, "Excel 8.0")

    myfile is an .xlsx file

    help very much appreciated
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,831
    You could try the the more normal open workbook method, which does not require a version and you can specify the format if required, like text etc.

    Workbooks.Open myfile

    the variable myfile should include the suffix .xlsx and the full path to the workbook.
     
  3. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9

    Many thanks for the input....

    The workbook is already open as Read-only when the problem occurs. I am making a database connection to the file in order to retrieve data using SQL. Users automatically get a read-only copy of the file once the 1st user has write access. The user with write access has no problem running the code.

    I thought the problem might be that I was asking for write permission in the statement when the file was read-only. However, I have tried all combinations of the "True False" parameters in the offending statement with no success.

    I do not need write access to the file...I am not using SQL to write any data back to the workbook.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,831
    So is this an Excel query question or an SQL database question?
    As I am not familiar with SQL databases.
     
  5. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    I suppose it's a bit of both. The excel file is being treated as a database for the purpose of the SQL query.

    Just frustrating that the code worked perfectly in office 2003 no matter if the excel file had been opened in read only or not and works fine in office 2016 but not if the file has been opened read only.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,831
    Is the Excel database in 2016 format?
    What happens if you save it as CSV instead?
     
  7. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    I converted the files from .xls to .xlsx just by opening them and then saving them as 2016 workbook.

    I have no idea re CSV save but that's not something I want to contemplate.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,831
    Does it still work OK in xls format, as 2016 should happily work with it?
     
  9. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    I haven't tried that. I changed all the references in the code to xlsx.

    I might try to make a copy and rename references back to xls. Worth a try...thanks for the tip
     
  10. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    tried that with a quick search & replace for xlsx.

    no problem when file opened with write access.

    got a different error when file opened read-only...

    3725 - "unexpected error with external database driver"
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,831
    Is it the original xls database or just a renamed xlsx one.
    I would try it with the original 2003 one if you have a copy, you can always update the original with any new data in the 2016 version.
     
  12. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    the xls files are the originals..sadly
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,831
    That is very odd, especially as it was working OK.
    It sounds more like an SQL problem that it can't identify for itself.
    Unfortunately I don't know much about SQL databases.
    Can you create a blank Excel Sheet that it can interact with in either format?
     
  14. CMIreland

    CMIreland Thread Starter

    Joined:
    Feb 4, 2015
    Messages:
    9
    I think I will try to switch to using ADO for database access and see how I get on with that.

    Many thanks for your input.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1198643

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice