Help! Bug-esque code behaivor on Windows 2K Server & XL2K

[Mike Mertes]

Hi there,

I'm having two problems today.

Firstly, when I call Workbooks.Open "#8321-2.xls", Excel opens the correct
workbook but appends a number onto the end of it's name. The first time that
line of code is executed I end up with an open workbook named,
"#8321-21.xls." After I close it and execute that line again, the result is,
"#8321-22.xls." And the third time, "#8321-23.xls" And so on, and so on.

I am absolutely positive that the correct file name is "#8321-2.xls." When I
open the file manually Excel DOES NOT append arbitrary numbers to the file
name. Also, this same bit of code successfully opened about 400 workbooks
previous to this one, but the error ONLY occurs on this particular file
name.

Is this an Excel bug or what?!

Secondly, in this same project there is a line of code that's something like
this, "FileSystemObject.MoveFile SourcePath\FILENAME~1.xls, DESTINATION."
This worked successfully until it encountered a file name with a tilde in
it. It appears that VBA interprets the tilde, "~," as a wildcard character
even though it's in a string. Literally: fs.Movefile "MyFile~1.xls"

How is this possible? And, is there a way to disable it, or change the wild
card character? I think it would be pretty ridiculous to set up a wildcard
character that naturally occurs in file names... But, it really looks like
that's what's happening.

Thanks for any help you can offer!

 

[Dick Kusleika]

Mike

I read your later message, so I hope you can read this.

Firstly, when I call Workbooks.Open "#8321-2.xls", Excel opens the correct
workbook but appends a number onto the end of it's name. The first time that
line of code is executed I end up with an open workbook named,
"#8321-21.xls." After I close it and execute that line again, the result is,
"#8321-22.xls." And the third time, "#8321-23.xls" And so on, and so on.

I am absolutely positive that the correct file name is "#8321-2.xls." When I
open the file manually Excel DOES NOT append arbitrary numbers to the file
name. Also, this same bit of code successfully opened about 400 workbooks
previous to this one, but the error ONLY occurs on this particular file
name.

Is this an Excel bug or what?!

When you open a template, Excel makes a unique name by appending an integer
to the end. It sounds like you're using Workbooks.Add instead of
Workbooks.Open.

Secondly, in this same project there is a line of code that's something like
this, "FileSystemObject.MoveFile SourcePath\FILENAME~1.xls, DESTINATION."
This worked successfully until it encountered a file name with a tilde in
it. It appears that VBA interprets the tilde, "~," as a wildcard character
even though it's in a string. Literally: fs.Movefile "MyFile~1.xls"

How is this possible? And, is there a way to disable it, or change the wild
card character? I think it would be pretty ridiculous to set up a wildcard
character that naturally occurs in file names... But, it really looks like
that's what's happening.

The tilde is a "negate wildcard" character. When you do an Edit > Find in
Excel, for example, and you want to find an asterisks (a wildcard
character), you precede it with a tilde. Since MoveFile allows you to use
wildcards, the tilde is part of the package. Use two tildes (~~) to mean a
literal tilde.

See also http://www.dicks-blog.com/excel/2004/05/finding_wildcar.html