PowerShell – Crystal Reports COM Object

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

 

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.

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…

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 … 

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.

 

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

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

But it doesn’t spoil the fun

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

 

Enjoy !

Advertisements

PowerShell – getting Errors using AutoITx3.dll

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

But occasionally you get this error.

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.

After registering both versions this was the result.

Cls

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

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

 

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 => $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

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 ?

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.