Data Format

[kitty77]

[Mlink] = "#\comps\pdf\" & [Msample] & ".pdf#"

Msample data looks like this: 12345.SPA, so the above results is: \comps\pdf\12345.SPA.pdf

How can I remove the .SPA? So it looks like \comps\pdf\12345.pdf in vba.

 

[Pat Hartman]

Do you want to remove ".SPA" specifically or only the period and characters to the right?

 

[kitty77]

need to remove the period and characters to the right.

 

[The_Doc_Man]

Do you know that there WILL be a period in the [MSample] data? Or is it a sometimes there, sometimes not case?

[Mink] = "#\comps\pdf\" & IIF( InStr( 1, [MSample], "." ) = 0, [MSample], Left( [MSample], Instr( 1, [MSample], "." ) - 1 ) ) & ".pdf#"

This would remove everything to the right of the first dot (.) it finds, but if there is no dot, it returns all of [MSample].

 

[kitty77]

Thanks!

 

[MarkK]

You can also split the text at the period, and only take the first element from the resulting array. This simplifies doc_mans code as follows...

[Mink] = "#\comps\pdf\" & Split(MSample, ".")(0) & ".pdf#"

 

[The_Doc_Man]

Thanks for the alternate viewpoint, MarkK - I don't always think about SPLIT but it surely could do the job.

 

[cheekybuddha]

Will the '12345' portion always be numeric?

If so, you can just use Val()

[Mlink] = "#\comps\pdf\" & Val([Msample]) & ".pdf#"

 

[Galaxiom]

Although they would work in this case, since it is also possible for folder and filenames to contain dots other than the extension separator, none of the suggestions so far should be considered as general solutions to removing the extension from a file name.

The safest way to do this is a variation in Doc's code using InStrRev() to locate the last dot.

 

[Galaxiom]

This simplifies doc_mans code

Developers should not be excessively focused on simplification of the appearance of the code. The simplest code is not always the most efficient code. Although I have not tested, I would expect Split() to have significant overheads., though it probably doesn't really matter much.

The desire for simple looking code often drives developers to overuse user-defined-functions when the job can be done with native SQL engine functions that, although they look more complex in the SQL, will perform better than a UDF.

 

[MarkK]

You can also do...

With CreateObject("Scripting.FileSystemObject")
   [Mink] = "#\comps\pdf\" & .GetBaseName(MSample) & ".pdf#"
End With