Using Cell value in VBA as Password to Open file

[Anabaric]

Hi All,

Noob question - I have an issue using a cell reference in VBA for the password in the rest of the script.

Background: I have 40+ reports that I need to use PowerQuery with, however they all have password protection to open which causes issues unless they are all open in Excel, or I manually remove the passwords.
I've managed to get the script working from a Form button in Excel, but every month the password changes.
What I want to do is have a field in the Excel sheet where I can enter the password each month, alternatively a popup for the password at the beginning of the script.

Would appreciate a poke in the right direction, as I suspect I've missed something silly.

Sub RemPass()

Dim PassOpen As String
Dim PassEdit As String
Cells(2, 5) = PassOpen 'when I try to use this it fails, the reference is in Cell B5 of the open spreadsheet. 
Cells(2, 6) = PassEdit
Dim FolderIn
Dim Matrix As Workbook
Dim strFilename As Variant
'PassOpen = "currentpassword"  'if I use this it works
'PassEdit = ""

    strFilename = Dir$("S:\TEMP\RemovePass\")
    FolderIn = "S:\TEMP\RemovePass\"
       
While strFilename <> ""

        Application.DisplayAlerts = False
               Set Matrix = Workbooks.Open(filename:=FolderIn & strFilename, _
                                    Password:=PassOpen, _
                                    WriteResPassword:=PassEdit)
        Matrix.SaveAs filename:=FolderIn & strFilename, _
                      Password:="", _
                      WriteResPassword:="", _
                      CreateBackup:=False
        Matrix.Close 0
        Application.DisplayAlerts = True
        strFilename = Dir
        
       Wend
MsgBox "All passwords have been removed."

End Sub

 

[Kaper]

If you want to use popup it will be as easy as:

Sub RemPass()

Dim PassOpen As String
Dim PassEdit As String
Cells(2, 5) = PassOpen 'when I try to use this it fails, the reference is in Cell B5 of the open spreadsheet. 
Cells(2, 6) = PassEdit
Dim FolderIn
Dim Matrix As Workbook
Dim strFilename As Variant
PassOpen = InputBox("Please enter this month password", "Input psswd to be removed")

'the rest of code

if you want to refer to cell A1 in Sheet1 of workbook with code

Sub RemPass()

Dim PassOpen As String
Dim PassEdit As String
Cells(2, 5) = PassOpen 'when I try to use this it fails, the reference is in Cell B5 of the open spreadsheet. 
Cells(2, 6) = PassEdit
Dim FolderIn
Dim Matrix As Workbook
Dim strFilename As Variant
PassOpen = Thisworkbook.Sheets("Sheet1").Range("A1").value
'the rest of code

 

[Anabaric]

Excellent, that fixed it perfectly!

 

[Kaper]

Glad to hear that!