Closed workbook

[Jac Tremblay]

Hi,
I have tried the macros from John Walkenbach and others and they work fine
as long as the Excel file is not password protected.

Is it possible to specify the password as the connection get oppened?
' **************************************************
Private Function GetValue(path, file, sheet, ref)
' From John Walkenbach
' http://j-walk.com/ss/excel/tips/tip82.htm
' Retrieves a value from a closed workbook.
Dim arg As String
' Make sure the file exists.
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument.
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro.
GetValue = ExecuteExcel4Macro(arg)
End Function
' **************************************************
Thanks

 

[Jac Tremblay]

Hi Jac,
I have found a solution to your problem. Just add this code
(Application.SendKeys)to the GetValue function:
' **************************************************
' Create the argument.
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Entrer the read password for the workbook.
Application.SendKeys "+Jac" ' <<-- Modify to your needs.
Application.SendKeys "{TAB}"
Application.SendKeys "{ENTER}"
' Execute an XLM macro.
GetValue = ExecuteExcel4Macro(arg)
End Function
' **************************************************
This option has his drawbacks:
1- The SendKeys method is a very dangerous operation and can cause many
problems with the users.
2- The application.ScreenUpdating = False will not be effective for the
password dialog which has to be showed every time.
Hope this helps the community.