Bentley "Trust Licensing" Check

[SteveGregory]

Bentley recently changed the licensing rules for structural analysis programs like STAAD and RAM that will penalize small firms that own only one or two licenses of a particular program. Previously, if you were using all of your available licenses, other potential users were prevented from using the program. That was fair and it worked. The new scheme allows all users to use the program at will without notifying the users before they exceed their limit. However, this usage will result in a phone call from Bentley requiring you to purchase more licenses. Even the same user opening a program and closing a program twice in the same clock hour counts as requiring two licenses!

I need a way to manage our usage. Opening, modifying, saving and closing a spreadsheet file on our network does two things. An open spreadsheet file can't be opened by another user. Also, the spreadsheet "last saved" date and time is recorded when finished. This second attribute is important to avoid the "hour" rule. The hour rule does not pertain to time duration. It's the clock hour time periods.

My question - I need a function in Excel to return the file modified date or last saved date. I found a handy program add-in that works: ASAP Utilities but I would like something simple and self-contained for this spreadsheet. If possible, I would like it to work on Excel 2000 and 2007.

See attached PDF Demo. My next question may be how I might get a batch file to load the spreadsheet if possible and then the Bentley program if the hour rule does not apply anymore.

 

[SteveGregory]

Here is my spreadsheet with the ASAP addin functions.

 

[SteveGregory]

I think that I solved the function problem with:

Function LastSaved() As Date
Application.Volatile
LastSaved = ThisWorkbook.BuiltinDocumentProperties(12)
End Function

At least it works with Excel 2000 at home. I have other versions at work.

Is it possible to write a batch file to load the spreadsheet and abort if the spreadsheet is already open and pass a message to the user. The message could be a simple "This Program is in use" or a more helpful message like "This Program is in use by ______".

If the spreadsheet opens and the "hour rule" check indicates the program may be used, can Excel run the program without exiting the spreadsheet?

If so how? I know almost nothing about VBA.

 

[IDS]

If so how? I know almost nothing about VBA.

You seem to be doing a good job with almost zero knowledge.

I'd be tempted to dump the Bently programs for something with a more friendly licence agreement, but I realise that may not be an option.

Check out the VBA "Shell" command, it will run any external application.

For your purposes, the link below looks like it has some valuable advice:

http://www.cpearson.com/excel/ShellAndWait.aspx

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SteveGregory]

Doug, I like the programs and the Tech support is great. When I install the next updates, I will have to face the new licensing scheme. So I am trying to have a work-around ready to go. Perhaps, Bentley will see how unfriendly this scheme is to the small firms and reverse course.

I will have a separate excel file for Ram Elements and Ram Structural System. We have one license for each. My plan is to put the excel files on our company network server and point desktop shortcuts to these files.

In the past, I have used these programs at home to work nights or weekends. A VPN connection (very slow) at home might work for the Excel file but not the structural programs. Currently, we no longer have VPN available. I thought about putting the files in a Dropbox folder that we can share. However, Dropbox puts a separate copy on each computer and syncs the copies. I can see problems with this approach. I am wondering if there is another free web "cloud" service that would allow us to store and open a spreadsheet file that only exists there.

Back to VBA!!! The shell command would have to point back to the structural program that resides on a local drive that could be in different locations. We have XP and Windows 7 OS on 32-bit and 64-bit machines. The shell command may need to point to a Batch file that loads the program such as C:\RAM1.bat and C:\RAM2.bat on each computer. When we exit the structural program, then we could manually close and save the excel file which sets a new "last saved" date for the next user.

For my VBA function, I used Alt-F11 and pasted the function I found on the web into a Module and then Alt-Q to get back to the spreadsheet. Should I do the same for the shell macro? Would it be cleaner to execute the macro with a Button or check a 0 or 1 formula result in a cell? HELP, I'm a long way from DOS (1980's).

 

[IDS]

Microsoft Skydrive might be a possibility, but if you are running the Excel file on the server it won't run VBA, and if you copy it to the local drive you will have the problem of multiple copies.

FWIW Excel 2013 seems to be closely integrated with Skydrive, but I haven't used it for any serious file sharing applications.

I'll give it some thought and reply later.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Some more thoughts on this:

I think an approach that might work is:
[ul]
[li] On Skydrive (or any other similar service that lets you run an Excel remotely) have one empty Excel file for each of your two Bentley programs[/li]
[li] On each workstation where someone may need to run the programs have an Excel file with VBA code that on the click of a button will:[/li]
[li] Try to open the Skydrive file for the chosen program.[/li]
[li] If the file is already open send a message to say it isn't available[/li]
[li] If it can be opened, read the last save time, and return an appropriate message[/li]
[li] If the licence is available, open the program, with the two Excel files still running in the background[/li]
[li] When the analysis program is closed, close the Skydrive Excel file, and return control to the local Excel file[/li]
[/ul]

One potential problem with this is if the local machine has a crash you may be left with the Skydrive Excel file still open, so no-one can access the associated Bentley program.

Regarding creating VBA code, yes what you did is all you need to do. General purpose code should go in a code module (which you need to create if starting from an empty workbook). You can also assign code to the "Thisworkbook" object, or to any worksheet. If you do that you can write code that runs on specific events, such as Workbook_Open. To do that:
[ul]
[li]In the drop-down box at the top-left of the code window select "Workbook" (or ""Worksheet").[/li]
[li]In the drop-down box to the right you will now find a list of events, to which you can assign code.[/li]
[/ul]

To get a reliable working system should not be a huge task, but it won't be totally straightforward either. If you have any colleagues with a good working knowledge of VBA, that would certainly help.

Good Luck!


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/