How to get the filename of a file just downloaded using VBA

[peskywinnets]

I've a pressing requirement to rename a file that has just been downloaded by the user ...but which will have a filename that is not known prior (& forever changing e.g. 10985693759384544.txt ).

I know how to rename a file using VBA, but what I don't know is how to identify the file that has just been downloaded in a given directory.

So I need a command that identifies the .txt with the most recent timestamp in a directory (or the .txt that has a timestamp along the lines of Now()-1 second )

Top tips warmly received!

 

[Beetle]

You may find this thread helpful.

 

[MrHans]

Or take a look at this excellent thread from Ridders

https://access-programmers.co.uk/forums/showthread.php?t=294548

 

[peskywinnets]

Thanks guys ...my problem was I didn't know the command/utility in play to be able to search how to deploy it

I've gone with the code below (basically loop through all files in a directory & if there's one with a .txt extension that was created within the past 1 second, then that's the file that was just downloaded)...

Dim FSO As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Set FSO = New Scripting.FileSystemObject

For Each fsoFile In FSO.GetFolder("C:\Users\RS\Downloads\").Files
If FSO.GetExtensionName(fsoFile) = "txt" And DateDiff("s", FSO.GetFile(fsoFile).DateLastModified, Now()) < 1 Then

'rename bit goes here
End If
Next

 

[Mark_]

Two important questions;
1) What happens if there are more than one .txt files in that directory with you date/time range?
2) What happens if your machine runs slow and this code isn't called within one second?

It does not sound good that you would try to import a file without knowing its name. If you ONLY know what directory it will be in, will these be the ONLY files that will ever reside their?

 

[jdraw]

I agree with Mark --your parameters may need to be refined.

You might consider *.txt file with the Max(createdDate).

Is there any significance built into the digits of the sample file name?
Is there something within the file that relates to something you already know (uniquely)?

How many downloaded files do you get per unit of time?
When you process a file do you rename it uniquely and do you keep it in the same folder?

 

[peskywinnets]

Two important questions;
1) What happens if there are more than one .txt files in that directory with you date/time range?
2) What happens if your machine runs slow and this code isn't called within one second?

It does not sound good that you would try to import a file without knowing its name. If you ONLY know what directory it will be in, will these be the ONLY files that will ever reside their?

Thank for your input.

This code is intended to slot into some larger preceding code...the preceding code prompts the user to download the file from the remote server & "click ok once done" blah blah),

This is just for me & my colleague to use (so not being deployed in a massive corporation). There's very little in the download directory, therefore so long as we click ok in a reasonably timely manner (e.g. something like 120 seconds) the follow on code ought to identify will the downloaded text file just fine.

 

[Mark_]

If the preceding code prompts the user, does said code also know the file name? If so, I'd pass that to your process.

Just thinking how ugly this could get if you had a file with messed up info that shows as being fro 2087 and is read only. You keep re-importing the same file over and over.

Having had to work with a system that relied on a similar structure I learned you need to be very clear in what is going where when you don't have a hard coded name OR if the name has meaning. I'd make sure the directory is not used for anything else just to avoid issues.

 

[peskywinnets]

All valid points.

The file is downloaded via a browser, which dumps it into the PC's (default) download directory ...so I can't have a separate directory.

As I say, there's only me & my colleague use this...there's barely anything else in the download directory & with only one of us using the PC at any one time, there'll only be one txt file that has been downloaded that meets the criteria.

Also the follow-on code runs a saved import ...which would barf if the wrong file was chosen.

 

[Beetle]

...There's very little in the download directory, therefore so long as we click ok in a reasonably timely manner (e.g. something like 120 seconds) the follow on code ought to identify will the downloaded text file just fine.

Not if your code is looking for files that were last modified within the last second;

If FSO.GetExtensionName(fsoFile) = "txt" And DateDiff("s", FSO.GetFile(fsoFile).DateLastModified, Now()) < 1 Then

 

[peskywinnets]

Not if your code is looking for files that were last modified within the last second;

If FSO.GetExtensionName(fsoFile) = "txt" And DateDiff("s", FSO.GetFile(fsoFile).DateLastModified, Now()) < 180 Then

Agreed ...I changed it to 180 seconds (that allows for even my slowest day at confirming)