ChangeFileOpenDirectory

[Ed]

I have installations of the identical VBA program on 20 different computers.
I use the following command with some frequency.

ChangeFileOpenDirectory newpath
With Dialogs(wdDialogFileOpen)
.name = "*.doc"
CloseNum = .Show
End With

The command works perfectly on 19. One the 20th it refuses to work. The
MyDocuments folder consistently opens when the section of code is
encountered.

I have doubled the "ChangeFileOpenDirectory newpath" command (I found that
suggestion on a board) but that doesn't work either.

When I step through (debugging) the vba code it works fine, so I know that
the connections are intact.

Any thoughts?

Ed (in Virginia)

 

[Russ]

Hey Ed,
Helmut's suggestion seems sufficient.
However, I'm curious.
If you put the line:
MsgBox "<<" & newpath & ">>"
Just before the ChangeFileOpenDirectory line in the recalcitrant computer,
does that message popup show the path string you expect between the chevrons
and/or is that enough time to allow the path to resolve itself before the
FileOpen dialog appears?

 

[Ed]

Thanks Helmut and Russ,

I 'forced' a fix, and it is working right now. The recalcitrant computer
was also the most powerful one on line and it just seemed (based on other
'errors' it generated in processing VBA code) that it was going 'too fast.'
(Is that possible??) I say this because whenever I stepped through the
ChangeFileOpenDirectory process in VBA, it always worked. When I 'ran' the
code, it frequently did not.

So I added a 'pause' in this fashion, just before the
ChangeFileOpenDirectory command.

For n = 1 to 500000
n = n + 1
Next

That added about a tenth of a second to the process, but seems to have cured
the problem. For today at least. But as Helmut says, "Who knows about
tomorrow."

Ed (in Virginia)

 

[Helmut Weber]

Hi Ed,

this one is working for me today,
but who knows about tomorrow?

Sub OpenFolder()
Dim f1 As String
Dim f2 As String
Dim f3 As String
f1 = "c:\test\word1\"
f2 = "c:\test\word2\"
f3 = "c:\test\word3\"
ChangeFileOpenDirectory f3
With Dialogs(wdDialogFileOpen)
.Name = f3 & "*.doc" ' ! Path & name
.Show
End With
End Sub



--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Ed]

Russ,

You have hit the nail on the head. There was not enough time for the
recalcitrant computer to resolve itself from the call to the sub-routine
(module). This happened in several other situations where sub-routines were
called. I found myself having to put 1/10th second counters in a half dozen
(so far) locations to insure proper processing. Any idea why this is allowed
to happen in a program such as VBA??

Ed (in Virginia)

 

[Russ]

Ed,
I'm unclear on what originally caused your problem. Was it the time needed
to build up and store the path string into the newpath variable or the
ChangeFileOpenDirectory syntax command line?
You said you put a delay before the command, so I'm guessing it was the
method you used to build up the newpath path.

VBA should inherently wait when calling one it's own subroutines. But if you
go outside of VBA by using a Windows shell or script routine, VBA will
happily initiate the shell/script stuff, but won't wait for any return
values.

Here is some information from a past message thread on how to make VBA wait
while going outside of VBA:
=======Quote
There are other ways to move files using other scripting languages, too.
In a dos batch file, you could use xxcopy ( a variation of xcopy ).
<http://www.xxcopy.com/xxcopy17.htm>

You could call a dos batch file from VBA.
Helmut Weber mentioned this:
<http://vb.mvps.org/samples/project.asp?id=Shell32>



Or this xShell code works in Word97, too:

Put this in Declarations section at the top of your VBA code module so that
all subroutines can take advantage of the 'wait for shell' code.

Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

'Add this code as one of the regular subroutines.

Public Function ShellX( _
ByVal PathName As String, _
Optional ByVal WindowStyle As Integer = vbMinimizedFocus, _
Optional ByVal Events As Boolean = True _
) As Long

'Declarations:
Const STILL_ACTIVE = &H103&
Const PROCESS_QUERY_INFORMATION = &H400&
Dim ProcId As Long
Dim ProcHnd As Long

'Get process-handle:
ProcId = Shell(PathName, WindowStyle)
ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

'wait for process end:
Do
If Events Then DoEvents
GetExitCodeProcess ProcHnd, ShellX
Loop While ShellX = STILL_ACTIVE

'clean up:
CloseHandle ProcHnd
End Function
++++++++++++++++
'And call it like this:

Dim x As Long
Dim strDosBatchFullPath As String
strDosBatchFullPath = ³C:\...myDosBatchFile.bat²
System.Cursor = wdCursorWait
x = ShellX(Chr(34) & strDosBatchFullPath & Chr(34))
============UnQuote

 

[Ed]

Russ,

Actually it turned out to be several things in the program that 'speed'
seemed to affect. All involved sub-routines and screen redrawings. And,
despite the fact that VBA should inherently wait while calling its own
sub-rountines, I can prove conclusively that it does not (or at least it
does not wait for the 'consequence' of the the sub-routine to fully
occur--in this case, a screen refresh with new text inserted or text
removed).

I do a lot of string functions manipulating the on-screen text. My
hypothesis is that while the sub-routine attempts to change the value of
certain on-screen text, that value is not actually resolved on screen by
the time the next steps are taken.

Without the pause (I will call it a speed bump) that I have built in,
the routine appears to proceed with the 'original' (pre-subroutine)
on-screen text. (I say 'appears' because it really isn't possible for me to
test for this. If I 'step' through the process, there is plenty of time for
the proper text to be present on the screen by the time the next step is
invoked.

So I have stuck a speed-bump at the end of each subroutine which
involves an on-screen value change. (i.e., something that involved changing
range or selection text (as opposed to a variable value change). And that
has cured the problem.

There is a slight delay for the speed-bump, but the computer is so fast,
it isn't noticeable. When I take out even one speedbump, the program reverts
to its old behavior.

Strange, but true. Do you have any other suggestions for testing the
hypothesis? (Actually, I just thought of one as I was writing this tome. Put
in a screen fresh command! I will try that.)

(By the way, I do recognize that the CFOD issue does not involve string
manipulation, but I do change the file open directory via a sub-routine and
the speed bump cured that problem as well. There may be something different
going on there, but I am accepting the fix without questioning it.)

Ed (in Virginia)

 

[Ed]

Okay, I just tested with 'ScreenRefresh' in lieu of the speed bump. It
didn't work. I must be something else, but the speed bump definitely works.
Weird.
--Ed

 

[Shardwell Stradtlater]

Thanks for the tip. I've been dealing with this bug, er, problem, er, issue since I switched to Word 2003. I repeated the ChangeFileOpenDirectory and the doggone thing worked. Then it stopped, so I repeated it again. Now I'm up to five iterations and it works most of the time. Thanks!