How to find the drive letter?

  • Thread starter Thread starter Mervyn Thomas
  • Start date Start date
M

Mervyn Thomas

I have found that the following often does not work and causes a run time
error:
ChDir "..\finance"
whereas ChDir "W:\finance" always works

So how can you determine what the drive letter is (not always W:) and insert
it in the ChDir command?

Mervyn
 
Drive letter of what, CD drive, primary disk, secondary, USB key, etc.?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi,

With help of FileSystemObject you can get the driveletter of a path
like this :

Function readDriveLetter(pstrPath as String) as String

Dim fso, d
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(pstrPath))
readDriveLetter = d

End Functio
 
I have found that the following often does not work and causes a run time
error:
ChDir "..\finance"
whereas ChDir "W:\finance" always works

So how can you determine what the drive letter is (not always W:) and insert
it in the ChDir command?
Click to expand...

Mervyn,

Bob's question is relevant; it's really not clear where you're getting
the path that you want to find the drive letter of.

However that's not the point of MY post; mine is to warn you that
ChDir "W:\finance" WON'T always work. It will work if your current
drive is W (or whatever), but not if you are moving from one drive to
another. See the on-line help for ChDir:

----------------------

Remarks

The ChDir statement changes the default directory but not the default
drive. For example, if the default drive is C, the following statement
changes the default directory on drive D, but C remains the default
drive:

ChDir "D:\TMP"

------------------

To be completely safe, you have to use a ChDrive statement before the
ChDir one.

But as for your original question... well, we can't go any further
with that until you answer Bob's question. (Though I WILL mention that
if it's just the drive that the current workbook is saved on, you can
get it by just pulling the left hand character (using the Left()
function) from the ActiveWorkbook.Path property.)
 
That doesn't seem to work unless the string (path) already has the drive
letter in it. Can you elaborate on your solution with respect to the
question of the original poster?

From what I can see

fso.GetDriveName(pstrPath)

alone would return the drive letter, but it looks to be little more than a
string parsing function (although it will parse a UNC path).
 
My guess is that your current "Drive" is not W:\.
Otherwise, your code works fine for me.


Sub Demo()
Dim s
Look to see if you are on the W Drive?
MsgBox CurDir$

s = CurDir$
ChDir ("..\OtherFolder")
s = CurDir$
End Sub
 
Just to clarify - I am writing the code on a stand alone PC in Drive C: but
with the intention to putting it on several servers whose drive could be W:
or O: etc
I hav'nt yet tried along the lines already suggested excxept that I do run
into trouble using relative addresses such as CHDir "..\RootFolder\ etc "
Sometimes it works other times not.
 
Just to complete the subject this is what I now use and works fine on any
hard drive:
As a little niggle - I don't understand the need for all these double quotes
and "&"'s but VB is a bit pedantic!!

'find path letter and change directory to CSVFiles
Dim readFullPath As String 'of this workbook
Dim readDriveLetter As String
Dim NewFullPath As String
readFullPath = ActiveWorkbook.FullName
readDriveLetter = Left(readFullPath, 1)
NewFullPath = readDriveLetter &
":\Finance\TLPtimesheet\CSVFiles"
ChDir "" & NewFullPath & ""
Mervyn

Tom Ogilvy said:
That doesn't seem to work unless the string (path) already has the drive
letter in it. Can you elaborate on your solution with respect to the
question of the original poster?

From what I can see

fso.GetDriveName(pstrPath)

alone would return the drive letter, but it looks to be little more than a
string parsing function (although it will parse a UNC path).
Click to expand...
 
Not sure if this will work for you, so I'll just throw it out.

Sub Demo()
' Dana DeLouis
Dim Remember As String
Dim FullFileName As String

Remember = CurDir$
FullFileName = ActiveWorkbook.FullName

'// This is ok
ChDrive FullFileName
ChDir "\Finance\TLPtimesheet\CSVFiles"

' Do your stuff here:

'If you want to go back...
ChDrive Remember
ChDir Remember
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Mervyn Thomas said:
Just to complete the subject this is what I now use and works fine on any
hard drive:
As a little niggle - I don't understand the need for all these double quotes
and "&"'s but VB is a bit pedantic!!

'find path letter and change directory to CSVFiles
Dim readFullPath As String 'of this workbook
Dim readDriveLetter As String
Dim NewFullPath As String
readFullPath = ActiveWorkbook.FullName
readDriveLetter = Left(readFullPath, 1)
NewFullPath = readDriveLetter &
":\Finance\TLPtimesheet\CSVFiles"
ChDir "" & NewFullPath & ""
Mervyn
Click to expand...
 
Back
Top