467,920 Members | 1,119 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,920 developers. It's quick & easy.

Script to change the filename of an Excel FIle

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
  • viewed: 1341
2 Replies
Expert 8TB
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
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.

Similar topics

reply views Thread by Info | last post: by
3 posts views Thread by zweng | last post: by
2 posts views Thread by grozanc | last post: by
2 posts views Thread by vedrandekovic | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.