Code now gives an error

[daniels012]

Here is my code:

 
Sub SaveToSidonna()
    Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
    Dim strPath As String, fname As String, i As Integer
    If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    Sheets("ESTIMATING").Select
    ActiveWorkbook.Save
    strPath = "\\Sidonna\c\Estimating\"
    strPath2 = "\\5.121.152.146\Estimating\"
    fname = Dir(strPath & Range("B11") & "*.xls")
    If fname = "" Then
         myFileName = Range("b11").Value & "1.xls"
    Else
         Do While fname <> ""
              myVal = Val(Replace(Replace(fname, ".xls", ""), Range("b11").Value, ""))
              myMax = WorksheetFunction.Max(myMax, myVal)
              fname = Dir()
         Loop
         myFileName = Range("b11").Value & myMax + 1 & ".xls"
    End If
    On Error Resume Next
    ThisWorkbook.SaveCopyAs strPath & myFileName
    ThisWorkbook.SaveCopyAs strPath2 & myFileName
End Sub

What I need it to do is try to SaveCopyAs to the strpath & myFileName , if it does not find this path ( no error message) then save to SaveCopyAs strpath2 & myFileName

Michael

[Bob Phillips]

Is this what you mean?

[vba]

Sub SaveToSidonna()
Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
Dim strPath As String, fName As String, i As Long

If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Sheets("ESTIMATING").Select
ActiveWorkbook.Save
strPath = "\\Sidonna\c\Estimating\"
strPath2 = "\\5.121.152.146\Estimating\"
fName = Dir(strPath & Range("B11") & "*.xls")
If fName = "" Then
myFileName = Range("B11").Value & "1.xls"
Else
Do While fName <> ""
myVal = Val(Replace(Replace(fName, ".xls", ""), Range("B11").Value, ""))
myMax = WorksheetFunction.Max(myMax, myVal)
fName = Dir()
Loop
myFileName = Range("B11").Value & myMax + 1 & ".xls"
End If
On Error Resume Next
If Dir(strPath) <> "" Then
ThisWorkbook.SaveCopyAs strPath & myFileName
ElseIf Dir(strPath2) <> "" Then
ThisWorkbook.SaveCopyAs strPath2 & myFileName
End If
End Sub
[/vba]

[Norie]

Michael

The first thing I suggest you do is drop the On Error Resume Next, it could just be hiding errors/problems.

Secondly I would suggest you use Dir not only to check filenames but the paths too.l

[daniels012]

XLD,
I will give that a try!

Norie,
Drop the On error resume Next ? OK, I wonder why it
was suggested in the first place.
Secondly Not sure what you are saying here? It looks
like Dir is being used?

Michael

[Norie]

Michael

I don't know why it was suggested or where it was suggested.

All I know is that using it can hide errors.

Now that could be a good thing but it could also be a bad thing.

You could find yourself staring at code, running it and then wondering why it's not working as you want when the On Error... is hiding some error.

And yes you are using Dir but as far as I can see you are only using it to check if files exist not paths which is what I thought you were looking to do.

[daniels012]

Norie,
Thank You for your explanation!!
on the 2nd, I am actually looking for paths not files.

Thank you!!!!
Michael

[Norie]

Michael

Are you saying the code you've posted check's for paths?

If you are I'm afraid you're wrong.

That code checks for files, though admittedly you do use the full paths but you would need that anyway.

Here's an example of checking if a path exists.
[vba]
MkDir "C:\NewDirectory"
MsgBox Dir("C:\NewDirectory", vbDirectory) ' this will show NewDirectory since the directory exists
RmDir "C:\NewDirectory"
MsgBox Dir("C:\NewDirectory", vbDirectory) ' this will show nothing because the directory doesn't exist[/vba]

[daniels012]

does this not check to see if the directory exsists?

If Dir(strPath) <> ""

And if it does, then process the code

I am just trying learn!
Michael

[Bob Phillips]

It does, but if testing for a directory I should have used

[vba]

If Dir(strPath, vbDirectory) <> ""
[/vba]

[Norie]

Michael

I suppose you could say it checks for both the file and path.

But if you only want to check for a path, which is what you asked in the 1st post, use something along the lines of what xld or I posted.

[daniels012]

Thank You both.

I guess I should have said at first what I was after.
I look for the local directory while the salesman is at our office.
If it is not found, then it searches for Hamachi (VPN) network connection.
If it is not connected or not turned on it should then a message should say "Did not copy because network connection not found".

So Norie! You are right that maybe I should stop the Error stopping from happening.

Michael

[daniels012]

This is my attempt. Does this look close to what I am looking for?

[VBA]End If
If Dir(strPath, vbDirectory) <> "" Or If Dir(strPath2, vbDirectory) <> "" Then msgbox "The directory to save the file cannot be found. So your data will not be sent." Exit Sub
ElseIf Dir(strPath, vbDirectory) <> "" Then
ThisWorkbook.SaveCopyAs strPath & myFileName
ElseIf Dir(strPath2, vbDirectory) <> "" Then ThisWorkbook.SaveCopyAs strPath2 & myFileName
End If
End Sub[/VBA]

Michael

[Bob Phillips]

[vba]

End If
If Dir(strPath, vbDirectory) <> "" Then
ThisWorkbook.SaveCopyAs strPath & myFileName
ElseIf Dir(strPath2, vbDirectory) <> "" Then
ThisWorkbook.SaveCopyAs strPath2 & myFileName
Else
MsgBox "The directory to save the file cannot be found. So your data will not be sent."
Exit Sub
End If
End Sub
[/vba]

[daniels012]

Very nice!
XLD and Norie
Thank You