Rename sheet to match name of an imported file

Hello, I have what I hope is an easy problem to solve. I have large quantities of .csv files that I want to import into excel. Each file will be imported to a different worksheet within the same workbook. I have written a macro that will mass import these files.

Here's the problem:

I would like for each new sheet that is created to be renamed to match the name of the incoming file. For example, if I import files named "Jan2010.csv", "Feb2010.csv", and "Mar2010.csv" each to different worksheets, I would like for the sheets/tabs to be renamed "Jan2010", "Feb2010", and "Mar2010" respectively.

Thanks in advance for any advice. I've been beating my head against the keyboard for months now.

Hello Jackets,

Welcome to the Forum!

It would help to see the macro code. If you could post it, it will be easier and faster to answer your question. Please use the code tags (See below my signature) when posting the code. It makes it easier to read and copy.

Hello Jackets,

This should work. The macro uses Regular Expressions to parse out the file name without the extension. The sheet is then named using this string.

Please Login or Register  to view this content.

Great! That seems to work with one minor correction

The end of the WITH statement should be:

ActiveSheet.Name = RegExp.Replace(fname(X), "$2")

and not

ActiveSheet.Name = RegExp.Replace(fname(1), "$2")


Here is the code that is now working for me:

Please Login or Register  to view this content.

I'm not exactly sure how you did it but it is working and that meets my need. Thank you! Mark this one as solved!

Hello Jackets,

Oops! Forgot to change that after I finished testing the parsing. Glad you caught that, thanks.