Trying to autopopulate a Save as filename

[Guest]

Hello. I have a template that opens, asks the user to select a file to get
values from, gets values from that file, and then automatically pops up the
save as dialog box. I want to put a filename there for the user so all they
have to do is choose save. I just want to add a string to the filename that
they opened to get values from. That value is stored in a variable called
fileToOpen and decalred as Variant. The problem I am having is that if I say:

fileToOpen & " Cathy"

it puts the Cathy after the whole filename like this: Todays Report.xls
Cathy.

I need it to drop the .xls and just add " Cathy".

Confused.....Hope someone can help.

Cathy

 

[Dave Peterson]

if lcase(right(filetoopen,4)) = ".xls" then
filetoopen = left(filetoopen,len(filetoopen)-4))
end if

filetoopen = filetoopen & "Cathy" & ".xls" 'include the .xls?

 

[Guest]

Thanks for the reply Dave. This worked great! What I wanted to do though if
possible is to add the string before the filename
(ie: filetoopen = Cathy Todays Report). When I put it into the code it just
brings back the file name and not the string in front. Any ideas?

Thanks,
Cathy

 

[Dave Peterson]

If you're using xl2k or higher you can use instrrev() to find the last backslash
and insert it there.

Option Explicit
Sub testme()

Dim myFilename As String
Dim lastBackSlash As Long
myFilename = "C:\folder1\folder2\folder3\myfile.xls"
lastBackSlash = InStrRev(myFilename, "\")

If lastBackSlash > 0 Then
myFilename = Left(myFilename, lastBackSlash) _
& "Cathy" & Mid(myFilename, lastBackSlash + 1)
Else
'what should happen?
End If

MsgBox myFilename
End Sub

If you're using xl97, you could use:

Option Explicit
Sub testme()

Dim myFilename As String
Dim lastBackSlash As Long
Dim iCtr As Long
myFilename = "C:\folder1\folder2\folder3\myfile.xls"

lastBackSlash = 0
For iCtr = Len(myFilename) To 1 Step -1
If Mid(myFilename, iCtr, 1) = "\" Then
lastBackSlash = iCtr
Exit For
End If
Next iCtr

If lastBackSlash > 0 Then
myFilename = Left(myFilename, lastBackSlash) _
& "Cathy" & Mid(myFilename, lastBackSlash + 1)
Else
'what should happen?
End If

MsgBox myFilename
End Sub