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 – Crystal Reports COM Object

December 17, 2017

Recently I was going through my files and found (again) Smile the very nice Crystal Reports COM libraries

 

image

As you can see these are more then 15 years old !!

But still works like a charm … many years back I created an example in AutoIT using the CRViewer COM object.

image

Actually the Crystal Reports at that time came with some nice COM libraries, which are still my all time favourites.

And tons of functionality to automate your tasks…

image

It can access all kinds of Database formats using ODBC and native DB connections.

As you can see it also has a multitude of Export and printing abilities.

$Crystal_Report = 1
$Data_Interchange = 2
$Record_Style = 3
$CSV = 5
$Tab_Sep_Val = 6
$CharSepVal = 7
$Text = 8
$TabSepText = 9
$Paginated_Text = 10
$Lotus_23WKS = 11
$Lotus_123WK1 = 12
$Lotus_123WK3 = 13
$Word = 14
$Excel_5 = 21
$Excel_5_Tabular = 22
$ODBC = 23
$HTML32 = 24
$Explorer32 = 25
$Excel_7= 27
$Excel_7_Tabular = 28
$Excel_8 = 29
$Excel_8_Tabluar = 30
$PDF = 31
$HTML40 = 32
$Crystal_Report_7 = 33
$Report_Definition = 34
$RTF = 35
$XML = 36

 

PowerShell COM Backwards compatible ?

So the idea now is to see how PowerShell is able to handle these old COM libraries.

Here we go …  Smile

This script will read a Crystal Report 8.5 report using a MS SQL database data source.

Refreshes the data in the report, Exports it to Excel and next E-mails this to 1 or more recipients.

CLS

$Excel_7= 27

$ServerType = "p2lodbc.dll"
$ServerName = "ServerName"
$databaseName = "DatabaseName"
$user = "UserName"
$pswd = "Password"
 
$vFilenameReport = "C:\Deliveries per customer.rpt"
$vFilenameExport = "C:\Deliveries per customer.xls"

$oApp = New-Object -ComObject "CrystalRuntime.Application" # Com Object

# LogOnServer
    $oApp.LogOnServer($ServerType, $ServerName, $databaseName, $user, $pswd)

# Open Report
  $oRpt = $oApp.OpenReport($vFilenameReport, 0) # 0 / 1

# Report Options
  $oRpt.DiscardSavedData() # DiscardSavedData In Report to REFRESH
  $oRpt.EnableParameterPrompting = $False
  $oRpt.DisplayProgressDialog = $False
  $oRpt.MorePrintEngineErrorMessages = $False

# Export Options
Start-Sleep 2

  # $oRpt.ExportOptions.Reset | out-null
   $oRpt.ExportOptions.DestinationType = 1 # 1=>filesystem 
   $oRpt.ExportOptions.FormatType = $Excel_7  # 27=> Excel 7
   $oRpt.ExportOptions.DiskFileName = $vFilenameExport 

  # $oRpt.ExportOptions.ExcelExportAllPages = $True 
  # $oRpt.Export($True) # Export without prompting = False

   $oRpt.Export($False) # Export without prompting = False

# Send Email
 Send-MailMessage -to Your.Name@Company.com `
-from admin@Company.com `
-Subject "Monthly Email Service" `
-body "Hello !!! `nThis is your personal E-mail service.`nPlease find the updates enclosed.`nUntill next time. `nRegards," `
-Attachments $vFilenameExport `
-smtpserver YourEmailServer  

# Remove Attachment File
   Start-Sleep -s 2
   Remove-Item $vFilenameExport

 

If you schedule this script you have created your own reporting platform.

 

image

Keep in mind that you need to you the 32Bit PowerShell version

image

C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe

But it doesn’t spoil the fun Smile

And this proves PowerShell is 100% backwards COM compatible !!

 

Enjoy !


PowerShell – getting Errors using AutoITx3.dll

August 9, 2014

Normally you can get all Methods and Properties of any registered COM object using the Get-member cmdlet.

But occasionally you get this error.

image

The reason is that you have registered the COM object only as x32 bit and not as x64 bit.

2 ways to solve this.

First register both x32 and x64 bit Dll’s.

image

After registering both versions this was the result.

Cls

New-Object -com AutoItX3.Control | Get-Member

image

Lucky for us that AutoIT ships 2 versions. But most of the DLL are either x32 or x64 bit ?

So you can get it running using the x32 bit if Powershell using the x32 bit of the COM Dll and the same for x64.

Or try a hack to register the x32 Bit like this :

The 32-bit one goes into C:\Windows\SysWOW64, and the 64-bit DLL goes into C:\Windows\System32.

And appropriate registry keys in the appropriate location

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724072(v=vs.85).aspx


PowerShell – Using AutoIT COM object

January 21, 2013

 

This example shows how to run the AU3 Com object in Powershell.

cls
Clear-Host
# AutoIT declare object in PS
$oAutoIt = New-Object -comobject AutoItX3.Control

# This will List all the methods and properties of the COM object
$oAutoIt | get-member

# This will create a tooltip in the upper left of the screen
$oAutoIt.ToolTip("This is an AU3 tooltip Example" , 300, 430) 

# Important don't leave a spaces after the properties like this
# is wrong $oAutoIt.Sleep (2000) but like this =&amp;gt; $oAutoIt.Sleep(2000)

$oAutoIt.Sleep(2000)
$oAutoIt.ToolTip("") 

You can use any COM object in Powershell as you can see.

For a .Net assambly or Powershell integration look here

Enjoy !


PowerShell – Export to EXCEL Issue

January 14, 2013

I was trying to use some simple script to create a workbook in Excel 2007 and add some worksheets in it, with data.

Spend hours on it, but it just opened up the Excel application. But no workbook and no sheets ? Crying face

This was the intial code :

CLS
#[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$Excel = new-Object -com Excel.Application
$Excel.visible = $True
$Excel.DisplayAlerts = $False
$ExcelWB = $Excel.Workbooks.Add()

# adding 3 extra sheets
$Sheet = $ExcelWB.Worksheets.Add()
$Sheet = $ExcelWB.Worksheets.Add()
$Sheet = $ExcelWB.Worksheets.Add()

# Assign each worksheet to a variable and
# name the worksheet.
$Sheet1 = $ExcelWB.Worksheets.Item(1)
$Sheet2 = $ExcelWB.WorkSheets.Item(2)
$Sheet3 = $ExcelWB.WorkSheets.Item(3)
$Sheet4 = $ExcelWB.WorkSheets.Item(4)
$Sheet5 = $ExcelWB.WorkSheets.Item(5)
$Sheet6 = $ExcelWB.WorkSheets.Item(6)
$Sheet1.Name = "General"
$Sheet2.Name = "System"
$Sheet3.Name = "Processor"
$Sheet4.Name = "Memory"
$Sheet5.Name = "Disk"
$Sheet6.Name = "Network"
Sleep 2
$ExcelWB.SaveAs('C:\Temp\test.xls',56)
write-host $Error[0]
Sleep 2

$ExcelWB.Close()
$Excel.Quit()

On the second line you can notice the line including

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

When you uncomment it, everything works fine ?

Reason is that I use an English OS with a non English regional settings, which seems to be the issue.