How to Get Windows OS Information Using VBA

I’ve previously touched upon this subject back in 2016 in my post entitled VBA – Determine the Installed OS.

That said, today, I thought I’d demonstrate 2 possible approaches to retrieving Windows OS information using:

  • PowerShell via VBA
  • Plain VBA

and dig in a little more into the subject to show various functions that can be used and how they compare to one another at the end of the day.

In both instances, we are calling WMI to get system information. In this instance, we can get Windows OS information by querying the Win32_OperatingSystem class as it exposes a whole slew of OS properties, such as:

Property Example of returned Value Comment
Caption Microsoft Windows 10 Home There is also a Name property but include Harddrive info for example ‘Microsoft Windows 10 Home|C:\WINDOWS|\Device\Harddisk0\Partition2’, so IMHO Caption is what most peopl would be considered the name.
OSArchitecture 64-bit
Version 10.0.19043
BuildNumber 19043
OSLanguage 1033
WindowsDirectory C:\WINDOWS

Check the official documentation, a link is provided below, for a complete listing of all the available properties.

Why?

Some people have asked me why we would need to gather such information. The reason is quite simple, this information can be useful when providing support to end-users and can assist in troubleshooting and isolating environment issues. I typically include this information as part of my error logging routine.

It makes sense to have this automatically retrieved through your application were it could be copy/paste, automatically e-mailed, … so your end-users don’t need to find a way to locate the information themselves. We always need to remember that not everyone is comfortable with computers and if we can simplify things for our end-users, we should take the opportunity to do so!

Retrieving Windows OS Information Using PowerShell

Since I’ve been doing a lot of work exploring extending VBA’s capabilities by using PowerShell through VBA, I thought it natural to see if such information could easily be accessed. As with my other VBA/PowerShell posts, such information is very easily retrieved.

These PowerShell functions all require a copy of my PS_GetOutput function which can be found in my post VBA – Run PowerShell Command.

So let’s explore how we can retrieve each property individually and then how we can get them all in a single call.

Continue reading →

Determine the Office Update Channel

Another critical element to determine to be able to provide support to an end-user is their Office Update Channel as it dictates what updates, build no., are available to them. So this is required to know if their installation is up-to-date, or not.

I came across a webpage that indicated the fact that each Update Channel used a different URL. With this information in hand, I figure we could build a function to cross-reference the PC’s URL against the list to determine the current update channel.

Below is what I came up with.

Continue reading →

Determine the Program’s Release Type

More than ever, with MS365 (previously Office 365), it is very important to easily be able to determine the release type (Volume License, Retail, Trial, …) of the Office installation as the different Release Types have different update schedules. Furthermore, what I have come to understand is that many users have no clue about Release Type nor how they can ascertain theirs.

This is the type of information along with things like the Bitness or Build No that anyone providing end-user support needs.

Today, I thought I’d offer a simple VBA function that can easily get this for us!

Continue reading →

How to Print Files in VBA Without APIs

As I continue to delve into PowerShell I have been successfully eliminating the need for several APIs in my code base to eliminate things like

  • APIs
  • Enums
  • Conditional Compilation Directives
  • Versioning issues/Missing References

In the past I have always printed documents by simply using the ExecuteFile function found in my article VBA – Opening Files and URLs.

PowerShell Printing a Single File

Once again, PowerShell makes it extremely easy to print a file and performs this action with a single line of code, as shown below:

Start-Process -FilePath 'C:\Users\Dev\Documents\PowerShell.docx' -Verb Print

Thus, we can build a simple VBA wrapper like:
Continue reading →

Get Image Exif Properties using VBA and PowerShell

In this article I thought I’d look at how we could retrieve Image Exif property values using VBA and PowerShell.

EXIF

What is contained in the Exif properties?

Well EXIF contains all sorts of ‘extra’ information pertaining to a file. What exactly depends on the original source of the file, and whether it has been edited. So that is to say that it can vary from images, mp3, … It will even vary between cameras used to take pictures.

Also note that some people also refer to this as a file’s meta data.

Here’s an example of what I retrieved for a sample image

Filename              : DSC_0860.JPG
Height                : ‎680 pixels
Date taken            : ‎10/‎27/‎2018 ‏‎6:37 PM
Item type             : JPG File
Metering mode         : Pattern
Space used            : ‎91%
File extension        : .JPG
Flash mode            : No flash, auto
Owner                 : MSI\Dev
Dimensions            : ‪1024 x 680‬
Vertical resolution   : ‎300 dpi
Name                  : DSC_0860.JPG
Light source          : Unknown
Folder                : 111APPLE (D:\Pictures\)
Width                 : ‎1024 pixels
Program name          : Ver.1.00 
Focal length          : ‎102 mm
Date accessed         : 10/22/2021 7:37 PM
Size                  : 447 KB
Path                  : D:\Pictures\111APPLE\DSC_0860.JPG
Date modified         : 10/28/2018 5:25 PM
Date created          : 1/22/2021 8:28 PM
Link status           : Unresolved
Shared                : No
Max aperture          : 4.4
Exposure time         : ‎1/200 sec.
Folder path           : D:\Pictures\111APPLE
Horizontal resolution : ‎300 dpi
Computer              : MSI (this PC)
Comments              :                                     
Exposure program      : Unknown
Attributes            : A
Total size            : 910 GB
Folder name           : 111APPLE
White balance         : Auto
EXIF version          : 0221
Space free            : 74.6 GB
Type                  : JPG File
Program mode          : Not defined
Bit depth             : 24
ISO speed             : ISO-1600
35mm focal length     : 153
F-stop                : f/4.5
Rating                : Unrated
Saturation            : Normal
Orientation           : Normal
Camera model          : NIKON D90
Exposure bias         : ‎0 step
Kind                  : Picture
Perceived type        : Image
Camera maker          : NIKON CORPORATION

