PDA

View Full Version : Code now gives an error



daniels012
03-10-2008, 08:23 AM
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\ (file://sidonna/c/Estimating/)"
strPath2 = "\\5.121.152.146\Estimating\ (file://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
03-10-2008, 08:30 AM
Is this what you mean?



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

Norie
03-10-2008, 08:30 AM
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
03-10-2008, 08:50 AM
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
03-10-2008, 08:54 AM
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
03-10-2008, 08:58 AM
Norie,
Thank You for your explanation!!
on the 2nd, I am actually looking for paths not files.

Thank you!!!!
Michael

Norie
03-10-2008, 09:04 AM
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.

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

daniels012
03-10-2008, 09:14 AM
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
03-10-2008, 09:24 AM
It does, but if testing for a directory I should have used



If Dir(strPath, vbDirectory) <> ""

Norie
03-10-2008, 10:36 AM
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
03-10-2008, 10:36 AM
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
03-11-2008, 06:11 AM
This is my attempt. Does this look close to what I am looking for?

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

Michael

Bob Phillips
03-11-2008, 06:39 AM
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

daniels012
03-11-2008, 07:11 AM
Very nice!
XLD and Norie
Thank You