Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Version of a file in VBA

Posted on 2011-02-14
Medium Priority
Last Modified: 2012-05-11

I'm trying to find the version of a file using VBA. What i've been trying to do is run the filever command in CMD via Shell, but can't seem to get this to output the result to a variable.

Is this possible, to populate a string variable with the results of a shell command?

Is there another way to find this?

Question by:ITUCIRL
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 85
ID: 34889387
That would depend on what the Shell command's called program returns. In most cases, the result isn't what you're after.

You can use several methods to find this out, however. The FileSystemObject can do this:


Expert Comment

ID: 34889850
Simple solution is to use DSOfile.dll from microsoft. Get more details from http://support.microsoft.com/kb/224351
LVL 11

Accepted Solution

RgGray3 earned 2000 total points
ID: 34889928
Shell happen in their own memory...   making communication difficult...

Does the process you are running in the shell... allow redirection...?

YourCmdToFindVersion > SomeTextFile.txt

If so you can redirect the output to a text file and READ the text file using FSO.

What are you using to determine the version number and version of What?

If you are looking to find a file version of a Dll or something.. you are better off using an API call to retrieve the value...

See the attached code
' --------------------------------------------------------------------
' Module     : usys_GetOLEAut32VersionInfo_bas
' Description:
' Procedures : IsOLELibNewer()
'              GetResourceVersion(strFileName As String, recFileVer As FileVersion)
' Copyright  : 2005-2011
'              Richard Gray, III Software
'              All rights reserved.
' --------------------------------------------------------------------
Option Compare Database
Option Explicit

' API Calls for getting a file's version information
Private Type FileVersion
    FileVersion         As String         ' Full file version as a string
    FileVersionMSl      As Integer        ' File version MSB Low
    FileVersionMSh      As Integer        ' File version MSB High
    FileVersionLSl      As Integer        ' File version LSB Low
    FileVersionLSh      As Integer        ' File version LSB High
    ProductVersion      As String         ' File product version as a string
    ProductVersionMSl   As Integer        ' Product version MSB low
    ProductVersionMSh   As Integer        ' Product version MSB high
    ProductVersionLSl   As Integer        ' Product version LSB low
    ProductVersionLSh   As Integer        ' Product version LSB high
End Type

    dwSignature         As Long
    dwStrucVersionl     As Integer
    dwStrucVersionh     As Integer
    dwFileVersionMSl    As Integer
    dwFileVersionMSh    As Integer
    dwFileVersionLSl    As Integer
    dwFileVersionLSh    As Integer
    dwProductVersionMSl As Integer
    dwProductVersionMSh As Integer
    dwProductVersionLSl As Integer
    dwProductVersionLSh As Integer
    dwFileFlagsMask     As Long
    dwFileFlags         As Long
    dwFileOS            As Long
    dwFileType          As Long
    dwFileSubtype       As Long
    dwFileDateMS        As Long
    dwFileDateLS        As Long
End Type

Private Declare Function GetFileVersionInfo Lib "Version.dll" _
                  Alias "GetFileVersionInfoA" (ByVal lptstrFilename As String, _
                                               ByVal dwHandle As Long, _
                                               ByVal dwLen As Long, _
                                                     lpData As Any) As Long

Private Declare Function GetFileVersionInfoSize Lib "Version.dll" _
                  Alias "GetFileVersionInfoSizeA" (ByVal lptstrFilename As String, _
                                                         lpdwHandle As Long) As Long

Private Declare Function VerQueryValue Lib "Version.dll" _
                  Alias "VerQueryValueA" (pBlock As Any, _
                                    ByVal lpSubBlock As String, _
                                          lplpBuffer As Any, _
                                          puLen As Long) As Long

Private Declare Sub MoveMemory Lib "kernel32" _
             Alias "RtlMoveMemory" (dest As Any, _
                              ByVal Source As Long, _
                              ByVal Length As Long)

Public Sub GetResourceVersion(strFilename As String, _
                                recFileVer As FileVersion)
' --------------------------------------------------------------------
' Comments  :
' Parameters: strFileName
'             recFileVer -
' Modified  :
' --------------------------------------------------------------------
' Comments  : Returns file version information
' Parameters: strFileName - Name of the file
'             recFileVer - FILEVERSION type
' Returns   : Nothing
' --------------------------------------------------------------------
Dim lngRC As Long
Dim lngDummy As Long
Dim abytBuffer() As Byte
Dim lngBufferLen As Long
Dim lngVerPointer As Long
Dim lngVerbufferLen As Long

