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?

--
Kind Regards

Mick

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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

chdir ..

--
Regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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


"Tom Ogilvy" wrote in message
...
chdir ..

--
Regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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.

--
Regards,
Tom Ogilvy

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


"Tom Ogilvy" wrote in message
...
chdir ..

--
Regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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)



--
Regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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

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



--
Regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick

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

--
Regards,
Tom Ogilvy

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

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



--
Regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy



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


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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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?

--
Regards,
Tom Ogilvy


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

--
Kind Regards

Mick