PDA

View Full Version : Solved: "This File was created in a later version"



Ken Puls
03-03-2005, 11:29 AM
Hi guys,

This isn't strictly a VBA question, but I'll VBA it if I have to...

I build all my files in Excel 2003, and deploy them to my 97 users (after testing on their version first to ensure it is compliant.)

Since I put a new install of 97 on a brand new Windwoes 2003 Terminal Server, they keep getting prompted with a message saying something like "The File was created in a later version of Excel. If you would like to save it in this version you may lose some featrures... blah, blah, blah" :banghead:

Problem is, even if I say yes to save it in 97, it makes no difference. Next time they open the file, same message when they try and save. Does anyone know if there is a flag or setting somewhere that I can turn this off? I'm didn't get this message before when I was deploying files to a Windwoes 2000 Terminal Server.

Thanks,

Zack Barresse
03-03-2005, 11:32 AM
There should be a setting for this when using SaveAs. Change the file extension so it says Microsoft Excel 97-2003 Workbook & 5.0/95 Workbook (*.xls), or something to that effect. By doing that, does that help any? I don't have 97 to test it on, but that was the impression I was under.

Ken Puls
03-03-2005, 12:38 PM
Yep. Tried that. You'd think it would work, but no difference.

Jacob Hilderbrand
03-03-2005, 12:57 PM
Copy the sheets and code over to a new workbook on the 97 machine and save it seperately.

Ken Puls
03-03-2005, 01:05 PM
Gugh. It's happening on every single workbook I have ever created... like hundreds. They may just have to get used to it.

I wonder if it's a flag in one of my Novell ZenWorks Office 97 policies or something. Problem is, I've never seen a setting that says: "Warn me!"

Is there any way to code around it by building an Excel_BeforeSave event (or something) in a class module dropped into personal.xls? :dunno

Jacob Hilderbrand
03-03-2005, 01:09 PM
Try putting Application.DisplayAlerts = False in the Before Save Event. Though I am not sure that it will stop this message.

Ken Puls
03-03-2005, 01:13 PM
Okay, but that sounds a little dangerous, doesn't it?

Even if it does work, what's the trigger to turn it back on? There isn't a Workbook_AfterSave routine, right?

I supposed I could code my own before save that turns alerts off, saves, then turns them back on, but what do I need to do to get it to stop the save process? Just set Workbook.saved to true?

Jacob Hilderbrand
03-03-2005, 01:18 PM
Try this.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Cancel = True

Application.DisplayAlerts = False
Application.EnableEvents = False

ThisWorkbook.Save

Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Ken Puls
03-03-2005, 01:25 PM
Okay, slight variation to add the saveas functionality, but this still didn't work:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Application
.DisplayAlerts = False
.EnableEvents = False
End With

If SaveAsUI = True Then
ThisWorkbook.SaveAs
Else
ThisWorkbook.Save
End If

With Application
.DisplayAlerts = True
.EnableEvents = True
End With
Cancel = True

End Sub

The thing is, I put a break on the first line in the VBE, then click save. The prompt pops up before the Workbook_BeforeSave event is even triggered! Grrr! Any other thoughts?

Jacob Hilderbrand
03-03-2005, 01:30 PM
Well we could always change the save buttons on the toolbars themselves, but first try this.

Make a new macro that just saves the workbook with Alerts off and see if you get the warning. Also you don't need to worry about SaveAs since it will be shown if the workbook has never been saved automatically.

Option Explicit

Sub MySave

Application.DisplayAlerts = False

ThisWorkbook.Save

Application.DisplayAlerts = True

End Sub

Ken Puls
03-03-2005, 01:41 PM
Yes. This works.

So basically what I need to do then, is create a personal.xls file for my users then, save it as personal.xls, and add-in or just in the startup folder. This file will need to remove Excel's built in file save commands (all of them) and replace them with this one.

What all do we have?
-Toolbar buttons
-CTRL + S
-File menu
-What about the SaveAs command? If they hit that from the menu, they'll get the same prompting, right?

Only other issue is that I will need to recreate the errors that I may usually see, such as "File already exists", as this would just overwrite without prompting.

mdmackillop
03-03-2005, 02:03 PM
This any help?
http://support.microsoft.com/default.aspx?scid=kb;en-us;168203

