Add unique password based on list in another workbook to each new created workbook to this VBA code?

HopefulGJL

Board Regular
Joined
Nov 28, 2008
Messages
100
Hello,

I have the following code which is creating a new workbook for me (.xlsx with no code) based on the name in cell A2 of Sheet1 (which changes based on the name in cell A1 of Sheet1). It also closes the new workbook, and keeps this .xlsm workbook open exactly as I need it to do.

It next deletes all the external links in the new workbook to keep just the values, which is also what I need it to do.

Next it adds a password to all sheets of each new workbook, which is also what I need it to do.

I'm now looking to add a protect each newly created workbook with a password, but with a catch. There is a list of passwords in a separate workbook. So, if the name of the new workbook matches column C of the other workbook, then take password from column D (of that same other workbook), and apply password to the new workbook. To clarify, yes, each newly created workbook needs a unique password.
I have done many online searches, but haven't seen anything available out there that I can use to create this scenario.

Thanks in advance for any assistance you can provide.

VBA Code:
Sub BreakLinksandSave()
  Dim link As Variant, wb As Workbook, origFile As String, newFile As String, wsheet As Worksheet

  Set wb = Application.ActiveWorkbook

  If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
    For Each link In wb.LinkSources(xlExcelLinks)
      wb.BreakLink link, xlLinkTypeExcelLinks
    Next link
  End If
  For Each wsheet In ActiveWorkbook.Worksheets
  wsheet.Protect Password:="1234"
  Next wsheet

  origFile = wb.FullName
  Application.DisplayAlerts = False
  newFile = ActiveWorkbook.Sheets(1).Range("A2").Value
  ActiveWorkbook.SaveAs Filename:="C:\File Pathway\" & newFile, FileFormat:=xlOpenXMLWorkbook
  newFile = ActiveWorkbook.Name

  Workbooks.Open origFile, UpdateLinks:=True
  Application.DisplayAlerts = True

  Workbooks(newFile).Close False
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,765
Members
418,412
Latest member
fehr56

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top