K
Kristina
This isn't really a question; more of an FYI for my fellow Excel 2004
VBA developers. It appears that there's a bug with the "Dir" command.
Specifically, I use Dir to see if the user has a sever mounted. The
command
Dir("ServerName:", vbDirectory)
should return a non-blank string if the server is mounted (it actually
returns the name of the first file found at that location). I've
successfully used this command in Excel 98 and X (mac), and in 97, XP
and 2003 (win). However, it ALWAYS returns a blank (zero-length)
string in Excel 2004.
I've been working with someone at Microsoft on this issue, and they're
marking it as a bug.
As a workaround, I just go ahead and try to open a file at that
location (a file that I know will always be there). If the file isn't
found, you get a trappable error. The following example will suppress
VBA's error message and display a custom message box:
Sub ServerCheck()
Dim stCheckFile as string
(add code to store a path to the file you want to open in
stCheckFile)
Application.DisplayAlerts = False
On Error Resume Next
Workbooks.Open stCheckFile
If Err.Number = 1004 Then 'File couldn't be opened
MsgBox prompt:="The XXX server must be mounted to run
MyMacro.", _
Title:="MyMacro Error"
End
End If
Application.DisplayAlerts = True
End Sub
VBA developers. It appears that there's a bug with the "Dir" command.
Specifically, I use Dir to see if the user has a sever mounted. The
command
Dir("ServerName:", vbDirectory)
should return a non-blank string if the server is mounted (it actually
returns the name of the first file found at that location). I've
successfully used this command in Excel 98 and X (mac), and in 97, XP
and 2003 (win). However, it ALWAYS returns a blank (zero-length)
string in Excel 2004.
I've been working with someone at Microsoft on this issue, and they're
marking it as a bug.
As a workaround, I just go ahead and try to open a file at that
location (a file that I know will always be there). If the file isn't
found, you get a trappable error. The following example will suppress
VBA's error message and display a custom message box:
Sub ServerCheck()
Dim stCheckFile as string
(add code to store a path to the file you want to open in
stCheckFile)
Application.DisplayAlerts = False
On Error Resume Next
Workbooks.Open stCheckFile
If Err.Number = 1004 Then 'File couldn't be opened
MsgBox prompt:="The XXX server must be mounted to run
MyMacro.", _
Title:="MyMacro Error"
End
End If
Application.DisplayAlerts = True
End Sub