Why can't locate the file?

[Guest]

I have the following code that copies data to another workbook, then saves
the file as the employee name in cell C2. The user can define the location
the file saves.

Private Sub CommandButton4_Click()
'rCell Makes a copy of the initial calculations and saves to the Data
worksheet
'rFound looks for a duplicate date and if found copies over it else copies
to next avail row
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Else
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B1737").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
'the following opens the "Save As" MsgBox in Excel so the user
'can save to the location they desire. File is saved as name of employee in
cell C2
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))
If RetVal <> False Then
ThisWorkbook.SaveAs RetVal
End If

End Sub

The problem I have is after saving the file to the desired location the file
is saved as type "File" not .xls. I don't know what that means exactly.
However when the file is opened for a second time and changes made, then the
user clicks the Save command button which fires the above code, and I get a
runtime error saying Unable to locate file "employee name".

Why can't it open the file? If I close and go to the file and open it is
fine. I just can't replicate the code above a second time.

Thanks for your help!

 

[Guest]

try changing
ThisWorkbook.SaveAs RetVal

to

ThisWorkbook.SaveAs RetVal, xlworkbookNormal

 

[NickHK]

Tim,
It is saved in the Excel file format, but because you are not adding a file
extension (.xls) to the filename, when you see in Explorer, Window has no
idea what type of file it is, as Windows only looks at the extension, not
inside the file, so it gives the file the generic "File" type.
So specificy the filename as RetVal & ".xls".

NickHk
P.S. To me, one of the many reason not to "Hide nown extension" in Windows.
That way it would have more noticable that there was no extension on the
filename.

 

[Guest]

Still the same.

I have an idea, don't know if this matters but.....
When you typically do a File, Save As you get the window that opens asking
for the file name and below that you have "Save as Type: Microsoft Excel
Workbook(*.xls)"

With my code I get "Save as Type: All Files(*.*)

Is that maybe the problem?

 

[Guest]

xlworkbookNormal
tells excel to save it as a workbook.

If it makes you more comfortable you can do this as well


RetVal = Application.GetSaveAsFilename( _
InitialFilename:=Range("C2").Value & ".xls", _
filefilter:="Excel Files (*.xls), *.xls")


If c2 already contains the .xls extension, then you can remove that part
from the above


RetVal = Application.GetSaveAsFilename( _
InitialFilename:=Range("C2").Value, _
filefilter:="Excel Files (*.xls), *.xls")