Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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. "Dave Peterson" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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. jnf40 wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
Dave,
Ill 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 havent muddied the waters more, I appreciate your patience and knowledge. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
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. jnf40 wrote: Dave, Ill 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 havent muddied the waters more, I appreciate your patience and knowledge. -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking for drive
Thanks Dave this was exactly what I needed.
"Dave Peterson" wrote: 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. jnf40 wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Checking for Duplicates in List | Excel Worksheet Functions | |||
How can I use spell checking in a protected worksheet? | Excel Worksheet Functions | |||
Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
Checking names on correct line across sheets | Excel Worksheet Functions | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) |