How to use VBA to capture version of Java/MS Office

[Saleen01-0311]

Hello all,

I currently have a userform that when you click the command button it collects and writes to a DB the following information:

UserID
Computer Name
MAC Address
HDD Avail/Used
RAM
# of Processors/ Speed

It also looks for folder names to determine if the PC currently has specific applicatons installed - "Yes or No"

What I would like to have is the latest version of Java installed on the computer, and possible which version of MS Office the computer has as well.

I am currently using

Please Login or Register  to view this content.

What I would like to do is search "C:\Program Files\Java" and return the name of the folder that was modified most recently.

For Office, I am currently using

Please Login or Register  to view this content.

What I would like is for it to return “2003”, “2007”, “2010”.


Any help would be greatly appreciated.

Thanks!

[mc84excel]

Maybe use Application.Version against a lookup of the MS Office versions?

[mc84excel]

Here you go:

Please Login or Register  to view this content.

P.S. I wouldn't rely on using the program folder to determine Office version. Read this page here for why not: http://www.cpearson.com/excel/Existi...lVersions.aspx

[Saleen01-0311]

Thank you for your response, I guessed that would be the route to take for MS Office, but wasn't sure where I can find a valid conversion of "2.4 = 2007"?

Does anyone have any ideas about Java? I figured it would not be possible to find the version of Java through the macro, but is there a way to define a path "C:\Program Files\Java\" and pull the name of the folder with the latest modified date?

Thanks again

[Saleen01-0311]

That worked so far (limited testing at this time, but looks promising) Thank you!

[mc84excel]

You could test using this code (nb writing from memory, untested):

Please Login or Register  to view this content.

[mc84excel]

Actually there may be a way to pull the version number of Java through the macro (but if my idea is on the right track, I'm afraid you will need someone else on the forum to help you. I'm not skilled at what I am about to describe).

First we need to store the Java folder location in the code. The folder path can be stored as a constant at the top of the module. Now here's the tricky bit - Will this code be run on 64 bit or 32 bit Windows? If both - then you will need to set up code with two identically named constants giving the 32 bit & 64 bit Java folder paths. The constant to be read will be auto determined by the end users PC "bitness".

Second have the code use Windows API to return either the "File Version" and/or the "Product version" file attributes from the "java.exe" executable file. Windows API can malfunction if your end users are using 64 bit Excel (note that 64 bit Excel is not the same as 64 bit OS) and the API code will need to be written so it is compatible with both versions.

Hope that helps.

[Saleen01-0311]

Here is the thing; we will be upgrading to Windows 7 soon and I don't know which it will be. I may even need another field to show which operating system is installed.

[mc84excel]

No worries. Here are some functions you may find useful for Windows version. Hope it helps.

Please Login or Register  to view this content.

If you like, you could edit GetWindowsOSVersion function to return string and add Select Case (refer the code I provided for the Excel version) so that it returns a string of the OS version name (rather than just a version number).



Also you may be interested in this function - you can use it to determine whether the Excel is 32 bit or 64 bit.

Please Login or Register  to view this content.

[mc84excel]

Hello other Forum Users,

Saleen still requires the Java version. (Refer post #4 & #7 of this thread). I don't have enough skill to solve this. Would anyone like to lend a hand?

[mc84excel]

I think I have the start of the solution for the first hurdle in post #7. See code below (NB this code is untested). Note that this isn't foolproof - there are subfolders below this folder path and, from what I've observed, it would seem that old version folders can remain (e.g. "jre6" & "jre7"). You would need code to search this folder path and return the "highest version" out of the subfolder names...

Please Login or Register  to view this content.

[abousetta]

Hi,

The code below will tell you which version of java you have by telling you which application is associated with .jar files:

Please Login or Register  to view this content.

This method can be extended to any extension type.

Hope this helps.

abousetta

[Kyle123]

Seems a bit involved

Please Login or Register  to view this content.

[abousetta]

Hi Kyle,

Thanks for the new approach. I used the approach above to find the executable file for a program I often use. In the XP and win7 environments they are located in different places. So I found that the approach I posted above can find it no matter where it is located on the computer. Would I be able to adapt your approach to any program that is installed or specific programs only? I'm not very familiar with the ins and outs of software development and so a lot of it is really new to me.

Thanks.

abousetta

[Kyle123]

I think it depends on what you're trying to do, my code just reads the currentVersion key from the registry folder that's created when the Java Runtime is installed. Most software will put something in the registry, but there's little consistency as to what data is stored in there - just so happens that the Java version is there. Reading the registry is useful for all sorts of things, like finding the port of a given printer - you just need to know where to look, registry keys are also usually consistently placed on different windows versions which can be handy

Have a dig, but don't change anything unless you know what you're doing - you can seriously break things !!!

[mc84excel]

Hello Kyle & abousetta - I don't know what's happened to Saleen but brilliant codework from both of you +1

Slightly puzzled though. I tested both versions and neither returns the right result for me:
abousetta = "No matching application"
Kyle = 1.6
(I have Java version 6.0.330.3)

[abousetta]

For the method I reported to work, Java must be associated with the .jar file type. A simple way to test is create a text file, change it's extension to .jar and right click and see which program is associated with it. I'm guessing on your system it is not associated with Java.

abousetta

[Kyle123]

1.6 is 6
1.7 is 7

See here:
http://www.oracle.com/technetwork/ja...ng-418744.html

[mc84excel]

Kyle - You are right. My apologies.

abousetta - You are right. (It was associated with WinRar). (So I changed the application for .JAR to Java. The code still returned the same message. )