This code is weird and does not work right

[GaryBurt]

I have a table where I use a field that is a hyperlink to hold a file name. The user gets an open dialog box to pick the file to use. That works fine. Then I want to strip the path and the file extension off to store in another field. It kind of works. It strips the path, but does not work for the file extension, because Access thinks the string is 196 characters long, way longer that it really is. What am I doing wrong? (Same results in Access 2003 and 2010). P.S. I can not find any documentation on GetOpenFileName, either.

code follows:

ReturnValue = GetOpenFileName(MyFile)

If ReturnValue = 0 Then
MsgBox "Cancel Button was pressed"
Else
path = "#..\" & Mid(MyFile.lpstrFile, 33) & "#"
pos = InStrRev(MyFile.lpstrFile, "\")
newName = Mid(MyFile.lpstrFile, pos + 1)
theLen = Len(newName)
RecipeName = newName
RecipeName = Mid(RecipeName, 1, theLen - 4)

End If

 

[RainLover]

If ReturnValue = 0 Then

Return Value is a String and will never = Zero

Try

If Not Len(ReturnValue) Then

Any length will return True. Not Len looks for the opposite namely False

How do you know the length to be 196???

The Last three lines of code could be replaced by just One Line. Think about it as you are just renaming the same thing.

The last line. I would consider using "Left" rather than "Mid". Search Access Help.

Finally use some message boxes to display the value of the previous line of code. It may help you debug.

 

[GaryBurt]

Thanks. The work-around is:

If ReturnValue = 0 Then
MsgBox "Cancel Button was pressed"
Else
path = "#..\" & Mid(MyFile.lpstrFile, 33) & "#"
pos = InStrRev(MyFile.lpstrFile, "\")
newName = Mid(MyFile.lpstrFile, pos + 1)
theLen = InStr(newName, ".pdf")
RecipeName = newName
RecipeName = Mid(RecipeName, 1, theLen - 1)

 

[GaryBurt]

Forgot to mention,

If ReturnValue = 0 Then is valid,
because the return value is not a string.
The filename is a member of an object of type OPENFILENAME

Dim MyFile As OPENFILENAME
GetOpenFileName(MyFile)

I don't remember where I got this info. I can not find anything about it now! ;-(
Hardest thing about learning VBA is finding the necessary info!

I found out the size by using MsgBox to print out the length, but I took out the MsgBox statements for the post because they contributed nothing at that point. They had already caused me to pull my hair out. Did not want to share the pain.
Thanks again for the help.

 

[Rabbie]

It makes life much easier for those of us who are trying to help you if you can post your code within code tags. thes have the format [ code ] closed by [ /code ]. but without the spaces after the [ and before the ].