johnske
03-03-2005, 02:04 PM
Hi Ken,

I have a similar problem where, for a project, I deliberately saved books used only to contain data in the smaller '95 w/books. I should get those messages by saving as a '95 workbook (I'm on 2k) but a variation on this works with no probs.Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\windows\Desktop\Book1.xls", FileFormat:=xlExcel5
Application.DisplayAlerts = True
End Sub

Ken Puls
03-03-2005, 03:09 PM
Hey guys!

Thanks for the link, Malcolm, but nope. This one only occurs when saving, and everything else works A-okay. I can still save, it's just a stupid prompt. I don't think it's corruption related.

John, I gave something like that a shot above, but this alert pops up before the Workbook_BeforeSave event is triggered. Even if I save the workbook into an Excel 5 format, it still gives me the error. I assume that the workbook must have some property, but don't have any more time to look right now.

Thanks for the effort, though. I think I may just build an add-in to work around the issue in the methods Jake worked out above.

Jacob Hilderbrand
03-03-2005, 04:37 PM
Try this.

Option Explicit

Sub SaveChange()

Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

With Application.CommandBars("Worksheet Menu Bar").Controls("&File")
.Controls("&Save").OnAction = "MySave"
.Controls("Save &As...").OnAction = "MySave"
End With
For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=3, recursive:=True)
If Not CmdCtl Is Nothing Then CmdCtl.OnAction = "MySave"
Next CmdBar

Application.OnKey "^s", "MySave"

Set CmdBar = Nothing
Set CmdCtl = Nothing

End Sub

Sub SaveReset()

Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

With Application.CommandBars("Worksheet Menu Bar").Controls("&File")
.Controls("&Save").OnAction = ""
.Controls("Save &As...").OnAction = ""
End With
For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=3, recursive:=True)
If Not CmdCtl Is Nothing Then CmdCtl.OnAction = ""
Next CmdBar

Application.OnKey "^s"

Set CmdBar = Nothing
Set CmdCtl = Nothing

End Sub

Sub MySave()

Application.EnableEvents = False
Application.DisplayAlerts = False

ThisWorkbook.Save

Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Then just run the SaveChange Sub on the Workbook Open/Activate Events and the SaveReset Sub on the Workbook Before_Close/DeActivate Subs.

johnske
03-03-2005, 05:49 PM
Hi Ken,

Also found this, but dunno whether it helps with your particular problem http://www.microsoft.com/resources/documentation/office/2000/all/reskit/en-us/68t2_1.mspx

John

Paleo
03-03-2005, 09:00 PM
Hi Ken,

I think you may find some answers here: http://office.microsoft.com/en-us/assistance/HP051985111033.aspx

JonPeltier
03-06-2005, 07:55 AM
Quick philosophical/rhetorical question for Ken:

If your users are using '97, why are you developing in 2003? This 'later version' issue is a relatively minor (if annoying) symptom of version backwards incompatibility. There are worksheet functions, Tools-Options settings, and VBA enhancements which can also cause problems. You should be always developing in ther earliest version your users have.

I enjoy working in 2003, but I step back to 2000 to develop projects for my clients. more than half are still using that version. I have told potential clients that I cannot develop in '97, simply because I don't want to develop in that version.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

XL-Dennis
03-06-2005, 09:00 AM
I have told potential clients that I cannot develop in '97, simply because I don't want to develop in that version.


Any particular reasons?

Except for not having the version available.

Kind regards,
Dennis

JonPeltier
03-06-2005, 12:47 PM
Dennis -

I do still have it, on the kids' old Windows 95 machine, which they don't even use anymore. Not as much fun to work on as it was eight years ago.

Office '97 was a huge advance over 95. Office 2000 was a small advance over 97, most notably being the jump from VBA5 to 6 and a great deal of stability. The later versions really don't have much meaningful improvement over 2000, except a little robustness, and a slicker appearance. Oh yeah, and XML.

Recent informal polls show that only about 5% of Office users are still using '97, while 40% or more are using 2000. I'm not greatly reducing my target audience by working in Excel 2000, while for me, 2000 has the bare minimum level of comfort and capability. Microsoft can push VSTO as much as they want, but I'll just push VBA 6 and Office 2000.

- Jon

XL-Dennis
03-06-2005, 01:34 PM
Jon,

