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.

'---------------------------------------------------------------------------------------
' Procedure : PS_GetFileContent
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Read a file into VBA using PowerShell
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Requires a copy of the PS_GetOutput() function
' References: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-content?view=powershell-7.1
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile         : Fully qualified file and file path of the file to be read into VBA
' lStartNoLines : Number of lines to return from the beginning of the file
' lEndNoLines   : Number of lines to return from the end of the file
' lLineNo       : Specific line number to return
' lStartLineNo  : Start Line number to return
' lNoLines      : Number of lines to return
'
' Usage:
' ~~~~~~
' sMyFile = PS_GetFileContent("C:\Temp\Address_Listing.txt")
' sMyFile = PS_GetFileContent("C:\Users\Dev\Documents\ADSecurity_Policy.ps1")
' sMyFile = PS_GetFileContent("C:\Users\Dev\Documents\Contacts.csv")
' sMyFile = PS_GetFileContent("C:\Temp\Address_Listing.txt", 3)
'   -> returns first 3 lines in the file
' sMyFile = PS_GetFileContent("C:\Temp\Address_Listing.txt", , 3)
'   -> returns last 3 lines in the file
' sMyFile = PS_GetFileContent("C:\Temp\Address_Listing.txt", , , 5)
'   -> returns the 5th line in the file
' sMyFile = PS_GetFileContent("C:\Temp\Address_Listing.txt", , , , 14, 4)
'   -> returns lines 14, 15, 16, 17 of the file
'   Where sMyFile will end up being a variable that will have the files entire content
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-10-15              Initial Release
' 2         2021-10-16              Added arguments to extract specific lines
'                                   lStartNoLines, lEndNoLines, lLineNo, lStartLineNo
'                                   lNoLines
'---------------------------------------------------------------------------------------
Function PS_GetFileContent(sFile As String, _
                           Optional lStartNoLines As Long, _
                           Optional lEndNoLines As Long, _
                           Optional lLineNo As Long, _
                           Optional lStartLineNo As Long, _
                           Optional lNoLines As Long) As Variant
    On Error GoTo Error_Handler
    Dim sCmd                  As String

    'Build the necessary PowerShell Command
    sCmd = "Get-Content -Path '" & sFile & "'"
    If lStartNoLines <> 0 Then sCmd = sCmd & " -TotalCount " & lStartNoLines
    If lEndNoLines <> 0 Then sCmd = sCmd & " -Tail " & lEndNoLines
    If lLineNo <> 0 Then sCmd = "(" & sCmd & " -TotalCount " & lLineNo & ")[-1]"
    If lStartLineNo <> 0 Then sCmd = sCmd & " -TotalCount " & (lStartLineNo + lNoLines - 1) & " | SELECT -LAST " & lNoLines
    'Execute the command and return the output which should be a csv string
    Debug.Print sCmd
    PS_GetFileContent = PS_GetOutput(sCmd)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PS_GetFileContent" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

As you can see, thanks to the power of the PowerShell CmdLet I have been able to add a few arguments to make this function very powerful. So now, not only can you read the entire file from end to end, but now you are capable of extracting specific sections of the file so you don’t need to perform such operations in VBA anymore!!!

Alternative Approaches

If you are not sold on using this VBA/PowerShell implementation, feel free to look at my previous article VBA – Read File into Memory.

A Few Resources on the Subject

• VBA – Run PowerShell Command
• Get-Content (Microsoft.PowerShell.Management) – PowerShell | Microsoft Docs