Problem running macros in Excel filenames containing special characters like []

My workbook embeds my own toolbar linked with some VB code. When I
download this workbook from the internet, IE asks whether to Open, Save
or Cancel the file. When I choose Open, IE creates a temporary file
adding "[1]" in the file name. On opening, Excel asks me whether I want
to activate macros. I choose yes. Now when I press one of my toolbar
buttons, excel asks me again to enable the macros. Choosing yes again
puts the file in a deadlock and the VB code behind the toolbar button
failed to execute. As soon as I save the file without using "[]" in the
filename, the macros work fine.

Does anybody have an idea how to work around this problem?

Thanks in advance.

Reto

It sounds like your toolbar icons are calling your macros from the other
workbook.

You may want to build your toolbar on the fly (create it when you open the
workbook and delete it when you close the workbook).

Here's how I do it:
http://groups.google.co.uk/groups?th...5B41%40msn.com

If you want to add items to the worksheet menu bar, you can use John
Walkenbach's menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm

"[email protected]" wrote:
>
> My workbook embeds my own toolbar linked with some VB code. When I
> download this workbook from the internet, IE asks whether to Open, Save
> or Cancel the file. When I choose Open, IE creates a temporary file
> adding "[1]" in the file name. On opening, Excel asks me whether I want
> to activate macros. I choose yes. Now when I press one of my toolbar
> buttons, excel asks me again to enable the macros. Choosing yes again
> puts the file in a deadlock and the VB code behind the toolbar button
> failed to execute. As soon as I save the file without using "[]" in the
> filename, the macros work fine.
>
> Does anybody have an idea how to work around this problem?
>
> Thanks in advance.
>
> Reto

--

Dave Peterson

Thanks, that might be a good workaround.
But the core issue is that if you have an Excel sheet with [] in the
filename, toolbars that call macros do not work anymore.
You can try that out easy by creating your own toolbar, link it to a
macro, save the file, rename it to something like myfile[1].xls and
then open it and try again...

I had a similar problem that appeared whenever the workbook was
renamed. I resolved this by assigning the macro within Visual Basic as
in the following snippet:


CommandBars("toolbar_name").Controls("Btn_Name").OnAction = _
ThisWorkbook.Name & "!macro_name"

I created a file with macros and saved it as book1.xls.

I closed excel and used windows explorer to rename the file to book[1].xls.

I reopened that book[1].xls file and excel's caption showed

microsoft excel - book[1].xls

But inside the VBE, excel was treating the filename as: book(1).xls.
(both in the project explorer and debug.print thisworkbook.name)

So I think the best solution is not to use those []'s in the file name
(difficult to do if MSIE is doing the naming!).