If it wasn't for three clients to me I would happily uninstall both 95 and 97 ;)

Oh well, I'm happy that I could at least drop Excel 4.0 last year...



The later versions really don't have much meaningful improvement over 2000, except a little robustness, and a slicker appearance. Oh yeah, and XML.


I can agree on that except that the calculation-engine is more powerful in later version.

Someone told me that MS Outlook is the mainreason why many companies constantly upgrade their Office-version.

XML is yet another area where MSFT push really hard for but in my part of the world the good ol' textfiles (and also prn-files!!) are still in heavily use.

It must be the first time during the IT-evolution that a fileformat get so much attention like XML gets :banghead:

Kind regards,
Dennis

JonPeltier
03-06-2005, 01:47 PM
I've recently rediscovered how easy text files are to use with VBA's file I/O library. I use them to output information I need for debugging, to save certain parameters (instead of INI files or the Registry), for all kinds of things. As I do this I think, this is what XML is for, and some day I'll learn enough to use XML.

Outlook may be driving Office upgrades (I've heard that too), but several of my clients use Outlook 2003 while still using Excel/Word/Ppt 2000 or XP. My backup computer is Office 2000 only, except for Outlook 2003. The one client I had that was using Office 2003 was also using Lotus Notes, and they wanted me to automate that, ha ha; sure it's possible, but it's difficult to determine the object model, and it's very unstable.

XL-Dennis
03-06-2005, 02:00 PM
It's amazing how quick we tend to drop old techniques with new ones. Textfiles are still valid to store data in. In many of my VB-apps I use textfiles to store some data in while for Excel I usually use the worksheet-area.

XML is excellent for working with well and semi-structured data and personally I like it when it comes to handle data between DBMS and other softwares. But not to store small amount of data. Although the I/O-handle is rudimentary it's both, simple, fast and reliable.

Kind regards,
Dennis

Ken Puls
03-06-2005, 04:19 PM
Quick philosophical/rhetorical question for Ken:

If your users are using '97, why are you developing in 2003? This 'later version' issue is a relatively minor (if annoying) symptom of version backwards incompatibility. There are worksheet functions, Tools-Options settings, and VBA enhancements which can also cause problems. You should be always developing in ther earliest version your users have.

Hi Jon,

Welcome to VBAX, and thanks for taking the time to look at my question. Honestly, the reasons why I use 2003 are probably poor, but here they are. I work in a mixed environment. Everyone with a real PC runs 2003, and all my other users on the terminal server use 97. Because I have a real PC, I have 2003 installed. I like it better, and prefer to develop in it. I also have 2003 at home, so if I need to work at home on a file I can easily.

I do always Alpha test my files on the 97 platform before deploying though. (My users are very good about letting me know if something goes wrong, and I can usually debug it on the spot for them.)

What is strange here is that these files worked just fine in Excel 97 on a Win 2000 terminal server. We recently upgraded to a win 2003 terminal server, though, and that's when things started to go haywire. In fact, over the last couple of days, I've found more strange and wacky things happening as well. In fact, upon encountering a runtime error that had never before been encountered performing a specific task, my users were unable to even see the VBA project upon opening the VBE.

I am beginning to think that much of my issues are not related to my files, but rather with the installation on the server. I'm not even sure if 97 is supposed to work on a 2003 server at all, come to think of it, but I really don't have the $20k to upgrade it to a newer version or I would. :(

JonPeltier
03-06-2005, 04:31 PM
Ken -

I like using 2003 also, as I admitted. One thing I've discovered, is if a workbook is having corruption trouble in 2000, sometimes opening and saving it in XP or 2003 makes the trouble go away. Anyway, I wonder if it's possible to install 97 on your PC (or a "spare" one) for development, to avoid some of the problems.

Are your workbooks stored on the server? Do they carry out tasks on the network? Do they work fine with Excel 2003? Do you know if they work on Excel 2000 and the new network? (2000 might be a much cheaper intermediate upgrade path than a direct shoot to 2003).

- Jon

Ken Puls
03-06-2005, 04:50 PM
Hi Jon,

I can answer a yes to every question except for the Excel 2000 on the terminal server. That one I'm not sure of. I will look in to the potential for a 2000 upgrade path, although I wasn't aware that it was even possible now that 2003 is out.

I know that may seem like putting the cart before the horse, but with the terminal server MS specifically says not to install multiple office versions. So unless I want to uninstall 97, install 2000 and test, there's not really a way to find out. I'd rather know if I can even buy licenses before I try.

Thank you for the food for thought though!

JonPeltier
03-06-2005, 04:57 PM
Well, I'm exposing my ignorance.

When I suggested 2000, I was thinking as a small admin who might go onto eBay to find a cheap copy of an older version of the software. If you have many to do, and if you deal with site licenses for X seats, this may not be an option.

Also, I don't know anything about terminal server. MS also recommends not installing multiple versions on Office on a single computer, but I have three versions on this one, and things work with a minimum of problems. My ability to use custom chart types is defunct, but I don't use that anyway.

But as you said, food for thought.

- Jon

XL-Dennis
03-06-2005, 05:11 PM
Ken,

I would say that the issue are related to the configuration of the Windows 2003 server.

Please see if the following KB-article shed some lights over it and send it to the staff that are responsible for the server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q210231

Note: The KB-articles was referred from here
http://www.microsoft.com/windowsserver2003/community/centers/terminal/terminal_faq.mspx
which indicate that the issues are valid for Windows 2003 as well.

Kind regards,
Dennis

Ken Puls
03-06-2005, 09:39 PM
Hmmm... I think that my installers definately missed a step or two in the Office 97 install. (That's why I pay them the big bucks, right?)

I'm going to get my server guys to check out the article, and will let you know if it solved the issue. :yes

Ken Puls
03-17-2005, 05:27 PM
Hey guys,

Just an update on this for you...

I had my systems guys in a week and a half ago and we did a complete uninstall/reinstall of Office 97 on the terminal server. On the reinstall, we tried to run the scripts that Dennis had referenced, but they don't seem to work on 2003 server. We did, however, find a Citrix KB article that mentioned access rights to a specific .dll file were needed, so we set that up.

My issue with the "Workbook created in a later version" has dissappeared, however I am now left with the more serious problem that every time a user saves a file with VBA in it, (no matter where it was created,) the VBA332.dll file experiences a general protection fault. After that, if the user opens the file, it just kills the instance of Excel 2 seconds later. Since I've automated about 70-80% of our files, this is a pretty huge issue. The only way to fix it, which I have been doing, is to resave the file in 2003. It then works again until the next 97 save. :wot

I've been pouring over MS KB articles, newsgroups, etc... but can find very little on the subject that is of help. (We found one article that suggested deleting normal.dot, although why that would affect Excel, I don't know.)

My IT support contractors are coming in on Monday morning, and we have a support call scheduled with MS themselves to get it resolved one way or another. My biggest fear now is that I'm going to get the "Office 97 is not supported on 2003 terminal server, upgrade to Office 2003". As I mentioned before, $20,000 is a pretty big pill to swallow when you have no budget for it. An upgrade will get into next year's budget, but somehow I have to survive until then.: pray2:

Anyway, I'll let you know how it turns out.

Anne Troy
03-17-2005, 05:37 PM
Sure.....blame Word! Why not? Everybody else does!!

Ken Puls
03-17-2005, 05:43 PM
:rotlaugh:

Well that's sort of what I thought... "What does that have to do with Excel? Well... it is Word... I suppose it could happen!"

Seriously though, I told the guy it wouldn't make any difference. Needless to say, Word wasn't at fault (no pun intended)

JonPeltier
03-17-2005, 08:39 PM
Sure.....blame Word! Why not? Everybody else does!!

I like to blame the printer drivers.

Anne Troy
03-17-2005, 08:49 PM
Exactly. Thanks, Jon.

Ken Puls
03-17-2005, 10:21 PM
I like to blame the printer drivers.

Funny you should say that. I have another program which bombs after printing. Suspicion there is exactly that! :rotlaugh:

JonPeltier
03-18-2005, 04:58 AM
Funny you should say that. I have another program which bombs after printing. Suspicion there is exactly that! :rotlaugh:
In your case I'd suspect the video card.

Ken Puls
04-25-2005, 02:02 PM
My IT support contractors are coming in on Monday morning, and we have a support call scheduled with MS themselves to get it resolved one way or another. My biggest fear now is that I'm going to get the "Office 97 is not supported on 2003 terminal server, upgrade to Office 2003".

Well, I'm back fighting this thing again...

I did have my IT guys in. They scheduled a support call with some super-certified Citrix guy, and also one with MS as well. And naturally, I was unable to recreate the issues for them at the time. :banghead:

Even more naturally, my issues have resurfaced. :(

I've done some more extensive tests, and will replay a full scenario here. Some is just for my purposes of documenting it so I don't forget, but I also figured that someone just might see something that I can try...

To recap the environment:
-Office 97, SR-2
-Windwoes 2003 Terminal Server, running SP1 and Citrix Metaframe XPe FR3 SP4
-File rights maintained by Novell 6.5 SBS, and pused to users using ZenWorks
-Data is stored on a Storage Area Network (SAN) device
-Excel and VBA worked just peachy while on a Windows 2000 server with teh rest of the environment unchanged.

Tested Scenario:
I have an Excel template which uses a commandbutton to save the file under a new name into a different directory on the network.
-If I log in as a regular user, open the template, and click the button to save the file, it works. I then try to reopen the file, and it opens. I then watch it for 2 seconds and *poof*, Excel closes completely.
-If I let the user create the file, then log in as an admin user and open the saved file, I also get to watch it for 2 seconds and again, *poof*!
-If, however, I log in as admin, open the template and click the button, the file will again save fine. This time, though, I can open the saved file with no issue.
-If I have a user use the template and save the file, and then I open it on a Win XP machine (running Office 2003), it seems to open fine. If I save it, the Office 97 users can open it again.

It's almost like the file is being corrupted somehow during the save process. And yet, when I resave it in 2003, that seems to stop the issue from recurring with that file...

Upon logging in as admin, I see the "this program faulted please notify MS" box that is shown below. This box references VBA332.dll.

Now, this isn't the only issue, just the one I've extensively tested. I also have issues with one of my users who can't run a file that incroporates a piece of code that I am certain is Excel 97 compliant. It gives her a debug error, and when trying to debug tells her that she can't get access to the VBProject.

I also have some files that get stuck in design mode, when they were not saved with design mode active.

Attempted Actions:
A search of the net seems to turn up lots of stuff on Access, but nothing that appears helpful, and it is all pretty old. (It's like most people have upgraded their Office before moving to the latest version of Windows Terminal Server or something :dunno)

As mentioned in a prior post, we did try to do the "proper install" per XL-Dennis's link above, and also found a document from Citrix saying that there was some certain files that required specific access setup. We've done all of that with not much difference. Done the uninstall/reinstall thing more than once, from the server console, with the "change user/install" switch set as well. (Terminal server thing sfaik.)

I have tried giving my users "power user" status, but this does not seem to help either, and naturally, I have no interest in giving all of my users admin rights to the server. :eek:

I've search the registry, and it seems like everything is registered correctly there. There are about 4 keys referencing VBA332.dll, but they're under the Hkey_Classes_Root and Hkey_Local_Machine keys, so should be accessible by everyone. I have checked that they are indentical for regular users and administrators.

I have also granted read/write/modify rights for VBA332.dll for all users, and when that failed, gave them read/write/modify access to the entire Office directory.

I'm trying to figure out what special things the admin login has access to that the regular users don't (with reference to the VBA332.dll file).

So what have I missed here? Somewhere there is a rights difference that I can't find, but I'm not sure what it is.

Anyone have any guesses?

Ken Puls
07-09-2005, 10:46 PM
Just updating some progress on this thread...

I've been experiencing the same issues consistently now for another 2 months since last posting. Fortunately, however, I can now report a little progress.

Last Thursday, I finally rescheduled a call with Microsoft, and got on the phone with two Microsoft professionals. One of them is an Excel/VBA Support Engineer, and the other is a Windows Platform Support Engineer.

I was able to reproduce the errors for them, and have uploaded a few files to their servers for them to look at. They are now supposed to be getting back to me.

Fingers are crossed that something will come through on this.

Now for the part that is really strange... On June 30th, I created 3 new templates which used a routine to save a file. They work. :eek:

My mysteries keep deepening here, but I hope that I'll have them resolved soon...

Scottie P
07-09-2005, 11:19 PM
Cool. Hope you get this worked out and REALLY glad you could reproduce it on-demand [unlike taking your car to the garage when it is making some odd noise, but it stops the moment you pull onto the garage lot!]

Keep us posted!

Ken Puls
11-21-2005, 06:03 PM
Okay folks,

4 1/2 months since my last update, and still waiting on that call back from MS! :rotlaugh:

I've still been suffering under the disappearing excel instance (vba332.dll crash) and also the strange design mode issues.

Today I tried something new... It suddenly occured to me that everything seems to be linked to the ActiveX commandbuttons (from the Control Toolbox) that I have been using. All my routines have run from there, and the spreadsheets with issues all seem to have them. For whatever reason, I decided to switch it up to a forms button today. I'm not as big a fan of them, since you can't make them as pretty, but functionality is going to win out over looks here, I think.

So when I went to the VBE to make the changes to the routine names, I also made another interesting discovery... every file I looked at had a blank (no option explicit line) Module1 added to the VBE, where I had only used the sheet module for the command buttons. (Some of these routines are only 6 lines long.) I don't know why those modules were there, but they weren't created by me.

At any rate, I cut the macros out of the sheet modules, put them in the standard modules (under new names,) deleted the CommandButtons, and replaced them with Forms Buttons. Assigned the macros to them and saved the file. Next, I borrowed a terminal server user's station and ran through all the steps I'd detailed before. (Open, run code, save, close, reopen...) Everything seems fine...

I'm crossing my fingers that it's only taken me 7 months to work out the answer here... :banghead: (And I'm trying to figure out why I didn't at least look at forms vs ActiveX buttons before.) Currenlty in the monitoring stages, so will post an update once I know more.

Cheers,

XL-Dennis
11-22-2005, 05:37 AM
Ken,

Excellent news :)



I'm crossing my fingers that it's only taken me 7 months to work out the answer here... :banghead: (And I'm trying to figure out why I didn't at least look at forms vs ActiveX buttons before.) Currenlty in the monitoring stages, so will post an update once I know more.


It's too easy to judge when we got the whole picture clear to us. In my opinion You've done it well despite the 7 months. After all, there exist too many issues that so far have not find their final solutions.

Kind regards,
Dennis

Zack Barresse
11-22-2005, 09:27 AM
Yes, great to hear the good news Ken. And thanks for keeping the board appraised on the conditions of your spreadsheet. This is interesting indeed. Keep us informed. :yes

Ken Puls
11-22-2005, 10:07 AM
I don't know if it's good news yet guys... I only released my solution to the wild yesterday afternoon! ;)

I will keep you up to date though. :yes

Ken Puls
01-11-2006, 02:20 PM
Hey guys,

Almost 2 months since my last update, so here's another. I have some good news at last!

:dance:

Looking back at it now, I believe that I was dead on target when I finally hypothesized that this was an ActiveX issue. Now if anyone reads this and disagrees with what I am about to put forward, please let me know. I'm not a Windows server expert, by any stretch, but I've tried to pick up and write down as much as I can on this. My understanding may be off on some pieces though...

One of the big changes between the Windows Server 2000 and 2003 versions was security settings. Many of us would be more familiar with similar changes when XP was upgraded to SP2. You know that "Click here to allow ActiveX components to run" prompt you get in Internet Explorer on occasion? That would be a similar thing. My understanding is that Server 2003 has a bunch of security settings that are installed by default out of the box, and I believe that they were impacting my ActiveX objects in a nasty way. This may explain why I could create/save files using the admin profile, since admin generally has rights/powers to do everything and users don't. When the users would run my code, though, the file would be corrupted and therefore blow apart at next use, whether for a regular user or administrator. Why it would be resurrected by having a WinXP, Office 2003 install access and resave it, though, I can only guess at; I have heard that Office 2003 does a half decent job of repairing files.

In hindsight, the Excel disappearing act that I thought was solved on July 9, 2005, in light of the new information below was NOT a corrupted workbook issue. I moved all my files from workbooks with CommandButtons to templates controlled by a CommandBar Menu... avoiding the ActiveX controls. While the issues disappeared under this new structure for this set of workbooks, it continued for others, all of which were CommandButton laden.

As mentioned in my last post, switching from ActiveX CommandButtons to Forms button seemed to solve those issues, but I am now convinced that it actually just skirted it, with the underlying problem still there. I'm happy to say that I actually cannot test it, as my server works now, but I think I have enough evidence to support my conclusions. :)

In the beginning of December, I had a very well certified and knowledgeable Citrix expert come on property to document my server, upgrade it from Citrix Metaframe XPe to Metaframe Presentation Server 4.0, and build me another Citrix server using the same specs to add to my server farm. And it looks like this is one of those instances where you get what you pay for.

During the install of the second server, one of my users mentioned what I thought was an unrelated issue that they'd been having since our upgrade. Yes, the upgrade was 9 months prior, and they'd never mentioned it before!:banghead: This issue was that they couldn't use an ODBC tool to generate spreadsheets from one of our databases. I mentioned it to the tech in passing, and he casually toggled the checkbox for "Windows 2000 Compatibility"... I think that's what it was called. Lo and behold, my issues are gone!

All of the above has brought me to the conclusion that it was the ActiveX components that were the issue, as I know that this specific software also uses a ton of ActiveX components in their program as well. Why theirs worked and Office didn't, though, I'll never understand, I'm sure. LOL!

I'm not 100% sure what kicked off the original "Workbook created in a later version" problem, except maybe a poor office installation. The new server is not fully tested to date, but I will post back if it becomes an issue there. In the mean time, my existing server seems to run without issue.

I?d also mentioned a weird issue earlier in the thread where one of my users ran couldn?t get to the VBA Project. A quick summary of the process: All code is in an xlt file, which also holds all the data entry fields. (This file was a template to speed up journal entry inputting.) At the end of input, the user clicks a button (ActiveX) and it exports it to a text file for upload into the database, at which point the JE form is tossed away.

What was weird is that the file would open fine, but clicking the button came back with a runtime error about a ?Compile Error in Hidden Project?. The user could enter the VBE, and could see the project in the project explorer (one line only). They could not, however, expand the project to get to the code. It kept returning the error ?Project Unviewable?. After much experimentation, I was able to work out that this issue was due partly to the fact that I?d used the Round function in my code, which isn?t supported in 97. Before anyone else says it, that is exactly what John Peltier warned me of, and was totally my fault. I?ve fixed it up with the Int(100*expression)/100 route.

And the good news? The ActiveX CommandButton that calls the code works just fine now, thank you very much. No need for an ugly forms button any more.

:ole:

Oh! And by the way... I'm still waiting on that call back from MS?

XL-Dennis
01-12-2006, 03:56 AM
Ken,

Thanks for the update :)



All of the above has brought me to the conclusion that it was the ActiveX components that were the issue, as I know that this specific software also uses a ton of ActiveX components in their program as well. Why theirs worked and Office didn't, though, I'll never understand, I'm sure. LOL!


Here is the situation when it comes to ActiveX controls, especially third-party's.

ActiveX controls exists in two versions, one for the design and one for the runtime. The later is distributed with the solution. No Office-solution can be compiled like solutions developed with classic VB et al and therefore they can only be run in design mode in Office.

That's why the security message pop up (Initialize ActiveX control) when used in Office solutions. In addition the license agreement allow us to only distribute controls in the runtime version.

That's why I usually use COM add-ins for Excel solutions.

Hopefully the above shed some light over it,
Dennis

Ken Puls
01-12-2006, 09:46 AM
Dennis,

Much appreciated! Maybe I will understand after all! :rotlaugh:

It's interesting that the design time version would have more issues with it in this case, but then I guess that the runtime version is stripped down to only have the components essential to actually use it, while the design time needs to hold all the information to actaully change the control.

What's really interesting is that I never got any security message popups. Now that could be that it's Excel 97 and not a more current version...? Maybe that kind of security prompting wasn't built in. I'm totally guessing here, but if the OS prohibited running the control until someone clicked okay, that could cause some errors, I assume...

XL-Dennis
01-12-2006, 09:51 AM
Ken,



What's really interesting is that I never got any security message popups. Now that could be that it's Excel 97 and not a more current version...? Maybe that kind of security prompting wasn't built in. I'm totally guessing here, but if the OS prohibited running the control until someone clicked okay, that could cause some errors, I assume...


Yes, You're right. It's an issue (feature) that was introduced with 2002 (XP) if my memory is not total out of order. It may or may be fixed with a entry in the registry but in general third-party ActiveX controls should be used in compiled application.

Glad You was able to track it down :)

Kind regards,
Dennis