dealing with a new file name for import every day

[joeythelips]

Hi,
I import an excel file into access every day using vba.
My problem is that this file has a different name every day and rather than renaming the file, i was wondering if vba could automatically do this for me.
A colleague told me that i could save the file in a directory, then, after it is processed, have VBA move it to a new directory. Then it does not matter what the name is, just have VBA process each file in the directory.
Unfortunately he isn't sure how to do this so if anyone had some code for me i'd be very grateful.

Thanks for your help.

 

[jgoodman00]

I found the following in the MSDN library, & it should be adaptable to do what you need it to do. If you let this run it will return each file within a specified directory, & by modifying it slightly you could add the code within the loop to actually import any excel files it encounters & then copies them to another directory before finally killing the excel file which it imported...




' Display the names in C:\ that represent directories.
MyPath = "c:\" ' Set the path.
NewPath = "c:\Old" 'Set the path for used files
MyName = Dir(MyPath & "*.xls", vbNormal) ' Retrieve the first entry.
Do While MyName <> &quot;&quot; ' Start the loop.
MyName = Dir
DoCmd.TransferSpreadsheet acImport, MyName, True
FileCopy MyPath & MyName, NewPath & MyName
Kill MyPath & MyName
Loop



Hopefully something along these lines will do what you need....
James Goodman
j.goodman00@btinternet.com

 

[MichaelRed]

Your question lacks a detail or two. If the File you 'receive' the smae name each day? If so, you could just rename it (perhaps use the date as a postfix?) and then always import the same file ever day. If (again) you receieve the fiel with the same moniker -but do not need to retain it- you could (again) import the same file(name) each day, and (after V&V on the content) just delete it.

If the file you receieve has a different name each day -AND the name varies in a systematic manner-, you could generate the daily file name from the systematic variation and just import what was sent No file [Murder | Alaising].

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over