Help to Indentify a drive letter

G

Grandad

Can anyone help with the code to first identify a drive letter on PC which
may also be different to the one on the PC that the macro was built on. I
need to save a file to a specific folder path and if the drive letter is
different how to specify a folder path.

I hope that makes sense?
 
T

Tom Ogilvy

What information do you have to start with? How would we know we have the
right drive?

Do you want to search all drives for a particular folder?
 
T

Tom Ogilvy

If you have a path string:

Sub findDrive()
Dim fs As Object, f As Object
Dim sPath As String, drv As Object
Dim sPath1 As String
sPath = "Data\CSVDaily"
Set fs = CreateObject("Scripting.FileSystemObject")
For Each drv In fs.Drives
sPath1 = drv.DriveLetter & ":\" & sPath
On Error Resume Next
Set f = Nothing
Set f = fs.GetFolder(sPath1)
On Error GoTo 0
If Not f Is Nothing Then
MsgBox "found in drive " & drv.DriveLetter
End If
Next
End Sub
 
G

Grandad

Tom

I'm sorry if I've not been clear.

I guess what I need is to be able to identify the default drive letter for
each PC the macro is used on and to have a folder path to save a file that
works on all PCs.

I hope that's a bit better tan my first attempt?

Mick
 
T

Tom Ogilvy

Dim sDrive as String
sDrive = left(curdir,1)
on error resume next
mkdir sDrive & ":\MyMacroFolder"
On error goto 0
Application.displayalerts = False
ThisWorkbook.SaveAs sDrive & ":\MyMacroFolder\" & thisworkbook.Name
Application.DisplayAlerts = True
 
G

Grandad

Thanks Tom I'll try that,

I know how to move forward in a folder structure, but how do to go back?

Thanks again Mick
 
T

Tom Ogilvy

chdir ..

--
Regards,
Tom Ogilvy

Grandad said:
Thanks Tom I'll try that,

I know how to move forward in a folder structure, but how do to go back?

Thanks again Mick

on
Click to expand...
 
G

Grandad

Sorry Tom

I don't know how to use chdir..

I have a line something like:

Thisworkbook.Path is in folder4

ActiveWorkbook.SaveA FileName:=Thisworkbook.Path &
"\folder5\folder6\folder7" &ThisWorkBook.name

How do I go from ThisWorkBook.Path to \folder3 or folder2 etc?

Regards

Mick
 
T

Tom Ogilvy

varr = Split(Thisworkbook.path)
for i = lbound(varr) to ubound(varr)
debug.print varr(i)
Next

if you wanted to save the file two directories above its current directory

? thisworkbook.Path
D:\folder1\folder2\folder3\folder4

ActiveWorkbook.SaveAs FileName:=Thisworkbook.Path & _
"\..\..\" & ThisWorkBook.name

? thisworkbook.Path
D:\folder1\folder2

two dots/periods means the directory above.
 
G

Grandad

Tom it's working more or less as I need it too so thanks for your help.

Having created the new folder is there a way to go back or identify the
original drive we started from?

Regards

Mick
 
T

Tom Ogilvy

Before you change anything, capture the name of the drive

sDriveStart = Left(curdir,1)

or if you want to know where the file was originally saved

sDriveSaved = Left(Thisworkbook.Path,1)
 
G

Grandad

Tom unfortunately it doesn't solve my problem. The situation is as follows:

A PC has a C:\ drive (or other local drive) and a P:\ on a server. When you
open my macro from the local drive the location the macro always seems to
point to is the P:\ drive. That's OK with what you have given me but have
done the business I need to get back to the local drive again.

Any ideas - you have done good so far Thanks

Mick
 
T

Tom Ogilvy

Sub findDrive()
Dim fs As Object, f As Object
Dim sPath As String, drv As Object
Dim sPath1 As String, dType As String
Set fs = CreateObject("Scripting.FileSystemObject")
For Each drv In fs.Drives
dType = ShowDriveType(fs, drv)
If dType = "Network" Then
dShare = drv.ShareName
Else
dShare = ""
End If
Debug.Print drv.DriveLetter & " - " & dType & _
IIf(dShare <> "", " - " & dShare, "")
Next
End Sub

Function ShowDriveType(fso, drv)
Dim d, t
Set d = drv
Select Case d.DriveType
Case 0: t = "Unknown"
Case 1: t = "Removable"
Case 2: t = "Fixed"
Case 3: t = "Network"
Case 4: t = "CD-ROM"
Case 5: t = "RAM Disk"
End Select
ShowDriveType = t
End Function



will list the drive letters and the drive type.

Perhaps that will give you what you want.

You can also look at Application.Path

? left(Application.Path,1)
C
 
Top