Want to automate data upload process

[SKV]

TOBE system I am looking for.
1) User will place the file (to be uploaded) in a specific folder
Constraint - File may have different name each time, but there will be only one file in the folder at a given time.
2) Macro should read data from this file and upload into the system

Problem facing - Using Transfersheet functionality but since my file name is different every time, the macro is not able to find the file. How can I overcome this problem and make macro to pick file with file name consideration.

Ideally I want the system to work in a way that any time someone drops file into this folder, system should read the file automatically (FIFO) and then move this file to some other folder and picks the next one in line. Is it possible?

any pointers will help
SKV

 

[Joe4]

I have done something in the past, and gotten around it using a BATCH file.

What my batch file does is rename the file to some static name, then calls (opens) the Access database which automatically runs using that static file name.

Similarly, you could rename the file with some VBA code right inside Access and skip the whole BATCH file altogether.

 

[SKV]

Can you please provide the code to rename the file?

Thanks
SKV

I have done something in the past, and gotten around it using a BATCH file.

What my batch file does is rename the file to some static name, then calls (opens) the Access database which automatically runs using that static file name.

Similarly, you could rename the file with some VBA code right inside Access and skip the whole BATCH file altogether.