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)

"Mervyn Thomas" wrote in message
...
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

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

--
Message posted from http://www.ExcelForum.com

On Wed, 28 Jan 2004 10:41:43 -0000, "Mervyn Thomas"
wrote:

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,

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.)

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

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).

--
Regards,
Tom Ogilvy

"tolgag " wrote in message
...
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 Function


---
Message posted from http://www.ExcelForum.com/

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


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


"Mervyn Thomas" wrote in message
...
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

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.
"Dana DeLouis" wrote in message
...
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


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


"Mervyn Thomas" wrote in message
...
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

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" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"tolgag " wrote in message
...
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 Function


---
Message posted from http://www.ExcelForum.com/

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 he

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

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


"Mervyn Thomas" wrote in message
...
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" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"tolgag " wrote in message
...
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 Function


---
Message posted from http://www.ExcelForum.com/