VBA to prevent keyboard input when a package object (XML) is read into an ADODB stream?

Question

I am developing an application that opens and reads an XML document previously built into a PowerPoint presentation, or a Word document. To read this object ( xmlFile as Object ), I have to do:

xmlFile.OLEFormat.DoVerb 1

This opens the package object, and I have another routine that receives an open instance of Notepad.exe and reads its contents into the ADODB stream.

An example of this procedure is available in Google Docs:

XML_Test.pptm .

During this process, there are a few seconds of a window where Notepad.exe receives focus, and an inadvertent key press can cause unwanted results or an error while reading XML data.

I am looking for one of two things:

• Or a method to prevent unintentional user input (via keyboard / mouse / etc.) during this operation. What is preferable is that it does not control the user machine, such as the MouseKeyboardTest routine, below. Or,
• The best way to extract XML data into a string variable.

For # 1: this is the function I found that I use. I am wary of such control over the user system. ## Are there any other methods that I could use? ##

 Private Declare Function BlockInput Lib "USER32.dll" (ByVal fBlockIt As Long) As Long Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sub MouseKeyboardTest() 'both keyboard and mouse blocked BlockInput True ' Turns off Keyboard and Mouse ' Routine goes here Sleep 5000 ' Optional coding BlockInput False ' Turns on Keyboard and Mouse End Sub 

For # 2: Some background, but the problem is the inability to reliably retrieve the embedded object using any method other than DoVerb 1 . Since I am dealing with an unsaved document in an application (Notepad) that is immune to my VBA skill, this is apparently the only way to do this. Full text about this here:

Extract an OLEObject (XML document) from PowerPoint VBA

Answer 1

I understand that you control how an XML file is embedded in a PowerPoint presentation in the first place. Here I do not quite understand why you decided to save the necessary data as the contents of the embedded object.

Of course, the task of obtaining this content is not a piece of cake. In fact, while there is no (simple or even moderately complex) way to call QueryInterface and use IPersist* interfaces from VBA, there is only one way to get to the contents of the embedded object. This method includes the following steps:

• Activate the embedded object. You used OLEFormat.DoVerb 1 for this. A better way would be to call OLEFormat.Activate , but that doesn't matter for your specific problem.
• Use the built-in object programming model to perform useful operations such as retrieving content, saving, or more. Notepad.exe does not provide such a programming model, and you have resorted to WinAPI , which is the best choice.

Unfortunately, your current approach has at least 2 drawbacks:

• The one that you identified in the question (activation of Notepad.exe , leading to the possibility of user intervention).
• If the user has a default program to open .txt files other than Notepad.exe , your approach is doomed.

If you have control over how the embedded object is embedded, it is best to use XML data storage in some property of the Shape object. I would use Shape.AlternativeText (very simple to use; should not be used if you export .pptm to HTML or have some other script where AlternativeText matters) or Shape.Tags (this option is probably the most semantically correct for this tasks).

Answer 2

As you correctly guessed in the comment above, that diverting attention from a notebook will solve your problem. This code does just that.

LOGIC

A. Scroll the shape and get its name. In your scenario, it will be something like Chart Meta XML_fbc9775a-19ea-.txt

B. Use APIs such as FindWindow , GetWindowTextLength , GetWindow etc. to get the notepad window handle using a partial header .

S Use ShowWindow API to minimize window.

Code (tested in VBA-Powerpoint)

Paste this code into the module in the above PPTM

 Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function GetWindowText Lib "User32" Alias "GetWindowTextA" _ (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long Private Declare Function GetWindowTextLength Lib "User32" Alias _ "GetWindowTextLengthA" (ByVal hWnd As Long) As Long Private Declare Function GetWindow Lib "User32" (ByVal hWnd As Long, _ ByVal wCmd As Long) As Long Private Declare Function ShowWindow Lib "User32" (ByVal hWnd As Long, _ ByVal nCmdShow As Long) As Long Private Const GW_HWNDNEXT = 2 Private Const SW_SHOWMINIMIZED = 2 Sub Sample() Dim shp As Shape Dim winName As String Dim Ret As Long For Each shp In ActivePresentation.Slides(1).Shapes If shp.Type = msoEmbeddedOLEObject Then winName = shp.Name shp.OLEFormat.Activate Exit For End If Next If winName <> "" Then Wait 1 If GetHwndFromCaption(Ret, Replace(winName, ".txt", "")) = True Then Call ShowWindow(Ret, SW_SHOWMINIMIZED) Else MsgBox "Window not found!", vbOKOnly + vbExclamation End If End If End Sub Private Function GetHwndFromCaption(ByRef lWnd As Long, ByVal sCaption As String) As Boolean Dim Ret As Long Dim sStr As String GetHwndFromCaption = False Ret = FindWindow(vbNullString, vbNullString) Do While Ret <> 0 sStr = String(GetWindowTextLength(Ret) + 1, Chr$(0)) GetWindowText Ret, sStr, Len(sStr) sStr = Left$(sStr, Len(sStr) - 1) If InStr(1, sStr, sCaption) > 0 Then GetHwndFromCaption = True lWnd = Ret Exit Do End If Ret = GetWindow(Ret, GW_HWNDNEXT) Loop End Function Private Sub Wait(ByVal nSec As Long) nSec = nSec + Timer While nSec > Timer DoEvents Wend End Sub 

Answer 3

I don’t think that blocking the user is the right approach,

If you should use the contents of the notepad window, I would suggest using the SendKeys method to send this combination:

SendKeys("^A^C")

Which is equivalent to Select All and Copy,

And then you can continue to work offline in the clipboard without fear of interference by pressing keys.

Answer 4

My approach, as suggested by Sid, was to find a way to minimize Notepad.exe. Since I already found a way to get this object and close it, I decided that it should not be so difficult.

I add them:

 Public Declare Function _ ShowWindow& Lib "user32" (ByVal hwnd As Long, _ ByVal ncmdshow As Long) Public Const SW_MINIMIZE = 6 

And then, in the FindNotepad function, right before the Exit Function (so after Notepad is found), I minimize the window with

 ShowWindow TopWnd, SW_MINIMIZE