rkoonce

Excel Is Our Friend – Part IV

Blog Post created by rkoonce Employee on Dec 10, 2013

Excel Is Our Friend – Part IV


While writing this blog series, I became consciously aware… my Dad knew that I didn’t know the names of those tools. I bet he was smiling to himself under the truck… It was a sneaky way of teaching me something useful, without my consent.

  • How to: VBA and FSO


Aside from importing the MDB, and getting current values with DataLink, we have confined ourselves to within the boundaries of the workbook. But with column G, we will access the File System Object, and extract a file property (version) from external files. This same library has other useful methods as well, such as testing for file and folder existence, reading and writing files, to name a few. The following will be limited to getting the file versions, but you can find plenty of information on the web should you have the need to utilize those other methods.


Note: The following code/ example will not always work. It is dependent on a number of things, but most particularly security. If accessing files on a remote machine, and your current logged in credentials don’t equate to an administrator on the remote machine, or if a firewall or policy prevents access. The code utilizes the hidden administrative shares of the remote machine. The remote machine could be in a different domain or workgroup, causing Windows Authentication to fail also. A quick test of accessibility, using start> run>
\\YOURinterfacemachinenameORipaddress\c$

Enough with the disclaimers!

  • In the Visual Basic Editor (VBE), add a reference for Microsoft Scripting Runtime (scrrun.dll) -> Tools menu> References (if you skipped “Foundation” entry, Part II)
  • Create an ActiveX Commandbutton on your worksheet, where the click event will execute the following VBA code (in the screenshot, the button is labeled “GetVersion” and has the name “cmdGetVers”)
  • The following code belongs in the worksheet module for the sheet that is the “parent” container for our button>


Note: Regarding #2, I use an ActiveX Commandbutton, but Excel’s Form Control Button will work too (with some adjustments that I won’t cover here).

 

 

Private Sub cmdGetVers_Click()  ‘The name is unimportant, but with AcitveX, it should match the object name’
    Dim strPath As String     
  If Selection.Columns.Count > 1 Or Selection.Column <> 7 Then
       MsgBox "Please select cell(s) in column G before executing GetVersions"
       Exit Sub
  End If
  For Each c In Selection         
       strPath = "\\" & c.Offset(0, -5).Value & "\" & Replace(c.Offset(0, 11).Value, ":", "$", 1) & c.Offset(0, -2).Value & ".exe"
       c.Value = GetVersion(strPath)     
  Next c
End Sub 

 

The code above calls the function below, passing the path\filename:


4. The following code belongs in the Module1, created previously to hold our User Defined Function "mySplitter", Part III.
Public Function GetVersion(strFilePath As String) As String
   Const ForReading = 1, ForWriting = 2, ForAppending = 3
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   Dim fs As Scripting.FileSystemObject, f As File, ts, s
   
  On Error Resume Next
     Set fs = CreateObject("Scripting.FileSystemObject")
     GetVersion = fs.GetFileVersion(strFilePath)
     If GetVersion = "" Then    ‘if no version, the mod date instead’
       Set f = fs.GetFile(strFilePath)
       GetVersion = f.DateLastModified
  End If
  If GetVersion = "" Then 'just in case it ain't there’
       GetVersion = "no data or file"
  End If
        
End Function 
Note: This function could also exist on the worksheet module above. (it is good practice to use a module for functions (IMO). However, Excel requires that User Defined Functions be in a module, like “mySplitter” above. They won’t work otherwise.) Notice the use of “Public” in the declaration. This allows the function to be called by the remote procedure in the worksheet module. Declaring it as Private would result in an error when we clicked the button above, “Sub or Function not defined”. Translation= I don’t “see” anything named “GetVersion”. (Access to a Private function would only be available to functions and subroutines that also reside within Module1, Public functions would be available globally at the Project level.)


5. Select a range of cells with your mouse, like cells “G28:G30”, and click the button! Hopefully, you will see versions popping into those cells in the blink of an eye.




You may notice that I have several unused variables in the function. These are standard variables that I copied from another routine. I use FSO a lot, for creating, reading, writing, renaming files, etc. More details can be found here:

Visual Basic for Applications Reference for FileSystemObject Object
http://msdn.microsoft.com/en-us/library/aa242706(v=vs.60).aspx

When exploring and understanding any block of code, stepping through the code one line at a time in debug mode is an invaluable learning technique. (see this link http://www.cpearson.com/excel/DebuggingVBA.aspx)

Hopefully, if you have followed along with no major issues, you have a working document. One that saves you a bit of time, monitoring and accessing your own interfaces. But more importantly, you have added new things to your “toolbox”. We have covered quite a bit of ground here: Navigation, Relative vs Absolute Addresses. PI MDB, VBA, Project References, VB Modules, Public vs Private Declarations, FSO, IF Statements, FOR loops, User Defined Functions, Functions (VLOOKUP, MID, LEN, FIND, HYPERLINK, SUBSTITUTE, ISNA), and the use of Nested Functions. Hopefully, you don’t feel like you have been drinking from a fire hose. And even if you are not fully versed in the usage of each and every one of these, just knowing that they exist, and some idea of when to use them, is a huge step in itself. I hope you can use this information to escape from an island…

Part V in the series will be posted soon, concluding this series and contains a FizzBlog Challenge (A bit of a contest). Winners will receive restroom breaks, automatic adjustments (for DST) to their system clocks, and an assortment of free junk mail!

Outcomes