Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / November 2007

Tip: Looking for answers? Try searching our database.

ChangeFileOpenDirectory

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ed - 21 Nov 2007 11:00 GMT
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)
Helmut Weber - 21 Nov 2007 16:55 GMT
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
Russ - 21 Nov 2007 21:54 GMT
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?

> Hi Ed,
>
[quoted text clipped - 44 lines]
>>
>> Ed (in Virginia)

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Ed - 25 Nov 2007 23:31 GMT
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)

> Hey Ed,
> Helmut's suggestion seems sufficient.
[quoted text clipped - 58 lines]
>>>
>>> Ed (in Virginia)
Russ - 26 Nov 2007 03:12 GMT
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

> Russ,
>
[quoted text clipped - 69 lines]
>>>>
>>>> Ed (in Virginia)

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Ed - 26 Nov 2007 10:11 GMT
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,
> I'm unclear on what originally caused your problem. Was it the time needed
[quoted text clipped - 151 lines]
>>>>>
>>>>> Ed (in Virginia)
Ed - 26 Nov 2007 10:24 GMT
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

> Ed,
> I'm unclear on what originally caused your problem. Was it the time needed
[quoted text clipped - 151 lines]
>>>>>
>>>>> Ed (in Virginia)
Ed - 22 Nov 2007 11:16 GMT
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)

>I have installations of the identical VBA program on 20 different
>computers. I use the following command with some frequency.
[quoted text clipped - 18 lines]
>
> Ed (in Virginia)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.