Macro Exiting External Before Finished

[mkron]

Hello,

I created a macro to open up a file location, move to the search box, paste text that was entered through an input box, then select the file and open it (a pdf). I am having issues where it doesn't actually enter the Windows Explorer when it opens it and ends up pasting the MO text in the excel sheet. It works about half the time. The code is pasted below.

Thanks for your help in advance!
-MK

Sub MO_Lookup()


MO = InputBox("Enter the MO: ")
    
a = 0
    
    Do Until a = 1
        Shell "explorer.exe" & " " & "C:\Users\name\Documents\MOs", vbNormalFocus
        Application.Wait (Now + 0.00001)
        Application.SendKeys "{TAB}"
        Application.Wait (Now + 0.00001)
        Application.SendKeys "{TAB}"
        Application.Wait (Now + 0.00001)
        Application.SendKeys "{TAB}"
        Application.Wait (Now + 0.00001)
        Application.SendKeys MO ' 
        Application.Wait (Now + 0.00001)
        Application.SendKeys "~"
        Application.SendKeys "{DOWN}" 
        Application.SendKeys "{UP}"
        Application.SendKeys "~"
        a = 1
    Loop
    


End Sub

 

[Russell Hauf]

I see that you haven't received a response yet. Might I suggest that you explain what you are trying to do, rather than what isn't working? I'll bet you'll get some help if so!

 

[mkron]

The macro is supposed to open up a Windows Explorer (specifically the location "C:\Users\name\Documents\MOs") and then move to the search box (using tabs) and paste the variable "MO", then it tabs down to the results, and arrows down and up to select the file, and 'hits' the enter button to open up the file.

It works about half the time and the other half it opens up Windows Explorer behind excel and pastes "MO" someone in the excel document

Thanks,
MK

 

[Russell Hauf]

So what might the file name look like? I believe there is a better way to do this, but need more specifics.

 

[mkron]

Typically it is a N9G95.pdf however, sometimes I will have 2-5 orders with that MO so I will have N9G95-0001.pdf, N9G95-0002.pdf, etc. I just omit the -0001 on MOs that don't have multiple items.

If it makes it easier, I can start adding the "-000#" to all of my file names, I have been thinking about it for a little while now.

Also a note would be that there are 2 different sub folders the file could be in:
"C:\Users\name\Documents\MOs\Current"
"C:\Users\name\Documents\MOs\Completed\05-17" (month completed)

 

[Russell Hauf]

I'm so sorry. I wanted to wrap this up in a nice bow for you...or hoped that someone else might. But I suggest you look into the FileSystemObject...you can search through any/all subfolders for a file that meets your criteria. This should be much more robust than using SendKeys. I just don't have the time right now - the paying job and the home job are taking a lot of time right now. I will still try to post something in the next few days if I get time...