File Name Causes Runtime Error 1004

[Guest]

Using Excel 2003, an application asks the user for a .txt file to open using
the open dialog. After the file has been opened, the code changes the name of
the sheet using Sheets(1).Name = "Data".

This works fine most of the tiime but with some file names, it causes
Runtime Error 1004. An example of a name that causes the error is
"report_PO_11-3-2005_11_3_2005[1].txt". If I rename the file and take out the
brackets, it works OK.

Is there some way I can avoid this error so that the user doesn't have to
rename the file every time? These files are downloaded from a web site so I
don't have anty control over the names.

 

[Norman Jones]

Hi Robert,

Try something like:

Dim sStr As String

sStr = "report_PO_11-3-2005_11_3_2005[1]"

sStr = Replace(sStr, "[", "")
sStr = Replace(sStr, "]", "")

Sheets(1).Name = sStr