+ Reply to Thread
Results 1 to 12 of 12

Batch open and print password protected Excel workbooks

  1. #1
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Batch open and print password protected Excel workbooks

    I have invoices I must send and receive each month. When I prepare them, they are saved in a folder unprotected. I then have a macro that prepares a list for my manager to check the figures. If she agrees, I open each one and send. The VBA for that, re-protects the file and sends it to the appropriate employee.

    They "digitally" sign it by completing the name and date field, and send it back to me, still protected. I affix SIGNED to the end of the filename and place all in a "Signed" folder.

    What I need is a routine that will batch open the files from a list of the passwords (perhaps in a vlookup), and then prints the appropriate page.

    Is it possible on the open dialog to have a macro run to open with the appropriate password inserted into the dialog?

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Batch open and print password protected Excel workbooks

    You could do it like this with the workbook name in column A and the password in column B:

    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  3. #3
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    Thanks! It manages to open the first wkbk so that portion is working great, however, it thrown an error on the next line and stops.

    Sub PrintWorkbooks()
    Dim cell, rng As Range
    Dim wb_loc, wb_name, wb_ext, wb_pw As String
    Dim wkbk As Workbook

    wb_loc = "C:\temp\Test\"
    wb_ext = ".xlsx"
    Set rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    For Each cell In rng
    wb_name = cell.Value
    wb_pw = cell.Offset(0, 1).Value
    Set wkbk = Workbooks.Open(Filename:=wb_loc & wb_name & wb_ext, Password:=wb_pw)
    wkbk.Sheets(Sheet3).PrintOut Copies:=1 '(Set what you want to print)
    wkbk.Close SaveChanges:=False
    Next cell
    End Sub

  4. #4
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Batch open and print password protected Excel workbooks

    Sheets(Sheet3) should be Sheets("Sheet3")

  5. #5
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    I tried it many ways including that and it says "subscript out of range"

  6. #6
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Batch open and print password protected Excel workbooks

    Whoops, I think I led you down the garden path... remove the wkbk. from that line. It should be just...

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    Same....Subscript out of range

  8. #8
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    I changed it to the actual sheet name:
    Sheets("Invoice").PrintOut Copies:=1

  9. #9
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    It works! Now....In some months, some instructors have no invoice. How do I set it such as it will skip a wrkbk if that book isn't present?

    For example for this test, I only put three workbooks in the folder. But in any given month, there might be some on the list not invoiced
    Last edited by mark1955; 03-02-2017 at 11:13 PM.

  10. #10
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    Actually, no worries! If I hide the lines with files not found in the folder, it runs perfectly. If as an exercise you wish to tackle something that wouldn't need that, great. Otherwise, you did a great service for me today! Thank you!

  11. #11
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Batch open and print password protected Excel workbooks

    This will check if the file exists in that location otherwise it will skip it.

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-14-2015
    Location
    Tokyo, Japan
    MS-Off Ver
    Office 2007
    Posts
    26

    Re: Batch open and print password protected Excel workbooks

    great Thank you, as I discovered that only worked when the hidden files were at the end. It also needs the .xlsx extension. Is there a way to put a wild card for that, such as .xls* ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Running workbooks.open to open pax-protected file still prompts for a password
    By karinincanada in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2015, 05:06 PM
  2. Open Password Protected Workbooks in a Folder Through a Macro
    By aditya88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-12-2014, 01:53 PM
  3. Replies: 3
    Last Post: 04-11-2014, 07:06 AM
  4. Open password protected excel using vba automatically
    By soul_krasty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2010, 01:06 AM
  5. How to make Workbooks.Open skip password protected files
    By hoffey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2009, 11:01 AM
  6. macro to batch fix errors on password protected workbooks
    By spence in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2005, 12:05 AM
  7. Replies: 4
    Last Post: 08-25-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1