Excel: Does not start within a schedule task

Hi,

if you have already created a script and within you do some things with Excel by DCOM Excel.Application the script runs well as a user logged to the Desktop but not within a schedule task started by the same user.

More »

Advertisment to support michlstechblog.info

Excel: Add trusted location to execute macros when a document is opened

Hi,

Excel can configured to prevent execute macros when a document is opened.

More »

Powershell: Export a Excel Worksheet as csv file

Hi,

with a few lines Powershell code it is possible to export a Excel Workbook as a csv file.
Define your excel list

$sExcelFile="D:\temp\ExcelFile.xlsx"
$sCSVFile="D:\temp\list.csv"

Open Excel using DCOM
More »

Powershell: Error while calling the SaveAs Method of some Office Applications

Hi,

if you tried to save a word or excel document as another format you got an error. For example, if you want to convert a CSV File to a Excel workbook and save it in Excel 95/97/2003 format.

[reflection.assembly]::LoadWithPartialName("Microsoft.Office.InterOp.Excel")
$oExcel = New-Object -ComObject "Excel.Application"
$sCSVFile="D:\temp\ImportFile.csv"
$oExcel.Visible = $true
$oCSVWorkbook=$oExcel.Workbooks.Add()
$oExcel.Workbooks.OpenText($sCSVFile)
$oCSVWorkbook.SaveAs("D:\temp\test.xls",[Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel9795)
Exception calling "SaveAs" with "1" argument(s): "SaveAs method of Workbook class failed"
At D:\test\CSV2Excel.ps1:29 char:20
+ $xl_workbook.SaveAs <<<< ("D:\Temp\test.xls")
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

More »

Powershell: Control Excel remotely

Hi,

I often have to collect some data and fill out some excel lists with those. Here are a few examples how to control Excel using the COM object with powershell.


###############################################################################
# This script controls Excel remotely
# Author Michael Albert michlstechblog.info
# License: GPL v2
################################################################################
# File must exists
$sInputFile="D:\temp\exceldocument.xlsx"
# Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
# Set Excel visible
$oExcel.Visible = $true
# open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sInputFile)
# Open 1st Worksheet
$oWorksheet = $oExcelDoc.Worksheets.item(1)
# Jump to worksheet (only GUI)
$oWorksheet.Activate()
# Get Rows and columns dimension
$iUsedRangeRow  = $oWorksheet.UsedRange.Rows.Count
$iUsedRangeColumns  = $oWorksheet.UsedRange.Columns.Count
# Auto adjust column with
$oWorksheet.UsedRange.EntireColumn.AutoFit()|out-null
# Read a cell A1 A=1 1=1, Row and columns startet by 1
write-host "Cell A1" $oWorksheet.Cells.Item(1,1).Text
# Write text
$oWorksheet.Cells.Item(2,1).value2="New Text"
# Activate cell (only GUI)
$oWorksheet.Cells.Item(1,1).Activate()|out-null
# Set Backgroundcolor of a cell
$oWorksheet.Cells.Item(1,1).Interior.ColorIndex = 4
# With RBG, first load assembly
[reflection.assembly]::LoadWithPartialName("System.Drawing")
# With .NET defined color constants
$oWorksheet.Cells.Item(2,1).Interior.Color=[System.Drawing.Color]::White.ToARgb()
# With  RBG Color
$oWorksheet.Cells.Item(2,1).Interior.Color=([System.Drawing.Color]::FromArgb(254,7,7)).ToArgb()
# Set Font Color
$oWorksheet.Cells.Item(2,1).Font.ColorIndex = 1
# Font Size
$oWorksheet.Cells.Item(2,1).Font.Size=12
# Rotate
$oWorksheet.Cells.Item(2,1).Orientation=45
# Bold
$oWorksheet.Cells.Item(2,1).Font.Bold = $true
# Strike through
$oWorksheet.Cells.Item(2,1).Font.Strikethrough=$true
# Write a comment to a cell
$oWorksheet.Cells.Item(2,1).AddComment("This is a comment")|Out-Null

# Format Cell Range
$oWorksheet.Range("A1:B3").Numberformat="00,0"
$oWorksheet.Cells.Item(2,1).NumberFormat="@"
$oWorksheet.Cells.Item(2,1).NumberFormatLocal="@"

# New Worksheet and get object
$oNewWorksheet=$oExcelDoc.Worksheets.add()
# Rename new Worksheet
$oNewWorksheet.Name="My new worksheet"

# Close Document and save
$oExcelDoc.Close($true)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
# Close Excel
$oExcel.Quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

To be continued 🙂

Michael

Original Theme by Schiy · Powered by WordPress