Is it a bug? in VBA, FileName (with "[", "]") changes after opene

[iop]

in Excel VBA macro, I found:
here is a file named "abc[def]gh.xls" in folder_A,
1. Code1:
for each objFile in folder_A.files
when objFile refers to this file, its Name property objFile.Name =
"abc[def]gh.xls", which is correct.
2. Code2：(if we open this file, its name changes.)
set objFile = application.open(objFile)
when objFile refers to this file, after running this code, objFile.Name =
"abc(def)gh.xls", here, "[" and "]" have changed to "(" and ")".

I've check it in both excel 2003 and 2007, the same result.

Is this a bug? or on some intended purpose?

thanks!

 

[Peter T]

Not sure if it's a bug or VBA trying to being helpful.
I tried to manually to SaveAs "abc[def]gh.xls" and got a message telling me
(in effect) the following characters are illegal
< > ? [ ] : | or *

Some of those characters are indeed illegal in any filename and it's not an
exhaustive list. However it is possible to save (say) a text file with
square brackets or rename an xls with them.

I notice if I open an xls that I had renamed to include [ & ], Excel's
window caption shows square brackets but in the VBE window captions renamed
( & ), returning it's name in VBA gives round brackets.

Regards,
Peter T

 

[Dave Peterson]

I wouldn't use [] in filenames.

Excel will use those characters to indicate other workbooks in formulas. And if
your filename uses them, excel could get confused. I bet that's why excel tries
to protect itself by changing them to () when you open the workbook.

='C:\My Documents\Excel\scott\2007\[otherworkbooknamehere.xls]sheet99'!$A$1

I also wouldn't use # in filenames--it can confuse hyperlinks.

in Excel VBA macro, I found:
here is a file named "abc[def]gh.xls" in folder_A,
1. Code1:
for each objFile in folder_A.files
when objFile refers to this file, its Name property objFile.Name =
"abc[def]gh.xls", which is correct.
2. Code2：(if we open this file, its name changes.)
set objFile = application.open(objFile)
when objFile refers to this file, after running this code, objFile.Name =
"abc(def)gh.xls", here, "[" and "]" have changed to "(" and ")".

I've check it in both excel 2003 and 2007, the same result.

Is this a bug? or on some intended purpose?

thanks!

 

[iop]

thank you for ur reply!
--
cj

Not sure if it's a bug or VBA trying to being helpful.
I tried to manually to SaveAs "abc[def]gh.xls" and got a message telling me
(in effect) the following characters are illegal
< > ? [ ] : | or *

Some of those characters are indeed illegal in any filename and it's not an
exhaustive list. However it is possible to save (say) a text file with
square brackets or rename an xls with them.

I notice if I open an xls that I had renamed to include [ & ], Excel's
window caption shows square brackets but in the VBE window captions renamed
( & ), returning it's name in VBA gives round brackets.

Regards,
Peter T

in Excel VBA macro, I found:
here is a file named "abc[def]gh.xls" in folder_A,
1. Code1:
for each objFile in folder_A.files
when objFile refers to this file, its Name property objFile.Name =
"abc[def]gh.xls", which is correct.
2. Code2if we open this file, its name changes.)
set objFile = application.open(objFile)
when objFile refers to this file, after running this code, objFile.Name =
"abc(def)gh.xls", here, "[" and "]" have changed to "(" and ")".

I've check it in both excel 2003 and 2007, the same result.

Is this a bug? or on some intended purpose?

thanks!

 

[iop]

thank u for the answer!
--
cj

I wouldn't use [] in filenames.

Excel will use those characters to indicate other workbooks in formulas. And if
your filename uses them, excel could get confused. I bet that's why excel tries
to protect itself by changing them to () when you open the workbook.

='C:\My Documents\Excel\scott\2007\[otherworkbooknamehere.xls]sheet99'!$A$1

I also wouldn't use # in filenames--it can confuse hyperlinks.

in Excel VBA macro, I found:
here is a file named "abc[def]gh.xls" in folder_A,
1. Code1:
for each objFile in folder_A.files
when objFile refers to this file, its Name property objFile.Name =
"abc[def]gh.xls", which is correct.
2. Code2：(if we open this file, its name changes.)
set objFile = application.open(objFile)
when objFile refers to this file, after running this code, objFile.Name =
"abc(def)gh.xls", here, "[" and "]" have changed to "(" and ")".

I've check it in both excel 2003 and 2007, the same result.

Is this a bug? or on some intended purpose?

thanks!