opening a password protected file using a macro

[martha555]

:whistle:

I have two requirements.
1. I have created a file named "abc100.xls". I have another excel file "main.xls", where I have written a macro to extract a value from file "abc100.xls". The problem is, that this file is password protected, hence when I try retrieving the value, I have to first enter the password. I want to enter the password in the macro itself, i.e., while opening the file.
2. secondly, the name of the file "abc100.xls" is stored in a variable. I want to extract only the numeric part of it(100), as this forms part of my password. My password contains a few characters followed by the file number(i.e., "n0100") in my case. Please help.

[Bob Phillips]

Open has a password property that you can use.

.

[martha555]

hi,
thanx for the response, but can u give an example.

[Steiner]

You could try this which should extract the password, open the file, get the value in A1 and close the file again:


Option Explicit
Sub OpenPwd(sPath As String, sFile As String)
Dim Wb As Workbook, sTemp As String, iPos As Integer
Dim sNumber As String, sPassword As String
'Split filename to get number
'1. remove the .xls
iPos = InStr(sFile, ".xls")
If iPos > 0 Then
sTemp = Left(sFile, iPos - 1)
Else
sTemp = sFile
End If
'2. grab from right to until until 1st non-numeric character
iPos = 1
Do Until Not IsNumeric(Right(sTemp, iPos)) Or iPos > Len(sTemp)
sNumber = Right(sTemp, iPos)
iPos = iPos + 1
Loop
'3. create the password
sPassword = "n0" & sNumber
'4. open the file
Set Wb = Workbooks.Open(sPath & sFile, password:=sPassword)
'5. do something
MsgBox Wb.Worksheets(1).Range("A1").Value
'6. clean up
Wb.Close False
Set Wb = Nothing
End Sub


Call it this way:

openpwd "e:\test\","abc100.xls"


Daniel