By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,213 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,213 IT Pros & Developers. It's quick & easy.

Script to change the filename of an Excel FIle

100+
P: 121
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
Share this Question
Share on Google+
2 Replies


Expert 5K+
P: 8,434
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
100+
P: 579
Try to use filesystem object
Mar 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.