VBA / Formula approach

[theta]

I need to gather information from my users regarding the operating system and version of office in use. I would like this to be in a set format, and detailed...without the user having to run a macro (manually).

My thoughts were to put together a workbook with an 'Open' event that looks at the registry to get this information, which is then presented in the workbook via a UDF e.g.

=SystemVersion() {Windows 7 SP1, Windows XP SP2, Windows 2000, Windows Vista SP2}

=OfficeVersion() {Office 2000, Office 2003, Office 2007 12.0, Office 2010}

Any help would be appreciated. I tried using =INFO but would like more information than 'pcdos' and '12.0'.

Using this method the user could then simply copy the cells and drop them to me in an email...

[BrianMH]

You could use
Application.OperatingSystem
Application.Version

[theta]

Tried this and doesn't work :

Function OperatingSystem(sOSVer As String)
Dim sOSVer As String
sOSVer = Application.OperatingSystem
End Function

But if it put it in a sub with a MsgBox then it does work... :/

Think I am misunderstanding the differentiation of :

Property
Function
Sub

[BrianMH]

Hi,

You are double declaring your string. You don't need to pass the function anything so it would be. You also didn't define the function as the value.

Function fnOperatingSystem()
Dim sOSVer As String
sOSVer = Application.OperatingSystem
fnOperatingSystem= sOSVer
End Function

To be honest for something this simple I wouldn't bother with a function. Just use a variable directly in your sub and assign the value directly to it. If you want to use it as a function the above will work and you would call it with a sub.

Sub getos()
Dim sOS As String
sOS = fnOperatingSystem
MsgBox (sOS)
End Sub

[theta]

This still returns non-useful info e.g. I am using Windows 7 Enterprise but returns the Net version.

Is there a newer version of this code available as (at the time) it appeared to cover all versions... ?

http://www.vbaexpress.com/forum/showpost.php?p=152961&postcount=2

Looks like it may have started life here : http://vbadud.blogspot.co.uk/2010/05/how-to-get-os-version-using-vba.html

And an incomplete list of codes vs. real world OS :
http://www.officekb.com/Uwe/Forum.aspx/excel-prog/167436/Application-OperatingSystem-returns-what

[BrianMH]

This is generally what you are looking for. It doesn't quite work as it needs the 32 bit or 64 bit returns and I don't currently have time to input all that. My suggestion is to get a few users to return you the raw data and then you can convert it once you have a better list.

Function fnOperatingSystem()
Dim sOSVer As String
sOSVer = Application.OperatingSystem
Select Case sOSVer
Case "Windows NT 6.1"
fnOperatingSystem = "Windows 7"
Case "Windows NT 6.0"
fnOperatingSystem = "Windows Vista"
Case "Windows NT 5.2"
fnOperatingSystem = "Windows Server 2003; Windows XP x64 Edition"
Case "Windows NT 5.1"
fnOperatingSystem = "Windows XP"
Case "Windows NT 5.01"
fnOperatingSystem = "Windows 2000, Service Pack 1 (SP1)"
Case "Windows NT 5.0"
fnOperatingSystem = "Windows 2000"
Case "Windows NT 4.0"
fnOperatingSystem = "Microsoft Windows NT 4.0"
Case "Windows 98; Win 9x 4.90"
fnOperatingSystem = "Windows Millennium Edition (Windows Me)"
Case "Windows 98"
fnOperatingSystem = "Windows 98"
Case "Windows 95"
fnOperatingSystem = "Windows 95"
Case "Windows CE"
fnOperatingSystem = "Windows CE"
End Select
End Function

[theta]

Perfect thanks :)

I have put this together in the meantime (checks for VB7 and 64 bit instlallations)

Will expand to include your workings...


Function OperatingSystem()
Dim sOSVer As String
sOSVer = Application.OperatingSystem
OperatingSystem = sOSVer
End Function
Function OfficeVersion()
Dim sOffVer As String
sOffVer = Application.Version
OfficeVersion = sOffVer
End Function
Function GetOS()
Dim OS
For Each OS In GetObject("winmgmts:").InstancesOf("Win32_OperatingSystem")
GetOS = OS.Caption
Next OS
Set OS = Nothing
End Function
Function WhichBit()
If WIN64 Then
WhichBit = True
Else
WhichBit = False
End If
End Function
Function WhichVBA()
If VBA7 Then
WhichVBA = True
Else
WichVBA = False
End If
End Function


Formulas :

=OperatingSystem()

=GetOS()

=OfficeVersion()&" ("&IF(whichbit(),"64-Bit","32-Bit")&")"&"("&IF(WhichVBA(),"VB7","VB6")&")"

[Aussiebear]

Correct the last WhichVBA.

[Bob Phillips]

Also need the conditionals


Function WhichBit()
#If Win64 Then
WhichBit = True
#Else
WhichBit = False
#End If
End Function
Function WhichVBA()
#If VBA7 Then
WhichVBA = True
#Else
WhichVBA = False
#End If
End Function

[Bob Phillips]

Perfect thanks :)

I have put this together in the meantime (checks for VB7 and 64 bit instlallations)

Will expand to include your workings...

Why bother, your GetOS() already does that?

[theta]

I have been reading two VBA books -

"Dummies Guide to Excel VBA Programming" - hate the 'dummies' range but it was written by a respected Excel expert.

"VB & VBA In a Nutshell" - standard O'Reilly reference

Am also reading up on Windows 32 API and will be studying as much information that I can get on ADO DB, ODBC etc...

But cannot find any (immediate) reference to 'conditionals' - google isn't giving me much but guess I am not framing the question properly. Does anybody have a good resource to explain this?