How to find what an error number means [{Solved}]

[daniels012]

Is there a list of error numbers and their meaning?

Michael

 

[daniels012]

I try this:

If Err.Number = 58 Then
Range("B11").Value & "l" & ".xls"

I get an error:
It highlights the first & sign
It says compile error Expected: expression

Michael

 

[Von Pookie]

If Err.Number = 58 Then
Range("B11").Value & "l" & ".xls"

Looks like you're trying to create a string, but you're not actually *doing* anything with it. It's basically a random statement, there. What do you want to do with that string?

 

[daniels012]

I am doing a SaveAs.

ActiveWorkbook.SaveAs Filename:= _
        Path & _
Range("B11").Value & ".xls", FileFormat:=xlNormal _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    If Err.Number = 58 Then
    Path & Range("B11").Value & "l " & ".xls"
    new_file = ActiveWorkbook.Name

Michael

 

[Von Pookie]

That's all well and good, but you are still effectively doing absolutely nothing with that bit of code. Ok, see how you used it in the ActiveWorkbook.SaveAs statement? It is fulfilling an expression there by returning the value to use as the workbook name.

The way you are using it here:

If Err.Number = 58 Then
    Path & Range("B11").Value & "l " & ".xls"

You are not doing anything. You aren't even saving it to a variable.

You need to tell the macro what to do with that string, not just leave it out in limbo like that.

 

[daniels012]

Ok So I should just do:

ActiveWorkbook.SaveAs Filename:= _
Path & _
Range("B11").Value & ".xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
If Err.Number = 58 Then
ActiveWorkbook.SaveAs Filename:= _
Path & _
Range("B11").Value & "l" & ".xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
new_file = ActiveWorkbook.Name

Just rewrite the hole thing over right??

Michael

 

[Scott Huish]

How about something like this?

Sub test()
Dim strPath As String, fname As String, i As Integer
strPath = "\\Sidonna\c\Estimating\"
fname = Range("B11") & "*.xls"
With Application.FileSearch
    .NewSearch
    .LookIn = strPath
    .Filename = fname
    .Execute
    i = .FoundFiles.Count
End With
If i > 0 Then
    fname = Range("B11") & i + 1 & ".xls"
    x = MsgBox("Your file already exists, do you want to make another copy?", vbYesNo)
Else
    fname = Range("B11") & ".xls"
End If
If x = vbYes Or i = 0 Then ActiveWorkbook.SaveAs strPath & fname
End Sub

 

[Von Pookie]

You could do that, but you shouldn't need to. Just check if the file already exists like Smitty suggested, then you only need to try at saving it once.

Hotpepper's example should work, but a simple example I cobbled together before I saw he posted that I believe this should work--I'm simply going with Dir instead of FileExists or FileSearch. This is definately not the *best* code, though. Hotpepper's is much better and adds some failsafes that I didn't:

Dim Path As String, NewFileName As String, i As long

i = 1
Path = "C:\"

NewFileName = Path & Range("B11").Value & ".xls"

'check if the file already exists
Do
    'if the file is not found at path specified
    If Dir(NewFileName) = "" Then
        'exit the loop
        Exit Do
    Else 'if file already exists at path specified
        'add 1 to the file name
        ' (if filename with '1' on the end exists, will try with 2, etc.)
        NewFileName = Path & Range("B11").Value & i & ".xls"
    End If
    i = i + 1
Loop

'save the file
ActiveWorkbook.SaveAs NewFileName

 

[daniels012]

Thank You for your responses ray: ray:
I ended up using HOTPEPPER's code:

Dim strPath As String, fname As String, i As Integer 
strPath = "\\Sidonna\c\Estimating\" 
fname = Range("B11") & "*.xls" 
With Application.FileSearch 
    .NewSearch 
    .LookIn = strPath 
    .Filename = fname 
    .Execute 
    i = .FoundFiles.Count 
End With 
If i > 0 Then 
    fname = Range("B11") & i + 1 & ".xls" 
    x = MsgBox("Your file already exists, do you want to make another copy?", vbYesNo) 
Else 
    fname = Range("B11") & ".xls" 
End If 
If x = vbYes Or i = 0 Then ActiveWorkbook.SaveAs strPath & fname

HOw in the world do you folks make this seem so easy :wink:

I wish I could write code like you folks...........


Thank You again and Happy Holidays!
Michael