finding a drive to write to

[pcc]

I have tools that are used by many users around the company, and their machines have differing connected drives. Sometimes I need to write a temporary file to (any) drive, and therefore need to find a drive letter that is read-write, so that the temp file can be written to this drive.
As a belt and braces job, I am trying every drive from A to Z, but am struggling to get the error handling to work. In the example here, I get an error when the drive is A, (I have no A drive) and the code goes to 'nextdrive'. However, after resetting the error handler, it then fails when looking for the B drive, (I have no B drive either) with 'Path not found' error.
What am I missing? I always seem to struggle with error handling!

Sub check_available_drive()
Dim ff As Integer
ff = FreeFile
init = 1
drv = "a"
retry:
On Error GoTo nextdrive
tfile = drv & ":\" & Environ("username") & ".csv"
Open tfile For Append As #ff
Close #ff

Kill tfile
' once it gets here then it has found a drive to write to
MsgBox drv
Exit Sub
nextdrive:
Close #ff
On Error GoTo 0
init = init + 1
Select Case init
Case 2: drv = "b": Case 3: drv = "c": Case 4: drv = "d": Case 5: drv = "e": Case 6: drv = "f": Case 7: drv = "g":
Case 8: drv = "h": Case 9: drv = "i": Case 10: drv = "j": Case 11: drv = "k": Case 12: drv = "l": Case 13: drv = "m":
Case 14: drv = "n": Case 15: drv = "o": Case 16: drv = "p": Case 17: drv = "q": Case 18: drv = "r": Case 19: drv = "s":
Case 20: drv = "t": Case 21: drv = "u": Case 22: drv = "v": Case 23: drv = "w": Case 24: drv = "x": Case 25: drv = "y":
Case 26: drv = "z"
End Select
GoTo retry
End Sub

All suggestions welcomed. Thanks

 

[Andrew Poulsom]

Couldn't you just save it to the Desktop?

http://www.mrexcel.com/board2/viewtopic.php?t=112404

 

[pcc]

Thankd for suggestion Andrew, but I don't know enough about all machines in use to confidently know that this will work, and I have no way of trying it out on any machine ither than my own desktop PC. I would rather understand why my code doesn't work. I cannot fathom out how to reset the errot handler after the first error has occured.

 

[Richard Schollar]

Pcc

If you want to see what network drives are mapped to the computer in question, you can run the following WScript.Network script:

Sub NetworkDrives()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
DrivesStr = "Network drive Mappings:" & Chr(13)

For i = 0 To oDrives.Count - 1 Step 2
DrivesStr = DrivesStr & "Drive " & oDrives.Item(i) & " = " & oDrives.Item(i + 1) & Chr(13)
Next
Debug.Print DrivesStr
End Sub

You obviously wouldn't need the code beyond "Set oDrives = WshNetwork.EnumNetworkDrives" as this gives you the information that you need, but the rest of the code does illustrate what info is returned.

Best regards

Richard

 

[pcc]

Thanks, but finding connected drives in not the problem I need to find a connected drive that is read-write!!!

 

[Richard Schollar]

Yes, but if you have the drives, you know which ones to test.

Richard

 

[pcc]

Correct, but if the first one fails (ie I don't have read=write access) then my code fails. I could use your suggestion if there was any way to also check for its 'read-writeability' at the same time, but I doubt that is possible.

Thanks

 

[Andrew Poulsom]

Try changing:

GoTo retry

to:

Resume retry

 

[pcc]

fixed!

Public init As Integer
Public drv As String, tfile As String
Sub check_available_drive()
Dim ff As Integer
ff = FreeFile

On Error GoTo quit
tfile = drv & ":\" & Environ("username") & ".csv"
Open tfile For Append As #ff
Close #ff

Kill tfile
' once it gets here then it has found a drive to write to
MsgBox drv
Exit Sub
quit:
drive

End Sub
Sub drive()

init = init + 1
Select Case init
Case 1: drv = "a"
Case 2: drv = "b": Case 3: drv = "c": Case 4: drv = "d": Case 5: drv = "e": Case 6: drv = "f": Case 7: drv = "g":
Case 8: drv = "h": Case 9: drv = "i": Case 10: drv = "j": Case 11: drv = "k": Case 12: drv = "l": Case 13: drv = "m":
Case 14: drv = "n": Case 15: drv = "o": Case 16: drv = "p": Case 17: drv = "q": Case 18: drv = "r": Case 19: drv = "s":
Case 20: drv = "t": Case 21: drv = "u": Case 22: drv = "v": Case 23: drv = "w": Case 24: drv = "x": Case 25: drv = "y":
Case 26: drv = "z"
End Select
check_available_drive
End Sub

I think it's to do with the fact that (according to the help on errors) the copde has to have a resume or exit sub statement in order to reset the error. Don't fully follow this, but this seems to work OK> Thanks for you r replies guys.

 

[pcc]

Your suggestion works also Andrew, and is I'm sure a better method. Thnk you very much.
Regards