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.

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




jnf40 wrote:

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.

--

Dave Peterson

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


"Dave Peterson" wrote:

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




jnf40 wrote:

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.

--

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

jnf40 wrote:

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

"Dave Peterson" wrote:

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




jnf40 wrote:

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.

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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

jnf40 wrote:

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

"Dave Peterson" wrote:

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




jnf40 wrote:

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.

--

Dave Peterson


--

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.

jnf40 wrote:

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

"Dave Peterson" wrote:

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

jnf40 wrote:

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

"Dave Peterson" wrote:

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




jnf40 wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson