Link to home
Start Free TrialLog in
Avatar of k heitz
k heitz

asked on

Characters in .xlsb filename cause macro association to be lost (Error 1004)

Hi Experts;
I'm working with an .xlsb file that has many macros defined.
Our users download the file and might or might not overwrite the previously downloaded file.
If they don't overwrite, this results in the downloaded file getting named filename (x).xlsb.
When this happens they are getting the 1004  'macro xyz not found' error.

I've found the error to be related to the filename with the following tests:
Filename: XY_abcdefgh17.xlsb - macros work as expected
Filename: XY_abcdefgh17(1).xlsb -

Run Time error '1004': Cannot run the macro 'XY_abcdefgh17(1)!MacroName.' The macro may not be available in this workbook or all macros may be disabled.

I've verified macros are enabled, and thru debug checked this line of code:
Application.Run ActiveWorkbook.Name & "!MacroName"
the 'ActiveWoorkbook.Name' = XY_abcdefgh17(1).xlsb.

Filename: XY_abcdefgh16-17.xlsb - same error 1004
Filename: XY_abcdefgh test.xlsb - same error 1004

My question is - Have I determined the correct cause? Is this really a filename issue?
If yes,
 - is there a list somewhere of what characters will cause this behavior? So far I've isolated hypen, parens, spaceband.
 - is there any way around this (besides trying to control customers downloading habits)?
If no,
 - any idea what else is going on? I've verified the excel options (enable macro with prompt, w/ & w/o admin privileges)
 - I've been able to determine it happens on excel 2010; 2016 so far.

Thank you for any advice/ideas.
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of k heitz
k heitz

ASKER

Thank you Bill. That works perfectly!