Excel mystery
-
How are the macros being called? If it is by a Ctrl code it may be a keyboard issue. There is no obvious reason why the Macro isn'r being sent, but it may be a upgrade issue. Is the Korean unit using Excel 2003 or 2007? Are the files being saved in the .xlsm format?
--------------------------------- I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave CCC Link[^]
The macros are being called using calls in the worksheets code. (so a control button is linked with event handling macro in the worksheet which itself uses code from modules, this is probably what you meant with ctrl code).
Dalek Dave wrote:
he Macro isn'r being sent
It is not that they are not being sent, it is that all code is removed when opening the file. The VBA project is completely empty. ( But for the spreadsheets modules, but they are empty, the code is gone). Korea uses Excel 2007, combined with WinXp. Devs uses Excel 2003, with WinXp. The release file is a excel 2003 .xls file. We however experience the same problem when the file is saved by the Devs in the Excel 2007 .xlsm file, which IMO excludes a problem with the excel version and/or security levels.
-
... in your coffee. :)
One of these days I'm going to think of a really clever signature.
-
So we are just facing a mystery here, maybe can someone throw some light on it. We have an 2003 Excel spreadsheet (format .xls) with macros, which is used in several locations in the world on Office 2007 without problem. But Korea. When they try and open the file, the modules just vanished, and there is no VBA code anymore. Two error messages appear : "Modules not found", and "Excel found unreadable content". Has anybody a clue ? (Dear hamsters, feel free to move this message, if not appropriate here. I am just trying to hit the mass, because I have no idea where to begin with).
~RaGE();
I think words like 'destiny' are a way of trying to find order where none exists. - Christian Graus Do not feed the troll ! - Common proverb
I have encountered situations where there are modules or DLLs that are not installed or are different between the versions of Office. Additionally VBA allows use of VB components available in VB6 such as MScomm and of course third party add ins.. When the VBA code is executed and the missing or incorrect module is called, Excel will strip the controls or modules from the project. This will happen as well with unregistered DLLs, OCXs and such. (I don't know why...) This, is in my experience, occurs when an Excel installation is a clean install of a newer version of Office with no prior versions as opposed to upgraded versions. (Had Office 2003 and installed 2010) It’s a VBA PITA. :mad:
It was broke, so I fixed it.
-
I have encountered situations where there are modules or DLLs that are not installed or are different between the versions of Office. Additionally VBA allows use of VB components available in VB6 such as MScomm and of course third party add ins.. When the VBA code is executed and the missing or incorrect module is called, Excel will strip the controls or modules from the project. This will happen as well with unregistered DLLs, OCXs and such. (I don't know why...) This, is in my experience, occurs when an Excel installation is a clean install of a newer version of Office with no prior versions as opposed to upgraded versions. (Had Office 2003 and installed 2010) It’s a VBA PITA. :mad:
It was broke, so I fixed it.
-
This sounds very likely. I was indeed looking at which add-ons and references and components are being required, and installed on both versions...
Rage wrote:
I was indeed looking at which add-ons and references and components are being required
I think you were on the right track then. One thing to note is once the controls, modules have been stripped, that spreadsheet is toast. Only a new working copy will replaced them. You may want to check the VBA versions of modules in use. Sometimes installing older VB runtime files can alleviate the issues. Good luck, hope your head doesn't get too bloody from bashing it on the keyboard. :thumbsup:
It was broke, so I fixed it.
-
Well get your a** in the office first, then!
I wanna be a eunuchs developer! Pass me a bread knife!
-
Well get your a** in the office first, then!
I wanna be a eunuchs developer! Pass me a bread knife!
-
On a serious note, is there any possibility to tell excel to use a specfic encoding ? I know that in browser rendering, but not in excel...
It used to be Data > Import in Excel before the ******* ribbon came along and made it possible only to create spreadsheets to handle storing your kids' heights to send them to Granny. I'll have a mooch; it's got to still be in there somewhere.
I wanna be a eunuchs developer! Pass me a bread knife!
-
It used to be Data > Import in Excel before the ******* ribbon came along and made it possible only to create spreadsheets to handle storing your kids' heights to send them to Granny. I'll have a mooch; it's got to still be in there somewhere.
I wanna be a eunuchs developer! Pass me a bread knife!
Gotcha: Excel Options > Advanced > Web Options (a button near the bottom) > Encoding
I wanna be a eunuchs developer! Pass me a bread knife!
-
Gotcha: Excel Options > Advanced > Web Options (a button near the bottom) > Encoding
I wanna be a eunuchs developer! Pass me a bread knife!
Dealt with that option recently, the settings changed in the Web Options panel in Excel have no effect on anything except the save as type Simple Web page and Web Page. in the save as dialog. If you want to import as a certain Encoding go to Data (in the ribbon) > From Text, it will ask for a file, and then the Text Import Wizard will open up, on the first page you can set the File Origin which is actually the file encoding.
-
So we are just facing a mystery here, maybe can someone throw some light on it. We have an 2003 Excel spreadsheet (format .xls) with macros, which is used in several locations in the world on Office 2007 without problem. But Korea. When they try and open the file, the modules just vanished, and there is no VBA code anymore. Two error messages appear : "Modules not found", and "Excel found unreadable content". Has anybody a clue ? (Dear hamsters, feel free to move this message, if not appropriate here. I am just trying to hit the mass, because I have no idea where to begin with).
~RaGE();
I think words like 'destiny' are a way of trying to find order where none exists. - Christian Graus Do not feed the troll ! - Common proverb
-
Have you tried converting the spreadsheet to 2007? It would require security changes but it may just work. I have a vbs script that adds trusted locations via the registry if you need.
Yes ( I mentioned that in my OP IIRC), and it did not help ( same behaviour, same error messages), both as .xls and as .xlsm. There was actually no necessary security changes, as everything was set on "allowed". Can you PM me your script ? This sounds interesting ! Thanks !
-
So we are just facing a mystery here, maybe can someone throw some light on it. We have an 2003 Excel spreadsheet (format .xls) with macros, which is used in several locations in the world on Office 2007 without problem. But Korea. When they try and open the file, the modules just vanished, and there is no VBA code anymore. Two error messages appear : "Modules not found", and "Excel found unreadable content". Has anybody a clue ? (Dear hamsters, feel free to move this message, if not appropriate here. I am just trying to hit the mass, because I have no idea where to begin with).
~RaGE();
I think words like 'destiny' are a way of trying to find order where none exists. - Christian Graus Do not feed the troll ! - Common proverb
-
Yes ( I mentioned that in my OP IIRC), and it did not help ( same behaviour, same error messages), both as .xls and as .xlsm. There was actually no necessary security changes, as everything was set on "allowed". Can you PM me your script ? This sounds interesting ! Thanks !
Sorry, didn't catch that in your orig post. Have you tried saving it as an xlsb (Binary Workbook) file or ,depending on what your code does, as an xlam (Add-In)? Here is the vbs script to add a trusted location to an office program
Dim WshWhell Set WshShell = CreateObject("WScript.Shell") 'Acceptable Office Version Values: 2003 = 11.0, 2007=12.0, 2010=14.0 'Acceptable Program Values: Excel,Access, Outlook, PowerPoint, Word, Visio. 'There is a "Common" program group that may possibly be used but I haven't tried it yet. 'The location ID is a unique value such as "LOCATION99" RegEdPath= "HKEY\_CURRENT\_USER\\SOFTWARE\\MICROSOFT\\OFFICE\\(Office Version)\\(Program)\\SECURITY\\TRUSTED LOCATIONS\\(LocationID)\\" WshShell.RegWrite RegEdPath , "" WshShell.RegWrite RegEdPath & "PATH" , "C:\\MyTrustedPath" WshShell.RegWrite RegEdPath & "Description" , "Description" WshShell.RegWrite RegEdPath & "AllowsubFolder" , 1 , "REG\_DWORD" -
Custom installed, yes. Left out VBA support, no, they have been using Office successfully for the last two years, so it must be something in the file.
Rage wrote:
have been using Office successfully for the last two years
A little unclear - does that mean they have been using other Excel VBA modules? If yes, I would suggest getting a copy of one that works and look for differences. If they have been running Office, but not using VBA modules, you might try reinstalling office.
-
Rage wrote:
have been using Office successfully for the last two years
A little unclear - does that mean they have been using other Excel VBA modules? If yes, I would suggest getting a copy of one that works and look for differences. If they have been running Office, but not using VBA modules, you might try reinstalling office.
They have been using MS Excel for two years with other workbooks without problems. They have only issues with one particular workbook, that they have tested on six machines, and which fails on all of them with the symptoms I explained. This workbook is actually a tool; the version we have sent has been completely reworked when compared to the last version one year ago, so it has almost nothing to do with it anymore. As a side note, the new version of the workbook is working properly in USA, Japan and China.