checking for drive

J

jnf40

I have a workbook that saves to drive C:\ when the user clicks a button on
worksheet1. I want to have it look to see if drive U:\, a drive on a server,
is available, and if so it will save the file there, if drive U:\ is not
available then I want it to save the file to drive C:\. Any help would be
greatly appreciated.
 
D

Dave Peterson

First, I wouldn't use the mapped drive.

You may connect to it via the U: drive, I may use x:. Others may not map it at
all.

I didn't test this (no access to a LAN drive):

Option Explicit
Sub testme()

Dim myUNCPath As String
Dim TestStr As String

myUNCPath = "\\server01\share\folder\"
If Right(myUNCPath, 1) <> "\" Then
myUNCPath = myUNCPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myUNCPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myUNCPath & " isn't available"
Else
MsgBox myUNCPath & " Was found"
End If

End Sub
 
J

jnf40

Dave,
Thanks for the quick response. Following I have copied the code I have to
create a folder in the C:\ drive if one does not exist, this works fine. What
I want to do is check to see if the U:\ drive is available, if so it would
create a folder in the U:\ drive automatically like the following code does
in drive C:\, however if the user is on a computer not hooked up to the
server it would automatically create a folder in the C:\ drive, without the
user having to do anything. I hope this explains what I am looking to do a
little clearer.

Private Sub CommandButton1_Click()
Dim dirstr As String
Dim myFileName As String
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="123"
Sheets("Create Pay Report").Visible = True
Sheets("Save Me").Visible = False
ActiveWorkbook.Protect Password:="123"
Set wb = ActiveWorkbook
dirstr = "C:\1257" & " " & "Forms\"
If Not DirectoryExist(dirstr) Then
MkDir dirstr
myFileName = "C:\1257" & " " & "Forms\" & "Blank" & " " & "Cement" &
" " & "Stabilized" & " " & "Backfill" & " " & "1257" & " " & "Pay" & " " &
"Reports.xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myFileName = "C:\1257" & " " & "Forms\" & "Blank" & " " & "Cement" &
" " & "Stabilized" & " " & "Backfill" & " " & "1257" & " " & "Pay" & " " &
"Reports.xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
Application.ScreenUpdating = True

End Sub
 
D

Dave Peterson

Your original question was clear to me.

You could turn that suggested routine into a function if you want.

Option Explicit
Function DoesPathExist(myPath As String) As Boolean

Dim TestStr As String
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

DoesPathExist = CBool(TestStr <> "")

End Function

Then you could add something to your existing code that checks first.


Sub testme()

If DoesPathExist("\\myserver\share") Then
'put stuff on that drive
MsgBox "yep"
Else
'put stuff on C: drive
MsgBox "nope"
End If
End Sub
 
J

jnf40

Dave,
It works, thanks. Now can this be expanded? I want it to create a directory,
let's say US 49, if it doesn't exist and then create a sub directory, let's
say Form 1257, if it doesn't exist then save the file. I can't figure out how
to do the IFs or what exactly I need to do. The following is some of the code
I have:

Function DirectoryExist(sstr As String)
Dim lngAttr As Long
DirectoryExist = False
If Dir(sstr, vbDirectory) <> "" Then
lngAttr = GetAttr(sstr)
If lngAttr And vbDirectory Then _
DirectoryExist = True
End If
End Function
Function DoesPathExist(myPath As String) As Boolean

Dim TestStr As String
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

DoesPathExist = CBool(TestStr <> "")

End Function

Dim dirstr As String
Dim dirstr1 As String


