Printable View
I am using MS Access to open a Excel file which is password protected.
I have the code that gives me the window handle value. Using the window handle value and SendMessage, I want either send a message that presses the X on the "Password" dialog box or alternatively place a dummy password in the password textbox as the mouse cursor defaults to or alternatively press the OK or Cancel buttons on the "Password" dialog box. Any assistance would be appreciated.
Here's the code I have so far.
Code:Const SW_SHOWNORMAL = 1
Const SW_MAXIMIZE = 3
Const WM_SYSKEYDOWN = &H104
Const WM_SETTEXT As Long = &HC
Const WM_KEYDOWN As Integer = &H100
Const WM_KEYUP As Integer = &H101
Const GW_CHILD = 5
Const WM_SHIFT = &H10 'SHIFT
Const WM_C = &H43 'Capital C
Const WM_R = &H52 'Capital R
Const WM_O = &H6F 'Capital O
Const WM_CLOSE = &H10
Const dlgOK = &H1
Const dlgCANCEL = &H2
Const dlgABORT = &H3
Const dlgRETRY = &H4
Const dlgIGNORE = &H5
Const dlgYES = &H6
Const dlgNO = &H7
Const WM_COMMAND = &H111
Dim strFile As String
Dim strLine1 As String
Dim strLine2 As String
Dim strLine3 As String
Dim strVBSFile As String
Dim wsh As Object
Dim waitOnReturn As Boolean: waitOnReturn = False
Dim windowStyle As Integer: windowStyle = 1
Dim FSO As Object
Dim objTextFile As Object
Dim WinWnd As Long
Dim WinWnd2 As Long
Dim strWindowTitle As String
strWindowTitle = Space(260)
Set FSO = CreateObject("Scripting.FileSystemObject")
strVBSFile = "Open MS Excel File.vbs"
strFile = Chr(34) & strFilePath & strVBSFile & Chr(34)
strLine1 = "Set objExcel = CreateObject(" & Chr(34) & "Excel.Application" & Chr(34) & ")"
strLine2 = "objExcel.Visible = True"
strLine3 = "objExcel.Workbooks.Open " & Chr(34) & strFilePath & strExcelFile & Chr(34) & Chr(44) & "True" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "True"
Call DeleteSpecificFile(strFilePath & strVBSFile)
Set objTextFile = FSO.CreateTextFile(strFilePath & strVBSFile)
With objTextFile
.WriteLine strLine1
.WriteLine strLine2
.WriteLine strLine3
.Close
End With
Set FSO = Nothing
Set objTextFile = Nothing
Set wsh = VBA.CreateObject("WScript.Shell")
wsh.Run strFile, windowStyle, waitOnReturn
Set wsh = Nothing
Pause (3)
WinWnd = FindWindow(vbNullString, "Password")
If WinWnd <> 0 Then
Call SendMessage(WinWnd, WM_COMMAND, WM_CLOSE, 0)
' Call SendMessage(WinWnd, WM_CLOSE, 0, 0)
End If
And does this code work?
No.Quote:
Originally Posted by VictorN
Ideally I want the Read Only button to be pressed as this opens the Excel file. The R in Read Only is underlined.
When I manually press the Read Only button everything is as I want it. Alternatively if I press ALT+R this also works.
When you shell out to start Excel, you are essentially opening the Excel file like it would be using Windows explorer. Well, the process isn't exactly the same, but the end result is the same - you end up with a regular Excel application that from this point on needs to be manipulated using UI automation techniques (as you've found out from trying to close the password dialog with findwindow).
To avoid having to deal with any of the UI automation headache, instead there is another approach. That is, to open the file in Excel using COM automation.
You can look up the exact syntax but it would be something like:
VBA.CreateObject("Excel.Application")
The problem is that even when I use CreateObject, the password dialog appears. Hence I need code that will put the focus on the 'Read Only' button and press or as I have found that if pressing the Cancel button also does the job. Any help in that would be great.
I just need code that take the focus away from the password text box to the Cancel button Nad then code that will press the Cancel button, that should do the trick for what I want. Thanks in advance.