Import file name into list box without path access 2007 vba-

0
votes

I am creating a database where users can click a command button, they will be taken to a folder that is stored in a trusted location and they can choose a word document from there and all the names of chosen files will show in a list box on a form. We have the code to double click the file name to open the document. What we are looking for is code to show only the file name in the list box without the path.

We have used the sample code given in the question "How to show open file dialogue in access 2007 vba" from this site to set this up so far.

Any help would be great.

2
ms-accessvba
Ta Tony, I can't seem to get that to work: here is our code at the moment. On clicking the command button the File Dialog opens and executes,public function trims it to be only the file name DblClick opens the selected object from the list box. Our problem now is that the file name has trimmed the path and can not be opened from the double click. We have got it working right up to this point. - Debra

2 Answers

3
votes

Execute the following code for each file name to be inserted into the list box.

Dim Chunks() As String, DocumentName As String
Chunks() = Split("\\server\share\folder\subfolder\docuemnt.doc", "\")
DocumentName = Chunks(UBound(Chunks()))
0
votes

You can use the FileSystemObject to quickly get the filename part

EDIT - good suggestion from BitAccesser

Requires a reference to Microsoft Scripting Runtime, or you need to use the CreateObject("Scripting.FileSystemObject")

Const WORD_TEST_PATH    As String = "C:\users\admin\test\test.doc"

Dim fso             As New FileSystemObject
Dim strWordFileName As String

strWordFileName = fso.GetFileName(WORD_TEST_PATH)
Debug.Print strWordFileName

Output is: test.doc

Just replace WORD_TEST_PATH with the filename returned from your Dialog path