PowerShell – Crystal Reports COM Object

Recently I was going through my files and found (again) Smile 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 … 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.

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 Smile

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

Enjoy !

This entry was posted on Sunday, December 17th, 2017 at 3:52 pm and is filed under Business Applications, COM - Powershell, Crystal Report, Powershell. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

audministrator