Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing specialcharacters like []
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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").O nAction = _ ThisWorkbook.Name & "!macro_name" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing specialcharacters like []
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing specialcharacters like []
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing specialcharacters like []
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
On the "myName = " line, is that just a message post typo or is your
code missing a right parenthesis at the end? mike |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing specialcharacters like []
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing specialcharacters like []
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running macros in Excel filenames containing special characters like []
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using wildcard characters for filenames in externally linked files | Excel Discussion (Misc queries) | |||
Putting special characters into Excel... | Excel Discussion (Misc queries) | |||
Problem with running Macros | Excel Discussion (Misc queries) | |||
VBA PROBLEM - Running Macros! | Excel Programming | |||
Problem running macros in Office/Excel 97 | Excel Programming |