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*ntrols("Btn_Name"). OnAction = _
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.

" 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.

" 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