File Rename within Access (1 Viewer)

[Grandchester]

Is it possible to rename files within in a directory with sequential filenames form within Access? Example:
rename all files in a directory to-
file1.csv
file2.csv
file3.csv
etc...
Thanks,

 

[Atomic Shrimp]

Do the files already exist in the directory or do you want to name them sequentially as you export them each time, There is a reasonably easy (but very different) solution either way.

Mike

 

[Grandchester]

There will be multiple numbers of files when the exporting takes place. It is not a set number, it could be anywhere from 30-60 files. What I need to do is import them into an Access db without having to specify a filename (wildcard), however I have not found a way for the wildcard to work when defining a filename. I have decided to try and have a common filename for all the files so importing would be more streamlined. If ou could help me with any portion of this, I would be greately appreciative. Thanks,

 

[Atomic Shrimp]

to import a whole bunch of files using wildcards, the best method I have found is to create a folder 'done' within the folder that they are stored, then use a bit of code like this:

Dim Filename as String
While Dir("C:\Mydir\*.*") <> ""
filename = Dir("C:\Mydir\*.*")
DoCmd.TransferText acImportFixed, "MySpecificationName", "MyDestinationTable", "C:\mydir\" & filename
'NB the above might be TransferSpreadsheet or TransferDatabase in your case
DoEvents
Name "c:\mydir\" & filename As "C:\Mydir\done\" & filename
DoEvents
Wend

This way, files are moved into the 'Done' subfolder after importing and you don't have to worry about the possibility of importing the same file twice, or missing one.

to export files with sequential filenames, I use a table called 'System' - in this table, I set up an autonumber field called 'Instance' and other fields such as 'NextFileNumber' (enter that number 10001 here - you'll see why later), you can use a table like this to save various system defaults such as default file search paths, system name etc, but anyway, I then use code like this to ensure that the file exported has an unique name each time:

DoCmd.TransferDatabase acExport, "dBase III", "C:\MyOutDir\", acTable, "MyTable", "FILE" & Right(Str(DLookup("[fgn]", "[system]", "[instance] = 1")), 4)
DoCmd.RunSQL "UPDATE system SET system.NextFileNumber = [NextFileNumber]+1 WHERE (((system.Instance)=1));"

This exports a file called FILE0001.dbf (it uses the last 4 digits of the NextFileNumber value in the system table, then it updates the number so that the next time it is run, the file name will be FILE0002.dbf and so on...

(the reason for the autonumber in the system table is that, if somebody fiddles and adds a row, the DLookup will always look at the values in row 1)

Hope this helps

Mike

[This message has been edited by Mike Gurman (edited 01-30-2001).]

 

[Grandchester]

Thanks Mike. You have been extremely helpful. I appreciate your input.

 

[Grandchester]

The code imports all the files, but it is in an infinate loop. I haven't been able to break it. Any thoughs?? Thanks

 

[MHM]

Hi Grandchester,

there is a very well to do way for this - yet it needs some programming. I've done it quite some times, but still it always needs to be tested well.

Solution: Use the fileSystem-object! (Look up in the object catalogue (->F2 in VBA-code)).
There is fileCopy(strSource, strDestination) - which does not rename a file but copy it.
Therefore you can use :
fileCopy strFileOldName, strFileNewName
kill strFileOldName

When you don't know, which files you need to rename, use the dir function:

Dim strFileName as String
strFileName=dir("C:\tmp\*.csv", vbNormal)
do
<here: Code for fileRenaming strFileName>
strFileName=dir()
until strFileName=""

Hope this helps - and is not too complicated.

Mic