dirstr = "U:\" & Range("hwy") & "\"
dirstr1 = "C:\" & Range("hwy") & "\"
Application.DisplayAlerts = False
If DoesPathExist("U:\") Then
If Not DirectoryExist(dirstr) Then
MkDir dirstr
myFileName = Range("file")
myFileName = "U:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myFileName = Range("file")
myFileName = "U:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
'put stuff on that drive
MsgBox "File Saved to " & myFileName
Else
If Not DirectoryExist(dirstr1) Then
MkDir dirstr1
myFileName = Range("file")
myFileName = "C:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myFileName = Range("file")
myFileName = "C:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
'put stuff on C: drive
MsgBox "File Saved to " & myFileName
End If
Application.DisplayAlerts = True

End Sub
 
D

Dave Peterson

You want to create a directory "US 49" -- but if it doesn't exist, create one
named "form 1257".

This doesn't make sense to me. You couldn't create a directory if it did exist.

But when I want to create a directory, I use something like:

dim myParentFolder as string
myparentfolder = "C:\temp\" 'something that I know exists
on error resume next
mkdir myparentfolder & "US 49"
on error goto 0

If the folder already exists, then the error is ignored. If the folder doesn't
exist, then it's created.
Dave,
It works, thanks. Now can this be expanded? I want it to create a directory,
let's say US 49, if it doesn't exist and then create a sub directory, let's
say Form 1257, if it doesn't exist then save the file. I can't figure out how
to do the IFs or what exactly I need to do. The following is some of the code
I have:

Function DirectoryExist(sstr As String)
Dim lngAttr As Long
DirectoryExist = False
If Dir(sstr, vbDirectory) <> "" Then
lngAttr = GetAttr(sstr)
If lngAttr And vbDirectory Then _
DirectoryExist = True
End If
End Function
Function DoesPathExist(myPath As String) As Boolean

Dim TestStr As String
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

DoesPathExist = CBool(TestStr <> "")

End Function

Dim dirstr As String
Dim dirstr1 As String

dirstr = "U:\" & Range("hwy") & "\"
dirstr1 = "C:\" & Range("hwy") & "\"
Application.DisplayAlerts = False
If DoesPathExist("U:\") Then
If Not DirectoryExist(dirstr) Then
MkDir dirstr
myFileName = Range("file")
myFileName = "U:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myFileName = Range("file")
myFileName = "U:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
'put stuff on that drive
MsgBox "File Saved to " & myFileName
Else
If Not DirectoryExist(dirstr1) Then
MkDir dirstr1
myFileName = Range("file")
myFileName = "C:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myFileName = Range("file")
myFileName = "C:\" & myFileName & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
'put stuff on C: drive
MsgBox "File Saved to " & myFileName
End If
Application.DisplayAlerts = True

End Sub
Click to expand...
 
J

jnf40

I guess I didn't explain it too well. I want to create a directory "US 49" if
it doesn't exist then create it and then put a sub directory in it named
"form 1257", if it does exist then it would just put the sub directory "form
1257" in it and then save the file I've named.
 
D

Dave Peterson

Again, I just try to create it and ignore any error that may (or may not!)
occur:

dim myParentFolder as string
myparentfolder = "C:\temp\" 'something that I know exists
on error resume next
mkdir myparentfolder & "US 49"
mkdir myparentfolder & "US 49" & "\form 1257"
on error goto 0

Just make sure you include the subfolders when you're going down the path.

I guess I didn't explain it too well. I want to create a directory "US 49" if
it doesn't exist then create it and then put a sub directory in it named
"form 1257", if it does exist then it would just put the sub directory "form
1257" in it and then save the file I've named.
Click to expand...
 
J

jnf40

Dave,

I’ll try this again as far as explaining everything I am wanting to do, I
think I may be confusing myself more than anything, but here goes…

I want to check to see if drive “U:†is valid, if so then I want to check
for directory “US 49â€, if it is there fine if not create it. If directory “US
49†is there then I want to check for sub directory “1257 Formsâ€, if it is
there then my file would save there, if it is not there then it would create
the sub directory “1257 Forms†then save my file there.
Now if drive “U:†is not a valid drive then it would check, create, and save
everything as above in drive “C:†So when all is finished I should have one
of the following paths:
“U:\US 49\1257 Forms\my file†if “U:†is a valid drive or
“C:\US 49\1257 Forms\my file†if drive “U:†is not a valid drive.

I have it now where it will do everything but check for and create the sub
directory. I hope I haven’t muddied the waters more, I appreciate your
patience and knowledge.
 
D

Dave Peterson

I think you have all you need.

Option Explicit
Function DoesPathExist(myPath As String) As Boolean

Dim TestStr As String
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

DoesPathExist = CBool(TestStr <> "")

End Function

sub testme01()

dim myParentFolder as string
dim myFolder as string
myparentfolder = "U:\" 'something that I know exists
if doespathexist(myparentfolder) then
'keep going using U:
else
'switch to the C: drive
myparentfolder = "C:\"
end if

on error resume next
mkdir myparentfolder & "US 49"
mkdir myparentfolder & "US 49" & "\form 1257"
on error goto 0

myfolder = myparentfolder & "US 49" & "\form 1257"

'and use myfolder in the rest of your code.

end sub

========
untested, uncompiled--watch for typos.
 
J

jnf40

Thanks Dave this was exactly what I needed.

Dave Peterson said:
I think you have all you need.

Option Explicit
Function DoesPathExist(myPath As String) As Boolean

Dim TestStr As String
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

DoesPathExist = CBool(TestStr <> "")

End Function

sub testme01()

dim myParentFolder as string
dim myFolder as string
myparentfolder = "U:\" 'something that I know exists
if doespathexist(myparentfolder) then
'keep going using U:
else
'switch to the C: drive
myparentfolder = "C:\"
end if

on error resume next
mkdir myparentfolder & "US 49"
mkdir myparentfolder & "US 49" & "\form 1257"
on error goto 0

myfolder = myparentfolder & "US 49" & "\form 1257"

'and use myfolder in the rest of your code.

end sub

========
untested, uncompiled--watch for typos.
Click to expand...
 
Top