As you can see, there is a treasure trove of information. It can even include things like Longitude, Latitude, … so it can contain sensitive information allowing people trace Who, Where, When.

Retrieving EXIF information via VBA

I was initially encouraged when I saw I could easily get image size value by simply doing:

[System.Drawing.Image]::FromFile('C:\Users\Documents\Test.jpg').Size.Height

and

[System.Drawing.Image]::FromFile('C:\Users\Documents\Test.jpg').Size.Width

I thought that perhaps it was that easy to do in PowerShell. Sadly, that was the extent of that technique; 2 available properties and no exif properties. Oh Well.

So I got digging and ended up creating the following:
Continue reading →

Get Environment and System Variables using VBA and PowerShell

In my continuing quest to build a PowerShell library, today I thought I’d look at how we can possibly use PowerShell, through VBA, to get various environment and system variable values.

I’ve somewhat touched upon this subject in the past in articles like

PowerShell

Why PowerShell exactly?

I think by now I have clearly proven the extreme power of PowerShell and the fact that we can tap into this through VBA is actually spectacular! Now, there is nothing wrong with some existing approaches, but there are many others that PowerShell is simply better. With PowerShell we don’t need to mess with

  • bitness/conditional compilation
  • APIs
  • …

PowerShell is surprisingly fast!

So you get what you want without any of the disadvantages of some of the other solutions.

Like everything else in life, you need to evaluate for yourself if this helps or hinders your development. For me, by adding the 2 function: PS_Execute and PS_GetOutput from VBA – Run PowerShell Command I have been able to eliminate many of my common APIs and streamline my code base. This simply makes my solution more stable and as indicate earlier, some PowerShell solution are faster, which makes users happy.
Continue reading →

Read JSON with VBA and PowerShell

JSON

Yesterday, I discussed reading XML content in my Read XML with VBA and PowerShell article. Today, I thought I’d see if the same was true with working with JSON data.

Like with my XML article, this article focuses on simply reading/retrieving data from either a web source or a file.

If you’ve already read the XML article, you’ll see that the processing, once you manage to load the JSON content is basically all the same. So it is refreshing to see that you can, for a very large part, handle all these different data formats with the exact same techniques and code. Learn once, apply to everything!

For this article I am using a JSON Sample taken from JSONPlaceholder – Free Fake REST API, specifically the users sample.

Also, as with any of my PowerShell articles, the base functions like: PS_Execute and PS_GetOutput come from my first article entitled: VBA – Run PowerShell Command.

Web Content vs File Content

The first thing to understand is that working with web JSON is initiated differently then when working with a JSON file from a PC or Server.

Web JSON

To load JSON coming from the web you would use a PowerShell command like:

$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/users' -UseBasicParsing

File JSON

To load JSON coming from a file on your PC/server you would use a PowerShell command like:

$file = 'C:\Users\Dev\Documents\users.json'
$response = (Get-Content $file -Raw)

Now that this is out of the way, the remainder of the reading/processing/… is all the same regards of the JSON source.

Continue reading →

Read XML with VBA and PowerShell

XML

Continuing with my PowerShell Series, today, I decided to play around with PowerShell to see if it could simplify working with XML content.

It took a little playing around with PowerShell to figure things out, but once you understand the basics, it become quite evident that, once again, PowerShell is definitely a most capable XML parser.

This article focuses on simply reading and extracting information from an XML file, but PowerShell also offers a vast array of XML manipulation method. So although not covered in this article, the option definitely exists. Perhaps that will be the subject of another post in the future.

Web Content vs File Content

The first thing to understand is that working with web XML is initiated differently that when working with a XML file.

Web XML

To load XML coming from the web you would use a command like

$doc = (New-Object System.Net.WebClient).DownloadString("YourURL")

File XML

To load XML coming from a file on your PC/server you would use a command like

$file = 'YourFilePathAndFilename' 
$doc = New-Object System.XML.XMLDocument 
$doc.Load( $file )

Once that part is done, working with the XML content is done in the same manner.

Continue reading →

Read a File’s Content using VBA and PowerShell

In my continued exploration of PowerShell, I’ve been trying to build a toolbox of common utilities all built off of PowerShell so as to simplify my code base as much as possible and eliminate APIs, reduce code length, …

So, today, I decided to look at another way to read the contents of a file into VBA for use there.

Reading a File Using PowerShell

Per my other PowerShell articles, PowerShell offers us easy to use CmdLets for this for almost everything. In this case, we can use the Get-Content Cmdlet and we’re done!

Once again, I simply build a wrapper function and the job was done.

Continue reading →