[Urgent] Automating the saving of OLEs to external folder via SendKeys

Dim strPath As String
Dim strExt As String

strExt =".doc"
strPath = "f:\whoemr\data\attach\subtblphysiciannote\"

    DoCmd.GoToRecord , , acFirst
    Do
       On Error Resume Next
       docmd.GoToRecord,,acNext
       If err = 2105 Then
Exit Do     'end of table
       
            ((here I need to start a loop that waits to see that I made it to the end of table))
    me!PhysicianLink = strPath & me!uniqueID & strExt
            ((here I need to know how to launch the OLE field from VBA, as if it were double clicked to start the external application))
   sSleep 5000 'giving the system enough time to parse the document
   Runcommand acCmdSaveAs  'reference: https://www.experts-exchange.com/questions/21540911/Urgent-Automating-the-saving-of-OLEs-to-external-folder-via-SendKeys.html
           ((here I need to sendkeys a string, AND the data from a field. Essentially I would "like" to [sendkeys(strPath & me!UniqueID & strExt)], but it has not proven very easy. The end result will put the string in the filename field of the save file dialog box))
   sendkeys("{enter}")'saves the file
   ssleep 2000
   Docmd.Quit 'closes microsoft word
   ssleep  2000
DoCmd.GoToRecord , , acNext
Loop

I hate it when I'm editing the comment and I hit the wrong key!!!   Ignore the above post, it's incomplete.  All of your sendkey calls are unneccessary since there are commands to handle them.

I anxiously await to see this :) Thanks for coming to my aid (again!)

I got down to the part ((here I need to know how to launch the OLE field from VBA, as if it were double clicked to start the external application)) and realized that what you are trying to do is clever but unfortunately won't work.  As soon as you open the external application, it has control and Access code will not control the other application.  You can control the other application with automation or with sendkeys but if you don't know which application has opened automation won't work and sendkeys could be very disasterous (as in HUGE problem like wiping out large pieces of your hard drive.)

But what you are trying to do has gotten me thinking.  You said all your imbeded objects are either doc or pdf files.  Is that correct?  I am assuming the frame is bound? If it is unbound, what is the ole class (on the data tab) for the different type of files.  With that info, I can play with it over the weekend and see if I can come up with a way to get the data out in a usable way.

I still think a quick way to improve performance drastically would be to export the imbeded objects to another database BE and link to it.  It would split the calls.  In fact, if you can objects to several databases, that would be even better.  You could split them by patient id and have code to look into the correct db.  Or you can split them by DOS year since the docs would rarely go back several years -- this would in effect create an active archive.  You could even archive the oldest DOS to cd.  But for sure, you want to create a linking scheme for all future storage.

I left the code I was working on below since I noted references for changes that you may be interested in.

 Dim strPath As String
Dim strExt As String

strExt =".doc"
strPath = "f:\whoemr\data\attach\subtblphysiciannote\"

    DoCmd.GoToRecord , , acFirst
    Do
       If Err Then MsgBox "Error: "; Err, Err.Description : Exit Sub
       me!PhysicianLink = strPath & me!uniqueID & strExt
            ((here I need to know how to launch the OLE field from VBA, as if it were double clicked to start the external application))
   sSleep 5000 'giving the system enough time to parse the document
   Runcommand acCmdSaveAs  'reference: https://www.experts-exchange.com/questions/21540911/Urgent-Automating-the-saving-of-OLEs-to-external-folder-via-SendKeys.html
           ((here I need to sendkeys a string, AND the data from a field. Essentially I would "like" to [sendkeys(strPath & me!UniqueID & strExt)], but it has not proven very easy. The end result will put the string in the filename field of the save file dialog box))
   sendkeys("{enter}")'saves the file
   ssleep 2000
   Docmd.Quit 'closes microsoft word
   ssleep  2000
DoCmd.GoToRecord , , acNext
       On Error Resume Next
       docmd.GoToRecord,,acNext
   Loop Until  err = 2105     'end of table

Well, the primary reason I am trying to get the files out of the db is size.

OLE object: 8000k
standalone file: 50k

So moving it to another DB, while getting out of the main DB and increasing speed, will still be problematic for reasons of size.

I already have a functional linking scheme, but cant switch to it until I have all of the old material moved over. I think this may be a case of manual labor, if you are certain that the sendkeys will not function regardless of active window.

edit: Wait a second.... .doc files can be opened in a frame inside access. Is there any way to issue the sendkeys on an activated OLE frame?

