PowerShell – Using PowerShell Script in Excel VBA

March 11, 2019

There are numerous real life scenarios where you would like to hand out an Excel to an End User to retrieve data straight in Excel.

Without him or her have to use PowerShell or any other obscure tool that does not look familiar to them.

So we can establish this by using the old time favorite tool from SAPIEN Technologies.

image

To download ActiveXPosh you need to register first …

This Free Tool is doing the magic between the PowerShell scripting and any COM compatible client, like Excel or VBScript or AutoIT Smile

So here we are going to create an Excel VBA macro to demonstrate the use.

Solution :

The flow being used is to export a PS script to CSV. And next open the data in the Excel file that runs the macro.

1. Make sure that you are using the correct architecture version x86 or x64, depending of your Excel architecture …

So if you use an Excel x86 version you need to install the x86 ActiveXPosh version.

2. Create a Excel + Macro using this script :

The PowerShell Script is :

Get-WmiObject -class Win32_Service | Export-Csv -Path c:\temp\temp.csv -NoTypeInformation

The complete VBA macro is :

 

Sub ActiveXPoSH_ExportPS_ImportCSV()

Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

Dim ActiveXPosh As Object
Dim success As Variant
Dim cmd As String
Dim file As String
Dim sheet As String

' see output location here
file = "C:\temp\temp.csv"
sheet = "Sheet1"

' create the PowerShell object
Set ActiveXPosh = CreateObject("SAPIEN.ActiveXPoSHV3")

success = ActiveXPosh.Init(vbFalse) 'Do not load profiles

If success <> 0 Then
    Debug.Print "Init failed"
End If

If ActiveXPosh.IsPowerShellInstalled Then
    Debug.Print "Ready to run PowerShell commands"
    'MsgBox ("Ready to run PowerShell commands")
Else
    Debug.Print "PowerShell not installed"
End If

'Set the output mode
ActiveXPosh.OutputMode = OUTPUT_CONSOLE

' set command string
cmd = "Get-WmiObject -class Win32_Service " _
      & " | Export-Csv -Path " & file & " -NoTypeInformation"

' clear all data
Sheets(sheet).Cells.Delete

Dim outtext, Str
' set the output mode to buffer
ActiveXPosh.OutputMode = OUTPUT_BUFFER

' run cmd
ActiveXPosh.Execute (cmd)

' get the output line by line and add it to a variable
For Each Str In ActiveXPosh.Output
    outtext = outtext & Str
    outtext = outtext & vbCrLf
Next

' import the data to the Sheet1
Set ws = ActiveWorkbook.Sheets(sheet)
With ws.QueryTables.Add(Connection:="TEXT;" & file, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With

' delete the querytable if there is one
On Error GoTo nothingtodelete
    Sheets(sheet).QueryTables(1).SaveData = False
    Sheets(sheet).QueryTables.Item(1).Delete
nothingtodelete:

' Delete the temp CSV File
If Len(Dir$(file)) > 0 Then Kill file

' Alternatively you can get the output as a single String
' outtext = ActiveXPosh.OutputString
Debug.Print outtext

' Clear Immediate
Application.Wait (Now + TimeValue("0:00:02"))
Debug.Print Now
Application.SendKeys "^g ^a {DEL}"

End Sub

The result is :

image

I chose to work using an interim CSV file, because this is the fastest and most compatible with Excel. Smile

You can of course write cell per cell or an PowerShell array to Excel. But it will take you forever to make it happen. Sad smile

As you can imagine I can now get O365 and Azure data straight in Excel …

If you put a password on the macro you can safely hand it out in your organization !

 

Enjoy!

Advertisements

PowerShell – Generating XLSX files without Excel

May 31, 2015

Ever got the need to create excel files without the need of having Excel installed.

Well start reading here http://ramblingcookiemonster.github.io/PSExcel-Intro/

The Powershell module is based on EPPLus Library.

Using this nice PSExcel Module you can do a lot.

First thing is to download the Module and install it in this location.

C:\Users\YourUser\Documents\WindowsPowerShell\Modules\PSExcel

image

Open Powershell ISE and run the Demo Script.

CLS

# Download PSExcel from https://github.com/RamblingCookieMonster/PSExcel
# Unblock the zip
# Extract the PSExcel folder to a module path (e.g. $env:USERPROFILE\Documents\WindowsPowerShell\Modules\)

# Import the module.
    Import-Module PSExcel    #Alternatively, Import-Module \\Path\To\PSExcel

# Get commands in the module
    Get-Command -Module PSExcel

# Get help for a command
    Get-Help Import-XLSX -Full

#Create some demo data
    $DemoData = 1..10 | Foreach-Object{
    
        $EID = Get-Random -Minimum 1 -Maximum 1000
        $Date = (Get-Date).adddays(-$EID)

        New-Object -TypeName PSObject -Property @{
            Name = "jsmith$_"
            EmployeeID = $EID
            Date = $Date
        } | Select Name, EmployeeID, Date
    }

# Export it
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx

# Import it back
    $Imported = Import-XLSX -Path C:\Temp\Demo.xlsx -Header samaccountname, EID, Date

# Open that Excel file...
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx

# Get a workbook
    $Workbook = $Excel | Get-Workbook

# Get a worksheet - can pipe ExcelPackage or Workbook.
# Filtering on Name is optional
    $Excel | Get-Worksheet
    $WorkSheet = $Workbook | Get-Worksheet -Name Worksheet1

# Freeze the top row
    $WorkSheet | Set-FreezePane -Row 2

# Save and close!
    $Excel | Close-Excel -Save

# Re-open the file
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx
    
# Add bold, size 15 formatting to the header
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Bold $True -Size 14

# Save and re-open the saved changes
    $Excel = $Excel | Save-Excel -Passthru
    
#  Text was too large!  Set it to 11
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Size 11

    $Excel |
        Get-WorkSheet |
        Format-Cell -StartColumn 1 -EndColumn 1 -Autofit -AutofitMaxWidth 7 -Color DarkRed

# Save and close
    $Excel | Save-Excel -Close

# Search a spreadsheet
    Search-CellValue -Path C:\Temp\Demo.xlsx { $_ -like 'jsmith10' -or $_ -eq 280 }

# Add a table, autofit the data.  We use force to overwrite our previous demo.
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx -Table -Autofit -Force

# Fun with pivot tables and charts! Props to Doug Finke
# Get files in your profile, create a chart breaking down size by file extension
    Get-ChildItem $env:USERPROFILE -Recurse -File |
        Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie

Result is this nice PowerPivot Excel Sheet

image

Great addition for PS Smile


PowerShell – read Excel the fast way

August 24, 2014

In Powershell there are 2 ways of reading excel.

Fast and slow.

The Slow way is using the Excel COM interface.

CLS
$ExcelWB = new-object -comobject excel.application

$ExcelWB.Visible = $True
$Workbook = $ExcelWB.Workbooks.Open("C:\_\Apps\_PowerShell\test.xls") 
$Worksheet = $Workbook.Worksheets.Item(1)
...

The Fast way is using the OLEDB approach

CLS
$strFileName = "C:\_\Apps\_PowerShell\test.xls"
$strSheetName = 'Sheet1$'

$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn
$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()

$DBCounter=$DataReader.FieldCount

"Querying File $Excel ..."

While($DataReader.read())
{
 $Excel = $DataReader[0].Tostring() 
  
 for ($i = 0; $i -lt $DBCounter; $i++) {
  ($DataReader.GetName($i) + " " + $DataReader.GetValue($i))
  }
  echo ' '   
}  

$dataReader.close()
$objConn.close()

So if you have a need for speed, you know which one to choose. Smile