Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO Excel source (Office 2000)

    I was unable to find online documentation on this. I'm using an Excel workbook as a data source from a Word document, with no problems thus far. But I discovered that the source may be password protected in the final implementation. If I know the password, how do I modify:

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

    to incorporate the password?

    Vaguely related, if a user has their Excel security set to High, and the data source workbook contains macros, I'm assuming that this won't affect DAO from Word VBA, just the Excel UI. Would this be correct?

    thanks
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Excel source (Office 2000)

    According to the online help, using "Excel 8.0; pwd=secret" as Connect argument should work, but it doesn't for me. Does it work for you?

    I don't think macro security comes into this, since Excel is not started.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Excel source (Office 2000)

    Nope <img src=/S/nope.gif border=0 alt=nope width=15 height=15> didn't work for me either. I saw it mentioned in reference to other than an XL source, so thought it must be incorrect syntax for the latter when it didn't work. Guess I'll keep a huntin'.

    thanks
    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Excel source (Office 2000)

    The screenshot is from the online help for OpenDatabase; it explicitly mentions a connect string for Excel with a password. But Microsoft Office Developer Forum - Connection String Parameters in Microsoft Access doesn't mention it, and the same question has been asked several times in the newsgroups, without a solution as far as I can see. Sorry.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Excel source (Office 2000)

    Thanks Hans. I probably looked at the same newsgroups, and got the same <img src=/S/sad.gif border=0 alt=sad width=15 height=15> results.

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Excel source (Office 2000)

    Knock me down with a feather! Today it works! I hope it's reliable from system to system though. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>.

    Later I find that it only works (with or without password) when the p/w protected XL file is already open... which sort of defeats the purpose of automation altogether I'd say. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Alan

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Excel source (Office 2000)

    You could start Excel in code, and open the workbook using Workbooks.Open, specifying the Password argument.

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Excel source (Office 2000)

    <P ID="edit" class=small>(Edited by AlanMiller on 28-Apr-05 16:04. )</P>True, but the hope here was that the Excel application didn't even exist in the eyes of the Word doc users (as, indeed it shouldn't have to when the automation works as expected). Things may require a bit of a rethink at user levels, in view of this "limitation".

    cheers
    Alan

    Edited - In light of your previous advice on ADO in another thread, do you think this might be more reliable, being newer, trendier and all that? Worth a shot maybe?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Excel source (Office 2000)

    If you start Excel using Automation, it will run invisibly (there will be no window and no icon in the task bar) unless you make it visible explicitly. So the user doesn't need to know that you have opened Excel.

    When DAO didn't work for me, I did try ADO, but I got the same kind of error.

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Excel source (Office 2000)

    Thanks Hans. Bad news on the second item, but offset by good news on the first! I think a solution is coming. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •