Closed workbook

J

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top