Get data from closed workbook that is password protected

Posted on 2012-09-04
Last Modified: 2012-09-04
I need to get some data from this range in my closed workbook that is password protected:


How can I retrieve this data and paste special (values) into my active workbook into range A2:C9?
Question by:Lawrence Salvucci
    LVL 11

    Expert Comment


    Workbooks.Open Filename:="Full Path Name", Password:="Apassword",  _ WriteResPassword:="Apassword"
    LVL 1

    Author Comment

    by:Lawrence Salvucci
    I also need to copy and paste special a specific range from this closed workbook and paste the values into my active worksheet.
    LVL 17

    Accepted Solution

    Try this - assuming you want to copy to the active sheet, and your source data is on the first worksheet of the protected workbook:
    Public Sub CopyFromPwDoc()
        Dim rngCopyTo As Range
        Dim rngCopyFrom As Range
        Dim wbkProtected As Workbook
        Set rngCopyTo = ActiveSheet.Range("A2:C9")
        Set wbkProtected = Application.Workbooks.Open(FileName:="c:\users\stuart\desktop\protected.xlsx", Password:="Password1")
        Set rngCopyFrom = wbkProtected.Worksheets(1).Range("M22:O29")
        rngCopyTo.Value = rngCopyFrom.Value
        wbkProtected.Close savechanges:=xlDoNotSaveChanges
    End Sub

    Open in new window

    If it's not on the first sheet in the protected workbook, use Worksheets("MySheetName") instead.  Obviously substitute your own file name and password
    LVL 17

    Expert Comment

    Note that this does not use copy/paste - in VBA it's nearly always better just to assign the values from one range to another.  Copy/paste can be useful if you need to take formats as well, but generally is an unnecessary overhead.
    LVL 1

    Author Closing Comment

    by:Lawrence Salvucci
    Thanks Andrew! Much appreciated!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now