Encryption

[jmk15315]

To say I am a Rookie is a mild understatement for sure, but have learned a lot from the great people here so, I know there is someone here, far smarter than I am, that might be able to point me in the correct direction.

I have a sheet that I used to import multiple sheets for the purpose of combining them for evaluation. The code I am using works to accomplish the task however, there are two issues that I have been unable to resolve.

1. I have made the latest version of my imported sheets encrypted. This adds another step in the import process.
- My ask is if there is a way to have the code enter the password in the background so I do not have to enter the password every time I import the file.
2. After importing the data, the code closes the file from where it was imported, but I get a "Do you want to save changes you made to (filename.xlsm) Yes/No/Cancel" dialog box. I simply want to close the file without saving it.

Here is the code I am using:

Sub ADD_COST_SHEET()

  Dim wbSource As Workbook, wsSource As Worksheet, wsComposite As Worksheet
  Dim sSource As String, sComposite As String, iDataRow As Long, rFound As Range
 
  sComposite = ActiveWorkbook.Name
 
    If ActiveCell.Column <> 1 Then
      MsgBox "Please ensure the selected cell is after the header row and in column A", , "Incorrect Cell Selected"
      Exit Sub
      End If
 
  Set rFound = ThisWorkbook.Worksheets("Multiple Cost Sheet Data").Range("A:A").Find("TOTALS:", LookIn:=xlValues)
 
    If ActiveCell.Row > 4 And ActiveCell.Row < rFound.Row Then
      iDataRow = ActiveCell.Row
    Else
      MsgBox "Please ensure the selected cell is between the header row and the TOTALS:" _
         & " and in column A", , "Incorrect Cell Selected"
      Exit Sub
    End If
 
  Application.Dialogs(xlDialogOpen).Show (ActiveWorkbook.Path)
 
    sSource = ActiveWorkbook.Name
    Set wbSource = Workbooks(sSource)
    Set wsSource = wbSource.Worksheets("Summary")
    Set wsComposite = ThisWorkbook.Worksheets("Multiple Cost Sheet Data")
 
  With wsComposite
    wsSource.Range("MultipleCostSheetData").Copy
    .Range("A" & iDataRow & ":DE" & iDataRow).PasteSpecial xlPasteValues
   
  End With

' UNLOCK WORKBOOK
    ActiveWorkbook.Unprotect (CAT_PROTECT)
   
  Windows(sComposite).Activate
  wbSource.Close SaveChanges:=False
 
 
 'LOCK WORKBOOK
    ActiveWorkbook.Protect (CAT_PROTECT)
   
  End Sub

Any assistance would be greatly appreciated.

 

[RoryA]

Your code doesn't actually open the file itself (it puts up a dialogbox for the user), so you don't have anywhere that you can add a password. You could probably alter it to use Application.getopenfilename to get the name of the file to open then actually open it in code.

Your code also already specifies not to save changes, so you really shouldn't be seeing a prompt at all. Is there perhaps code in the source workbook that is causing the prompt?