On Error GoTo PROC_ERR
    ' Get the size
    lngBufferLen = GetFileVersionInfoSize(strFilename, lngDummy)
    If lngBufferLen < 1 Then
        Exit Sub
    End If
    ' Set up the byte array
    ReDim abytBuffer(lngBufferLen)
    ' Get the file version information
    lngRC = GetFileVersionInfo(strFilename, 0&, lngBufferLen, abytBuffer(0))
    lngRC = VerQueryValue(abytBuffer(0), "\", lngVerPointer, lngVerbufferLen)
    ' Manipulate the bits
    MoveMemory udtVerBuffer, lngVerPointer, Len(udtVerBuffer)
    ' Build the file version string
    With recFileVer
        .FileVersion = Format$(udtVerBuffer.dwFileVersionMSh) & "." & _
                       Format$(udtVerBuffer.dwFileVersionMSl) & "." & _
                       Format$(udtVerBuffer.dwFileVersionLSh) & "." & _
        .FileVersionLSh = udtVerBuffer.dwFileVersionLSh
        .FileVersionLSl = udtVerBuffer.dwFileVersionLSl
        .FileVersionMSh = udtVerBuffer.dwFileVersionMSh
        .FileVersionMSl = udtVerBuffer.dwFileVersionMSl
    ' Build the product version string
        .ProductVersion = Format$(udtVerBuffer.dwProductVersionMSh) & "." & _
                                Format$(udtVerBuffer.dwProductVersionMSl) & "." & _
                                Format$(udtVerBuffer.dwProductVersionLSh) & "." & _
        .ProductVersionLSh = udtVerBuffer.dwProductVersionLSh
        .ProductVersionLSl = udtVerBuffer.dwProductVersionLSl
        .ProductVersionMSh = udtVerBuffer.dwProductVersionMSh
        .ProductVersionMSl = udtVerBuffer.dwProductVersionMSl
    End With
    Exit Sub
    MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    Resume PROC_EXIT
End Sub

Public Sub TestFileVer()
Dim strFile As String
Dim recFile As FileVersion

    strFile = "avmeter.dll"
    GetResourceVersion strFile, recFile
    Debug.Print recFile.FileVersion
    Debug.Print recFile.ProductVersion
End Sub

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 34889995
Is this possible, to populate a string variable with the results of a shell command?
The short answer is YES

I'm not sure if you have been able to get the version, but I'll tell you how just incase.

The easiest method would be to extract the "filever.exe" from the support.cab file in a windows cd located at drive:\SUPPORT\TOOLS\SUPPORT.CAB - You can open it with Windows Explorer, Winzip etc. From there you can use the filever.exe to get the file version of pretty much anything, the full explanation is at http://support.microsoft.com/kb/913111 - it will explain everything it outputs. an example usage of it would be:
C:\filever.exe myTestFile.exe

Open in new window

which would output something similar to:
--a-- W32i   APP   -  3.1.4007.24314 shp  6,488,064 12-21-2010 myTestFile.exe

What I've done for the VB example, is I've created two buttons, one button creates a batch file (which contains my shell command), and the other starts it, reads the output, stores it in a string variable, and displays it in a message box, then deletes the batch file.

This is certainly the easiest way to do this, either creating or accessing a batch file, because otherwise you're going to be trying to access specific windows and controlling them etc.

Here's what i did:

Button1 creates the batch file (Note: This is unneeded, it's here to show you how to create one from VB, you can just access anything directly from the Button2)

Required Imports:
Imports System.IO

Open in new window

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oWriter As StreamWriter
        oWriter = My.Computer.FileSystem.OpenTextFileWriter(Application.StartupPath + "\test.bat", True, System.Text.Encoding.ASCII)
        oWriter.WriteLine("@echo off" & Chr(13))
        oWriter.WriteLine("echo This Is A Test")
End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim process As New Process()
        Dim FileName As String = Application.StartupPath + "\test.bat"
        process.StartInfo.UseShellExecute = False
        process.StartInfo.RedirectStandardOutput = True
        process.StartInfo.RedirectStandardError = True
        process.StartInfo.CreateNoWindow = True
        process.StartInfo.FileName = FileName
        Dim output As String = process.StandardOutput.ReadToEnd()
End Sub

Open in new window

When you select Button2 you will get a message box saying "This Is A Test".

Basically, it is just starting a new process with the file you tell it to, and redirecting the standard output to your application for reading. Very simple, and it works.

Hopefully this answered your question,
LVL 46

Expert Comment

ID: 34891662

It looks like you've posted VB.Net code.  This is a VBA runtime environment.
LVL 46

Expert Comment

ID: 34891705
This is an example of using FSO in a VBA environment:

Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Debug.Print objFSO.GetFileVersion("path and file name")

Set objFSO = Nothing

Open in new window


Expert Comment

ID: 34891874
@aikimark yes, it's vb.net, sorry I forgot to mention that, I posted that to merely set him on the right track as from my understanding, VBA uses the same 'base' for programming and are fairly similar. The oWriter was purely informational as it isn't required for the other code to work. I'm on the road atm so I can't check but I'm sure there are plenty VB.Net to VBA converters. I'll post back later tonight when I'm back home or in the office.
LVL 11

Expert Comment

ID: 34891988

FIY to all

If you explicitly declare your fso object as a NEW FileSystemObject you will expose intellisense and see all of the methods and properties.

Dim objFSO     As New FileSystemObject

    Set objFSO = CreateObject("Scripting.FileSystemObject")  
    strYourVar =  objFSO.GetFileVersion("path and file name")  

I wish there were an early binding method for WMI
LVL 46

Expert Comment

ID: 34892087

I haven't found any.  How are you at doing the conversion manually?  There is another EE question where that expertise is needed.

>>I wish there were an early binding method for WMI
Add this reference:
Microsoft WMI Scripting V1.2 Llibrary

Author Comment

ID: 34895249
I've rolled out Flash and Shockwave via GPO, and i was going to use VBA in Access to check the versions to make sure they're the most up-to-date.

I have all of my client PCs in a table, so i was going to use a loop to check each one, and if it is older than the current version, to print the PC name to a text box, or a text file.
LVL 85
ID: 34895376
Was you last comment something that we need to take into consideration regarding this question? Because it seems your original question has been well answered by any of several comments. If you need further help building the loop and such, you probably should ask a new question.

Author Comment

ID: 34895493
i was just giving a bit more info. I'll have a go at some of these methods.

Thanks guys.

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question