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

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

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



Cathy W wrote:

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

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" wrote:

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

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



Cathy W wrote:

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 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



Cathy W wrote:

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" wrote:

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

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



Cathy W wrote:

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


--

Dave Peterson