Add FileName to Field from Input box

[laman]

This may be a tough one but from what I've seen in this Forum, I'm sure someone can help. I'm using the following code to import a text file into a table using an Inputbox to get the file (Full Path).

Dim Message, Default, MyValue As String
Dim Title
Message = "Enter the File name including drivepath"
'Set Prompt
Default = ""
MyValue = InputBox(Message, Title, Default)
DoCmd.TransferText acImportFixed, "temp", "Temp1", MyValue

The Table has a field for the Filename. I would like to add some code to get the Filename from the InputBox (Not the full path) and populate that field for all the imported records.

Thank You!

 

[cmmrfrds]

I think you can get what you need from this example.

Dim mypath, myname, myfile(15)
Dim indx As Integer, pathname As String, pathPrefix As String
'--- Load the files from the directory into an array
mypath = "C:\edi\850in\imports\*.txt"
pathPrefix = "C:\edi\850in\imports\"
myname = Dir(mypath, vbDirectory)
'-- Get the 1st file
indx = indx + 1
myfile(indx) = myname
'-- Load remaining files
Do While myname <> &quot;&quot;
myname = Dir
If Not (myname = &quot;&quot Then
indx = indx + 1
myfile(indx) = myname
End If
Loop

 

[laman]

I'm not sure how to use what you gave me. Need more help.
The path of the files I import varies thats why I use the inputbox.

 

[GComyn]

Here is a function that will return the filename from the path received:

'===================================================================
'Function: GetFileNameFromPath
'Description: Returns the file specified in a full path
'Where to place code: Module
'Notes: Set strPath to the string containing the full path
'
'[URL unfurl="true"]http://www.littleguru.com[/URL]
'===================================================================

Public Function GetFileNameFromPath(strPath As String)
    Dim intX As Integer
    Dim intPlace As Integer
    Dim intLastPlace As Integer
    
    intLastPlace = 0
    
    For intX = 1 To Len(strPath)
        intPlace = InStr(intLastPlace + 1, strPath, "\")
        
        If intPlace = 0 Then
            GetFileNameFromPath = Right(strPath, Len(strPath) - intLastPlace)
            Exit Function
        Else
            intLastPlace = intPlace
        End If
    Next 'intx
End Function

hope this helps...

GComyn