Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts

    fetching data from closed workbook (Excel2003)

    Hi

    I know how to import data from a closed workbook.
    My question is:
    If I know the workbook password, can I import data automatically from a password protected closed workbook?

    At the moment, when I execute my import routine I am presented with a Password dialog box. After I enter the password, the import works OK.
    Can I include the password string in my import routine???

    zeddy

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

    Re: fetching data from closed workbook (Excel2003)

    I think you'll have to open the workbook. The Workbooks.Open method lets you specify a password.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: fetching data from closed workbook (Excel2003)

    Hi Hans

    But I don't want to open the book.
    It's big and I only want a little bit.

    After I enter the password in the dialog box, I get the data I want without opening the book.
    I would rather have my VBA 'pass' the required password

    zeddy

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

    Re: fetching data from closed workbook (Excel2003)

    This might work to get the value of cell A1 on Sheet1 in C:ExcelTest.xls. The password is Secret.
    <code>
    Dim strCellRef As String
    Dim varValue As Variant
    strCellRef = "'C:Excel[Test.xls]Sheet1'!R1C1"
    SendKeys "Secret{Enter}"
    varValue = ExecuteExcel4Macro(strCellRef)
    </code>
    But SendKeys isn't always dependable.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: fetching data from closed workbook (Excel2003)

    Hi Hans

    I am currently using something like this..

    Sub fetchValuesFromClosedWorkbook(zFolder As String, zFilename As String, zSheetname, zRange As String)

    With ActiveSheet.Range(zRange)
    .FormulaArray = "='" & zFolder & "[" & zFilename & "]" & zSheetname & "'!" & zRange
    .Value = .Value
    End With

    End Sub


    In another routine, I get the required values for

    zFolder
    zFilename
    zSheetname
    zRange

    e.g.
    "C:Excel"
    "Test.xls"
    "Sheet1"
    "a20:q200"

    ..I move to my import sheet, pass these values to the routine and then get back the data in the corresponding range.
    It works OK without a password-protected book, seems quite fast and I can easily modify it to suit my needs.
    ..But if I could supply the password too it would be really really nice.

    zeddy

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

    Re: fetching data from closed workbook (Excel2003)

    As far as I know, that is not possible. If you search Google, I don't think you'll find anything except the ExecuteExcel4Macro approach. If you do, I'd love to hear about it.

Posting Permissions

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