CODEWITHSUNDEEP
vbafileexcelfilenames
Yigit Tanverdi
Yigit Tanverdi

Reputation: 161

subracting specific word from a variable file name

This might be easy for many of you but i am new in VBA.I have an Excel file with variable name which is "Dateiname" and i would like to update this "Dateiname" by adding variable "hops". My Problem is Dateiname has already ".xlsm" inside of it and i want ".xlsm" Extension to be gone after the Dateiname, so i can add "hops" variable to "Dateiname" and end it with ".xlsm" Extension. 

    Dim hops as String
    Dim Dateiname as String

 Dateiname = Dateiname & hops & ".xlsm"

How can i do this ?

Upvotes: 1

Views: 53

Answers (2)

user3598756
user3598756

Reputation: 29421

just to play a little bit with, the following one row statement

Dateiname= Mid(Dateiname, 1, InStrRev(Dateiname, ".") - 1) & hops & Mid(Dateiname, InStrRev(Dateiname, "."))

would work for every file extension and for names containing dots other then the extension one

and whose more "speaking" version is

Dim iDot as long
iDot = InStrRev(Dateiname, ".")
Dateiname = Mid(Dateiname, 1, iDot - 1) & hops & Mid(Dateiname, iDot)

they both won't work if the name hasn't any dot in it...

Upvotes: 0

John Coleman
John Coleman

Reputation: 51988

There are several ways to do this. The simplest is to use

Dateiname = Replace(Dateiname , ".xlsm", hops & ".xlsm")

A drawback of this previous solution is it would fail if the extension is something other than .xlsm. A more robust approach is

Dim A as variant, i as Long
A = Split(Dateiname,".")
i = UBound(A)-1
A(i) = A(i) & hops
Dateiname = Join(A,".")

Finally, the File System Object (which can be used from VBA) contains various methods for taking apart and reassembling paths in different ways. I tend to use lower tech approaches like the above, but for some purposes you might need more sophisticated tools.

Upvotes: 3

Related Questions