Import a text file with a name that changes

[jmersing]

I need to set up an import from a text file into an Access table. Usually I would just create the link or use a transfer text macro. But my problem is that the name of the file will change every day. It will always begin with the date, after that it the name is somewhat random.

Any ideas?

Thanks

 

[mdmilner]

Yes - your exact approach will vary based on how reliable your file naming situation is. If it's *always* named like you mention, then you can open up the folders and extract items that match your criteria and import them. If it may or may not...or if it's the result of a human naming it...or if maybe you just want to do it this way, you can open up the windows API File Open Dialog window and pick which file you want manually.

After that, the rest is easily automated.

Take a look at:

http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=recallfilelocation

Something else you can also try is this using the filesystemobjects.
You'll need to set a reference to the MS Scripting Runtime.

Function rAppObject()
Dim fsoSysObj As FileSystemObject
Dim fdrFolder As Folder
Dim fdrSubFolder As Folder
Dim filFile As File
Dim strPath As String

strPath = UCase("C:\path\to\a\folder\")
'strPath = path_to_file_in_double_quotes

Set fsoSysObj = New FileSystemObject
    'Set fs = CreateObject("Scripting.FileSystemObject")
    'Set f = fs.GetFile(filespec)
Set fdrFolder = fsoSysObj.GetFolder(strPath)

For Each filFile In fdrFolder.Files
    Debug.Print filFile.DateLastAccessed & " " & filFile.Name
Next

End Function

Copy this code segment into a module and then play around and substitute items for the above ".DateLastAccessed". You can look at the various properties. There's a lot of useful things in here. This was just a demo function wrote myself long time ago as "notes" on how to do this.

Mike

 

[jmersing]

Thank so much Mike, that will really help me out. I appreciate it.