wait and shell

[maxbre]

Given my Sub “open_dir” which is opening a new explorer window by using the Shell:

'------------------------------
Public Sub open_dir(name_directory As Variant)

full_path = Application.CurrentProject.path & "\" & name_directory

Shell "c:windows\explorer.exe """ & full_path & "", vbNormalFocus

End Sub


'------------------------------

I want to take control of the VBA “code flow” inside of the Sub named “another_sub” which has a call to the sub “open_dir”

'------------------
Sub another_sub()

‘…some vba code here

Call open_dir(name_directory)

‘…some other vba code here

End sub

'-----------------

Now, I would like to wait until the shell has finished its work before going through the next VBA statement; I know about this link http://www.mvps.org/access/api/api0004.htm but for some reasons I’m not able to make it working properly (or at least I do not understand how to, because I’m very new in programming);

I put the API code in a new module and then I’ve been trying to call it by something like:

'---------------------
full_path = Application.CurrentProject.path & "\" & name_directory
shell_string = "c:windows\explorer.exe """ & full_path & ""
Call ShellWait("shell_string", vbNormalFocus)
'------------------------

(and many other attempts I'm not posting here...)

I guess it’s a problem about the string of the path or something similar….

Any hint or help for this, please?


Sorry for such a trivial question but I’m really stuck here since hours now….

maxbre

[boblarson]

What exactly are you attempting to do? Since you already have said what you have used, don't refer back to that, but instead tell us what you expect the Windows Explorer to do. Why are you opening Windows Explorer and what isn't working exactly? It looks like it should open the Explorer fine and that would mean that it has completed its Shell okay and would go to the next bit of code.

[maxbre]

thanks for your reply

it's about a problem of "user interaction control" of the vba code flow
I'll try to explain myself

what I want to do is summarised by following points:
1- to open an explorer window (for interaction with the user);
2- to wait to complete the task of opening the explorer window;
3- to pass to next vba statement in the sub (a message box for further interaction with the user);

from the user point of view, what is actually happening (and I would like to avoid) is that point 3 is run to the end before the end of point 2 (i.e point 2 is completed before point 3); so I want that point 2 (opening the explorer window) is carried out to the end before point 2 is going to be executed (i.e. point 3 run after point 2)

I hope this clarifies, a bit at least...

thank you

max

[boblarson]

What is the user using the explorer window for? That is the important part of the question. Are you just opening the explorer window and that's it, or what is supposed to happen with it? What are they doing and does it require them to do that part before you move on to the next part of the code.

[maxbre]

the user is supposed to use the explorer window to upload a file in a specific directory (which path is already stated by previous user inputs);

now, what is confusing the user is that the opening of the explorer window is performed after the completion of the next part of the code (which is a message box asking for another type of user action);

given this situation the user is left in the condition to forget the first input which is fundamental for the accomplishment of the correct procedure (which is not strictly regarding the vba code syntax)

thanks a lot for your time

[boblarson]

Sounds to me like you might be able to just use a Windows Open/Save dialog instead. How are they uploading the specific file?

[maxbre]

yes, bob you are quite right...

in principle the user is in the condition to use Windows Open/Save dialog BUT because at that point is "in the middle" of a form filling procedure I think it's quite distracting to swap into a different environment and then come back to access (at least I think so...)

I would like to "smooth" the procedure of uploading the file because it's crucial the correct accomplishment of this task in order to be successful in the next steps

in this sense, if you want, an ideal tool would be a "file browser" within access to upload the file into a specific path (already stored in a variable)

can you provide me with this feature?

thanks

[boblarson]

yes, bob you are quite right...

in principle the user is in the condition to use Windows Open/Save dialog BUT because at that point is "in the middle" of a form filling procedure I think it's quite distracting to swap into a different environment and then come back to access

But isn't that what you're doing with the Windows Explorer? It sure is.

Just pop up a dialog to browse to the file to select. It is very easy. I have a meeting coming up so I may not be able to help right away but look into using the FileDialog.

[maxbre]

exactly!
and that's why I want to find a way to wait for the completion of the opening of the window explorer before executing the next vba statement by means of the ShellWait() Sub

anyway, to cut off this neverending story now my question is:
is there a way to put to work the ShellWait() Sub given my code snippet posted in my opening of this long thread?

I would like to have an answer yes/not

thank you again bob for your help and the valuable time you dedicated to me (hopefully not wasted)

[boblarson]

The answer is NO, you cannot use Shell And Wait for what you are looking to do. Post the code which you are trying to use and we'll see how we can use the File Dialog instead.

[maxbre]

sorry bob for my slow reply but I've been away for a couple of days...

here is the code snippet I’ve been working on;
it’s supposed to be attached to a click event button in a form (in order to save user inputs); as I’ve been trying to explain you earlier the problem is that before completing the opening of the explorer window by mean of the “Call open_dir(path_dir)” the vba code execution is moving to the next msgbox asking for another user input action (add a new record);

do you have any suggestion to improve this code?

Thanks



Sub save_form_Click()

‘….some code here (not posted)….


If MsgBox("Do you want to upload a file?", vbYesNo, "file") = vbYes Then
path_dir = DLookup("path", "pratiche", "[id_pratica]='" & IDPRT & "'")
Call open_dir(path_dir)
End If

If MsgBox("Do you want to add a new record?", vbYesNo, "continua") = vbYes Then
Call ClearForm
Else
DoCmd.Close
End If

End Sub

Public Sub open_dir(path_dir As Variant)
full_path = Application.CurrentProject.path & "\" & path_dir
Shell "c:windows\explorer.exe """ & full_path & "", vbNormalFocus
End Sub

Private Sub ClearForm()
Me.RecordSource = ""
Me.id_recors.ControlSource = ""
‘…here some other code to clear each control source inside the form (not posted)….
End Sub

[boblarson]

Okay, here's your revised code. You can delete the Open_dir procedure. You would use strSelectedFile for the path to the selected file (as you should be able to make out in the code below):

Private Sub save_form_Click()
    Dim fd As Object
    Dim strSelectedFile
 
    '….some code here (not posted)….
 
    If MsgBox("Do you want to upload a file?", vbYesNo, "file") = vbYes Then
        Set fd = Application.FileDialog(3)
        path_dir = DLookup("path", "pratiche", "[id_pratica]='" & IDPRT & "'")
 
        With fd
            .Filters.Clear
            .InitialFileName = path_dir
            .Title = "Select File to Upload..."
            .AllowMultiSelect = False
            .Show
        End With
 
        If fd.SelectedItems.Count <> 0 Then
            strSelectedFile = fd.SelectedItems(1)
        End If
    End If
 
    If MsgBox("Do you want to add a new record?", vbYesNo, "continua") = vbYes Then
        Call ClearForm
    Else
        DoCmd.Close
    End If
End Sub

[maxbre]

thanks bob, it's perfect

now I just need to copy the file in the given dir and the work is done: do you have any suggestion for this?
by the way is it possible to allow for multiple file selection ?


thanks a lot for your patience, I've learned a lot during this session

m

[boblarson]

Where are you wanting it copied to?

[maxbre]

I modified part o f the code like this (in order to select multiple files);
the destination (where to copy) is taken from a field in the form;
now is just missing the vba part to copy the files (by mean of FSO?)

'-----------------------------------
Set fd = Application.FileDialog(3)

path_dir = DLookup("path", "pratiche", "[id_pratica]='" & IDPRT & "'")

Me!path.Value = path_dir

With fd
.Filters.Clear
.InitialFileName = "d:\*.*"
.Title = "Pick up files to copy"
.AllowMultiSelect = True
.Show
End With

'---------------------------------