Script to change the filename of an Excel FIle

JHNielson

I posted this in the Access forum, but I think the answer may be a VBA script, so I thought I would also post this here...


I have an Access database I am importing records into from an Excel file. I currently have set it up by creating a linked table to the Excel sheet. But I am not sure that is the best way to do what I'm trying to achieve. So, I have two questions:


1) The user will be copying all the Excel files into one folder (this is just how I currently envision it). Is there a way to copy records from all those files in the folder, without having to have the user name them all the same.

For example: If there is a file named "Test.xls" and "Test2.xls" can I get it to add both those files' records to the table?

2) Can I rename the files that have been imported do that I can mae a check so that I know which files hvae been already processed. If I can't do the task i described in #1, then I could do this one one file at a time. This is the more important of the two

For example: Once he has imported "Test.xls" - can I change it to "Test - Imported.xls"

Thanks in advance for any help

Mar 28 '07 #1

Killer42

In VBA you can easily loop through the files which match a particular wildcard,. For example, "Test*.xls". I suggest you look up how to use FileSystemObject. There's also the built-in Dir() function, which is simpler to use but provides less functionality.

You don't necessarily need to link them. Your VBA code can use Excel to open each file (do some searching, the use of Excel from VB/VBA has been covered quite a bit on TheScripts) and read the info from it.

As for renaming, that's also easy in VBA. It's as simple as Name "Test.xls" As "Test - Imported.xls" (of course you would normally be substituting variable names in there, not hard-coding the name).

Mar 29 '07 #2

vijaydiwakar

Try to use filesystem object

Mar 29 '07 #3