Run-time error "5" on ChDrive (Left(.DefaultFilePath, 1))

[Stev1parr]

I have a code that seems to work on some PCs and not on others. All PCs are running 2007. Files can be on PC or network drives. Essentially the code error occurs at ChDrive (Left(.DefaultFilePath, 1)). I step into the code. When I get to ChDrive (Left(.DefaultFilePath, 1)), error on some PCs. Below is a partial of the code. I cannot figure out what is causing this error.

Private Sub CommandButton119_Click()
Dim Filter As String, Title As String
Dim File As String
Dim FilterIndex As Integer
Dim Filename As Variant
Dim curWB As Workbook, destWB As Workbook
Dim ws As Worksheet, bFound As Boolean

Set curWB = ActiveWorkbook
With Application
'Set Password
PWORD = "1111"
End With

'Unprotect active sheet with password.
Unprotect PWORD

' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("")

With Application
' Set File Name to selected File

 

[HughLerwill]

You do not appear to be using

ChDrive (Left(.DefaultFilePath, 1))

in your example!

Excel help says;

"If you supply a zero-length string (""), the current drive doesn't change"

 

[HughLerwill]

a$ = vbNull
ChDrive Left$(a$, 1)

reproduces the error 5 for me.

a$ = ""
ChDrive Left$(a$, 1)

does not.

I suggest you check the contents of .DefaultFilePath

 

[Stev1parr]

In my initial code example was not complete. below is a much better example that includes ChDrive (Left(.DefaultFilePath, 1)).
Private Sub CommandButton119_Click()
Dim Filter As String, Title As String
Dim File As String
Dim FilterIndex As Integer
Dim Filename As Variant
Dim curWB As Workbook, destWB As Workbook
Dim ws As Worksheet, bFound As Boolean

Set curWB = ActiveWorkbook
With Application
'Set Password
PWORD = "1111"
End With

'Unprotect active sheet with password.
Unprotect PWORD

' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("")

With Application
' Set File Name to selected File
Filename = .GetOpenFilename(Filter, FilterIndex, Title)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
' If no file selected, exit on Cancel
If Filename = False Then
MsgBox "No file was selected."
Exit Sub
End If

 

[HughLerwill]

You can abbreviate;

ChDrive (Left(.DefaultFilePath, 1))

to

ChDrive .DefaultFilePath

because ChDrive only accepts the first character in its argument.

 

[Stev1parr]

Is the "Left" part of the command creating the inconsistency with some PCs? Also, would file extensions matter. For instance, the file that is being opened in the using the code is a .xlsm. Some PCs have no issue and some do.

 

[PHV]

What about replacing this:
ChDrive (Left(.DefaultFilePath, 1))
with this ?
ChDrive .DefaultFilePath & ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stev1parr]

Will try and get back.

 

[Stev1parr]

Tried all of the modifications to this one code. All led to the same conclusion. I am thinking that there may be a security iusse. Is this a possibility?

 

[strongm]

What is the value of DefaultFilePath on the machines that fail, and what is it on the machines that are OK? Are they definitely the same?