Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Sep 2003
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    passwords (excel 2002)

    I am wanting to import a cell from a password protected worksheet to another excel worksheet ( diffrrent files). I want to build a dns to perform this action. I have tried using the import new database query but it fails upon connect ( unable to decrypt file). The field to put the password in is grayed out. Does anyone know how to pass the password so I can pull the information in. If anybody know another way to do this I will try this. I tried a link but still ran into the password issue. Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: passwords (excel 2002)

    If you have the password, you could open the file with the password get the cells contents, then close it.

    Steve

  3. #3
    Lounger
    Join Date
    Sep 2003
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords (excel 2002)

    I am building a worksheet that pulls in totals from 30 different quickbooks files, 7 foxpro databases and these two excel spreadsheets. I am trying to have this fully automated so I do not have the option of unlocking the excels spreadsheet in a seperate sheet. Thanks for the suggestion though. thanks

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords (excel 2002)

    As long as you are aware which cells you are trying to pull values from, you don't actually have to open the workbook at all. Do all the protected workbooks have the same structure?
    Gre

  5. #5
    Lounger
    Join Date
    Sep 2003
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords (excel 2002)

    When I build the dns to pull in the data and do the connect it says unable to decrypt files. What steps do you suggest to pull the data into the worksheet.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords (excel 2002)

    This is "pseudo" code, but I meant something along the lines of:<pre> With Range(strTargetRangeName)

    'import all the values as formulas
    .Range([size of Range in Target Workbook - in cell notation]) = _
    "='" & strSourceFilePath & _
    [first cell of equivalently-sized range in Source Workbook]

    'convert them into hard numbers (equivalent of Paste Special - Values)
    .Range([size of Range in Target Workbook - in cell notation]) = _
    .Range([size of Range in Target Workbook - in cell notation]).Value

    End With</pre>

    Please post back if this is not altogether clear.
    Gre

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords (excel 2002)

    Assuming you know the password to open the xl files containing your totals, start with this:

    (Book1.xls contains one total in a cell named SourceTotal.
    Book1.xls requires password: "YogiB" to open

    Sub test()

    Dim SourceFile As String
    Dim Target as Range

    Set Target as ThisWorkbook.Sheets(1).Range("A1")
    SourceFile = "Cocuments and SettingsPaulDesktopBook1.xls" 'adjust path and name

    Workbooks.Open Filename:=SourceFile, Password:="YogiB" 'adjust password
    Target= Workbooks("Book1").Sheets(1).Range("SourceTotal")
    ActiveWorkbook.Close

    'Repeat for the other xl file
    End Sub

  8. #8
    Lounger
    Join Date
    Sep 2003
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords (excel 2002)

    Thanks I will add it to my macro

Posting Permissions

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