You may want to make sure your temp folder is empty as well as the temporary
internet files are empty. (Maybe MSIE won't bother adding [1] to the file name
if there's no existing file with the real name (just a guess).

Alternatively, I think I'd just save the file in MSIE using a name excel likes.

Good luck,

Remy wrote:
>
> Thanks, that might be a good workaround.
> But the core issue is that if you have an Excel sheet with [] in the
> filename, toolbars that call macros do not work anymore.
> You can try that out easy by creating your own toolbar, link it to a
> macro, save the file, rename it to something like myfile[1].xls and
> then open it and try again...

--

Dave Peterson

Mike,

I do that already but the VB runtime seems to have a problem when a
macro is referenced within a filename containing such special
characters.

Reto

After reading Dave's response, I see the problem differently. If the
present concern is the brackets in the filename, why not store
"ThisWorkbook.Name" to a string variable and then use string functions
to replace brackets with parenthesis? My snippet becomes

CommandBars("toolbar_name").Co=ADntrols("Btn_Name").OnAction =3D _
strWorkbookName & "!macro_name"

I haven't tried this but I see no reason why it shouldn't work.

I tried it and it didn't work for me:

Dim myName as string
'....

myName = Replace(Replace(ThisWorkbook.Name, "(", "["), ")", "]")

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = myName & "!" & mac_names(i)

It still tried to open a different file. And when it did, it looked like there
were two files open with the same name--and xl2003 sometimes crashed (and
sometimes not) when I tried closing one of them.


mike wrote:
>
> After reading Dave's response, I see the problem differently. If the
> present concern is the brackets in the filename, why not store
> "ThisWorkbook.Name" to a string variable and then use string functions
> to replace brackets with parenthesis? My snippet becomes
>
> CommandBars("toolbar_name").Co*ntrols("Btn_Name").OnAction = _
> strWorkbookName & "!macro_name"
>
> I haven't tried this but I see no reason why it shouldn't work.

--

Dave Peterson

There is another thread which is related to this problem. It sais that
[x] are only added by IE if you transfer the file using binary writer.
What they recommend is to make the file as direct link available. The
problem here is that it opens the Excel workbook directly in the
browser rather than the app. Any suggestions?

http://groups.google.com/group/micro...d2561111da0b8b

Not from me. I don't speak that kind of scripting.

Maybe someone else will chime in.

"[email protected]" wrote:
>
> There is another thread which is related to this problem. It sais that
> [x] are only added by IE if you transfer the file using binary writer.
> What they recommend is to make the file as direct link available. The
> problem here is that it opens the Excel workbook directly in the
> browser rather than the app. Any suggestions?
>
> http://groups.google.com/group/micro...d2561111da0b8b

--

Dave Peterson

On the "myName = " line, is that just a message post typo or is your
code missing a right parenthesis at the end?

mike

This portion?

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = myName & "!" & mac_names(i)

I think it's ok as-is.



mike wrote:
>
> On the "myName = " line, is that just a message post typo or is your
> code missing a right parenthesis at the end?
>
> mike

--

Dave Peterson

I found a solution which does workaround this problem. On Workbook_Open
a copy of the workbook with [, ] replaced by (, ) in the filename will
be saved. The "Save As" will automatically close the original file and
keep the new one with the modified filename open. After saving the copy
the original workbook containing [, ] in the filename will be deleted.
As a result, the loading of a workbook containing [,] in the filename
takes a bit longer but the opening process behaves way as if the user
opens a regular workbook.

Here the code:

Sub Workbook_Open()
Dim oldFileName, curFileName, newFileName As String

' Read current filename and replace [, ] through (, )
' This is required because toolbar buttons wouldn't work
curFileName = GetFileName()
newFileName = Replace(Replace(curFileName, "[", "("), "]", ")")

' If file contained [], replace them with ()
If (Not curFileName = newFileName) Then
' Create and open the same file without []
Excel.ActiveWorkbook.SaveAs (Excel.ActiveWorkbook.Path & "\" &
newFileName)
End If

' Remove the old file containing []
oldFileName = Replace(Replace(newFileName, "(", "["), ")", "]")
If (Not oldFileName = newFileName) Then
oldFileName = FileSystem.Dir(Excel.ActiveWorkbook.Path & "\" &
oldFileName)
If (oldFileName <> "") Then
FileSystem.Kill (Excel.ActiveWorkbook.Path & "\" &
oldFileName)
End If
End If
End Sub

Thanks for posting--now google has it for anyone who needs it.

But it maybe easier to just rightclick and saveas a nice name <vbg>.

"[email protected]" wrote:
>
> I found a solution which does workaround this problem. On Workbook_Open
> a copy of the workbook with [, ] replaced by (, ) in the filename will
> be saved. The "Save As" will automatically close the original file and
> keep the new one with the modified filename open. After saving the copy
> the original workbook containing [, ] in the filename will be deleted.
> As a result, the loading of a workbook containing [,] in the filename
> takes a bit longer but the opening process behaves way as if the user
> opens a regular workbook.
>
> Here the code:
>
> Sub Workbook_Open()
> Dim oldFileName, curFileName, newFileName As String
>
> ' Read current filename and replace [, ] through (, )
> ' This is required because toolbar buttons wouldn't work
> curFileName = GetFileName()
> newFileName = Replace(Replace(curFileName, "[", "("), "]", ")")
>
> ' If file contained [], replace them with ()
> If (Not curFileName = newFileName) Then
> ' Create and open the same file without []
> Excel.ActiveWorkbook.SaveAs (Excel.ActiveWorkbook.Path & "\" &
> newFileName)
> End If
>
> ' Remove the old file containing []
> oldFileName = Replace(Replace(newFileName, "(", "["), ")", "]")
> If (Not oldFileName = newFileName) Then
> oldFileName = FileSystem.Dir(Excel.ActiveWorkbook.Path & "\" &
> oldFileName)
> If (oldFileName <> "") Then
> FileSystem.Kill (Excel.ActiveWorkbook.Path & "\" &
> oldFileName)
> End If
> End If
> End Sub

--

Dave Peterson

It actually does make a lot of sense, cause Internet Explorer does
these [] parenthesis quite often.
Reto you're the man!

Remy Blaettler
Chief Software Architect
www.collaboral.com