Sendkeys inputs to the keyboard buffer so whatever application has the focus will receive the sendkeys.  It will work when Word or Adobe has the focus but my concern is how do you know which one has the focus.  Also this code will be running a long time.  How can you be certain that a background app doen't take focus and receive the send keys?  For the sendkeys to work, you would have to determine if the ole object is a doc or pdf file to send the correct sendkeys combination (unless word and adobe happen to use exactly the same sendkeys combination.)  You would also want to make sure the background apps are all turned off.  The risks are some ole files may be lost or if a background app took the focus, deleting other important items on your hard disk.  Some programmers never use sendkeys.  I only use sendkeys for very short keystroke combinations to an application that I'm certain will have the focus for at least a few microseconds.  I never use sendkeys in a loop.  If I were doing it, if I could identify the type of OLE file, work on a backup of the database on a computer phisically disconnected from any network and I didn't care about lossing anything from the computer's local drives, I would consider it.  So it is your call.  I can help you with the coding if you want to give it a shot.  

As I am writing this, I thought of another way of doing it which has the potential of doing it safer -- using an application called hotkey master at http://www.hotkeymaster.com/.  It simulates keyboard and mouse control (so it can simulate the double click you want)  but it can identify what window has the focus.  It has full scripting capability.  You can try it for 30 days and it will set you back the budget busting sum of $5 to register.  If I used hotkey master for this application, I would still do it on a copy of the db on a stand alone computer.

I do want to give it a shot.

For the 1st run, everything will be word files... however, Word and Acrobat use the same keystroke combinations for everything, so The script is bulletproof, pending another application does not take the focus.

So yes, if you can help me with my script up there, I would be very thankful.

and yes, I will be running this on a backup DB, on a virtualized PC.

Here's the code.  I tested the best I can with different data.  It should be fun to watch although it should take a very long time.  Make sure the computer you run it on has a lot of memory and disk space.  You don't need the PhysicianLink field since you can create the link from the uniqueID field (my demo db at: http://www.nosuffering.com/Nelson/ImageDemo.zip will show you how to link to external files based on a uniqueID field).  Eliminating this field will reduce the size of and speed up your db.

Dim strPath = "f:\whoemr\data\attach\subtblphysiciannote\"
Dim strSendKeys = "%fv" & strPath & & me!UniqueID & ".doc{Enter}%{F$}"

DoCmd.GoToRecord , , acFirst
Do
   If Err Then MsgBox "Error: "; Err, Err.Description : Exit Sub

   me!PhysicianLink = strPath & me!uniqueID & ".doc"  'NOTE: You really don't need this field since you can create it from uniqueID
   PhysicianNote.SetFocus
   SendKeys strSendKeys                                 'send the string to save as, close once the native app is open
   RunCommand acCmdOLEObjectDefaultVerb   'open native application
 
   On Error Resume Next
   docmd.GoToRecord,,acNext
Loop Until  err = 2105     'end of records

Well, I had to modify the code, as it would not do anything in its current state. Here is what I have thus far.

Private Sub Form_Open(Cancel As Integer)
Dim strPath As String
Dim strSendKeys As String
strPath = "f:\whoemr\data\attach\subtblphysiciannote\"
strSendKeys = "%fa" & Me!UniqueID & ".doc{Enter}%{F4}"

DoCmd.GoToRecord , , acFirst
Do
   PhysicianNote.SetFocus
   SendKeys strSendKeys                                 'send the string to save as, close once the native app is open
   RunCommand acCmdOLEObjectDefaultVerb   'open native application
 
   On Error Resume Next
   DoCmd.GoToRecord , , acNext
Loop Until Err = 2105      'end of records

End Sub


And what it is doing is opening the attachments, one by one, and closing them. It recognizes the alt-f4... but it seems to be ignoring the rest of the sendkeys string.

I have a pause variable called ssleep which halts the code from running, and allows the system to run what has been sent to it. Its measured in milliseconds.

I tried to do a

SendKeys ("%f")
ssleep 4000
SendKeys ("a")
ssleep 4000
SendKeys strsendkeys2
ssleep 4000
sendkeys ("{enter}")
ssleep 4000
sendkeys ("%{F4}")
ssleep 4000



however, it never even seemed to perform the 1st command ("%f").

This is fairly frustrating :(.


Any ideas?

The support.microsoft.com one worked great! I combined the loop code you created for the prior method with the word document creation method from there, and it pulled all the records out!

The only problem now will do with the mixed OLE databases, where some are word docs and some are PDFs. But this saved a MAJOR chunk of time, and allowed me to get my databse up and running.

Thanks for all your help! I will post the code here tomorrow that worked, for future reference.

Fantastik!!!

<<The only problem now will do with the mixed OLE databases>>
That's even a tougher nut to crack.  I would try using the code you have on a pdf embeded file and see if it creates an error message you can trap and switch to Adobe.  Also single step through the code and see if you can identify something that would say, "this is not a Word doc."

If you get that, your next problem is extacting the pdf file.  Adobe reader does not support automation:
https://www.experts-exchange.com/questions/20971514/Is-Access-VBA-output-to-a-PDF-file-still-this-complicated.html#10954891
But you could use the sendkeys method with it.  

Remember, if you end up using sendkeys, use %fx instead of %{F4}