Windows 7 user account notifications and VBA...

[swoop99]

I've got an application written in VBA running in Excel and it's having issues on Windows 7 machines. During testing it worked absolutely fine, but it hadn't occurred to me that the fella doing the testing is an IT expert and had turned off user account notifications in Windows 7 already.

The issue is that if the app tries to write a file to a system area in windows (there's two being written, one to the same location as the Workbook in c:\program files (x86)\MAPS\MAPSdata\ and another to c:\programdata\MAPS) then Excel will hang and go unresponsive. If the subfolders do not exist it will try to create them. If user account notifications are turned off (control panel - user accounts - user account settings - slider bar all the way to the bottom) then it works absolutely fine.

The code works fine in all other versions of windows.

Is there some way in VBA to tell windows 7 to "shut the hell up and let me write a file ferchristsakes"? Or am I going to find somewhere else to write data files to? And if so, any idea where I can write to from VBA without Windows 7 going off in a sulk?

[JosephP]

you could write to the users documents folder?

[swoop99]

good idea. How to I address it in code?

At present I'm using the following:

Please Login or Register  to view this content.

which, in XP gives me the all users folder under docs and settings; and in Windows 7 gives me c:\ProgramData\

[swoop99]

After more digging....

I've found I do need to be able to write to the program files folder. This does work ok in Windows 7, however, in order to allow network access I'm using the following function to detect if the file in question is in use by another instance of the application:

Please Login or Register  to view this content.

And call it in code:

Please Login or Register  to view this content.

The problem is that under Windows 7 the file reports as existing (using getattr(path) and seeing if it errors) but the do until isfilelocked = false goes off into an endless loop and Excel hangs. Therefore, the issue is that the isfilelocked will always report a file in a system area as locked in windows 7 whether it is or not, unless user account notification is switched off. As I say, I have to be able to read and write to the program files subfolder.

Um.... any ideas anyone?

[JosephP]

can we ask why you need to write there? I doubt it will be simple to bypass UAC or it would be worthless.
by the way

Please Login or Register  to view this content.

will give user's mydocument path.

[swoop99]

It's a complicated answer I'm afraid.

The system is designed for use in schools, children take maths tests in the application and all the results are written to another workbook, this workbook is stored in a shared network location and the 'isfilelocked' function successfully delays writes to it allowing for smooth network operation.

However, there's also an @Home version of the software that works in the same way but the installer (winzip self extracting archive) creates (successfully) a folder in C:\program files with the main application, the database, a sounds folder and the icon. The installer also creates (successfully in both win XP and 7) a program files group and an icon on the desktop.

When the application is first run it creates a new workbook called MAPSdata.xls and writes it to both a new (hidden) folder in the same folder as the main application and a copy written to dosc&settings\all users\application data\MAPS\ (or c:\programData\MAPS\ in Win 7). This MAPSdata.xls holds the path to the database so that if a home user wishes to install the app on multiple PCs and have the whole family playing on MAPS at the same time he won't have to tell it where to find the database every time.

When the app launches it first looks for the all users\app data\ datafile, if it can't find one it'll look for the hidden data directory in whatever the path to the application workbook is, if it can't find that either it assumes it's a fresh install and runs through the install routine which will auto detect where the database is and write the two data files. Auto detect takes a min or two, hence why it doesn't just auto detect every time.

[JosephP]

maybe I missed something but I don't see any reason why you have to use those directories rather than say a new MAPS directory in the users Documents folder.

[swoop99]

Because the database needs to be accessible both through a network and by every user of the PC. If the database workbook is in a users documents folder then other users can't access it.

[swoop99]

After some more testing.....even changing to using the documents folder gives the same error. Seems Excel is refusing to open the workbook with read/write access, always read only. File properties in windows explorer DO NOT say read only though.

I don't get it....

[JosephP]

can't say without any code to see :-)

[swoop99]

The code is as simple as workbooks.open (path), activeworkbook.close savechanges:=true

But anyway, solved it now. I've found that Windows 7 doesn't like one process opening files created by another process, even if that other process was the same application running under the same user account. ie, if you have code that creates a new workbook, writes a value to it and saves it then it will have the default security permissions:

administrator accounts - full control
user accounts - read and execute only.

If the same process opens that file again it'll open with full access but if you quit Excel and reload the application, when the file is opened in code it'll be open read only and attempts to save it will cause an error.

The fix is to add the usergroup 'everyone' to the file's security access with full control.
So immediately after first creating the file in VBA I'm now using the following code to set the security permissions to allow full access in the future:

Please Login or Register  to view this content.

[JosephP]

that is not normal behavior in Windows 7. if it were it'd break most all the code in use today.

[swoop99]

Well that's odd then. It's doing it on both test systems and every single production system. The only time it doesn't do this is if Windows security notifications are turned off (control panel, user accounts, notifications, slider all the way to the bottom) but rather than asking every user to turn em off I've added the code above as a workaround.

[JosephP]

which version of Win7 are you running? Pro, Enterprise or other?