Determining a files extension?

[baronqueefington]

Hello,

I need to find a files extension so that I can add an attachment to an email.

I know the folders pathway and the files will have a specific number but the files range from office docs to images.

The file number will relate to a field in my db, so that when the email is generated it knows which file to look for and attach.

I assumed that I could use something like "C:folder\file\" & me.field & "*" but when using * it tells me that the file doesn't exist.

Any help you be great.

[June7]

Extract file extension with string manipulation. x represents the file name.

Right(x, InStrRev(x, ".") - 1)

[baronqueefington]

Would x have to have the extension or can I just supply "C:folder\file\" & me.field and some how it finds the extension?

[June7]

Provide it any string you want. It will pull all characters following the first period from the right.

This assumes all strings have a file extension.

If you also assume all extensions are 3 characters, can be simply: Right(x, 3)

Consistency in data is critical.

[baronqueefington]

Thank you! I shall try this in the morning.

[baronqueefington]

Here is the quick test I used:

FileName = "C:\Users\Paul\Desktop\templates\" & Me.field & "."

test = Right(FileName, InStrRev(FileName, ".") - 1)

MsgBox (test)

message box shows "C:\Users\Paul\Desktop\templates\1."


If I provide it with a the extension also the message box shows : "C:\Users\Paul\Desktop\templates\1.docx"


any ideas?

[baronqueefington]

Right(FileName, Len(FileName) - InStrRev(FileName, ".")) This code seems to separate the extension from the path name, but the extension has to be supplied in the FileName.

I can only supply it with the pathname without the knowing the extension.

[CJ_London]

in your vba window immediate section

if you type the following and hit enter

?Dir("C:\Users\Paul\Desktop\templates\" & "1.*")

it should return the first file called 1.extension where extension is docx, xlsx or whatever

so what you may need to do is use the Dir function to return the name which you can then pass to your email

[June7]

Sorry, guess I should have used Mid instead of Right. I did test the Right and thought it was pulling just the extension, now not sure what I was doing before.

Mid(x, InStrRev(x, ".")+1))

The file name is stored in the table with the extension?

I am not really understanding your statement "I can only supply it with the pathname without the knowing the extension." What exactly do you want?
You want to pull and use the extension from the filename OR you want to cut the extension from the file name?