Excel Example Scripts

[water]

Attached you will find a few examples based on the Excel UDF. Enjoy!

Worksheet Change Event
This example handles the Excel Worksheet change event when a cell has been changed and sets the color of the cell to red.

Example Excel Events.zip

History:

Spoiler

2013-11-07: Added version 1.0.

 

Set color of every second row
This example sets the background color for every second row in the used range to yellow.

Example Color Rows.zip

History:

Spoiler

2013-11-22: Added version 1.0.

Edited July 4, 2016 by water

[lorenkinzel]

Nice example, thanks.

While it runs fine, if I run by pressing f5:

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /prod /AU3Check /in "C:UsersLorenDesktopExcel Rewrite Beta 3Excel Rewrite.au3"
+>06:27:14 Starting AutoIt3Wrapper v.2.1.0.33    Environment(Language:0409  Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64)
>Running AU3Check (1.54.22.0)  from:C:Program Files (x86)AutoIt3
C:UsersLorenDesktopExcel Rewrite Beta 3Excel Rewrite.au3(169,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

Is this due to my current version being 3.3.8.1?

[water]

Correct. To run the new Excel UDF you need the latest AutoIt beta version.

[Chimaera]

I am getting closer to some excel work using the new UDF so this will help

Ok that works  im curious can you add tabs using the new udf?

Nvm _Excel_SheetAdd

Thx

Edited November 7, 2013 by Chimaera

[nitekram]

Correct. To run the new Excel UDF you need the latest AutoIt beta version.

Cannot wait to try this out...if I get the UDF, do I need the beta version still?

[water]

Correct,

the new Excel UDF (and the examples) only run in combination with the latest AutoIt beta.

[artislp]

Correct. To run the new Excel UDF you need the latest AutoIt beta version.

Hi,

I have downloaded the latest beta Excel UDF & copied everything in the installation path of AutoIt.

But when I include Excel Rewrite.au3 in my script, I got below errors:

>Running AU3Check (1.54.22.0)  from:C:Program Files (x86)AutoIt3

C:Program Files (x86)AutoIt3IncludeExcelConstants.au3(29,40) : ERROR: $xlCenter previously declared as a 'Const'.

Global Const $xlCenter = -4108 ; Center

 

I wanted to try the latest UDF as I need to automatically find the range in my excel table.

[l3ill]

You have to intentionally run w/ beta version:

With ALT + F5

or

Tools | Beta Run

Bill

[water]

And you can't include the old and the new Excel UDF in the same script.

[l3ill]

Yours includes all the originals any way oder?

[water]

My rewritten UDF includes all Constants that are used by the UDF itself or which can be passed as parameters.

If the users script or another include has one of this constants defined as well then you get the above error message.

[water]

Example to set the background color of every second row in the used range added.

[water]

Released Beta 4 of the UDF.
For download and a history of changes please see post #1.

[Neutro]

Just a basic script for noobs like me

expandcollapse popup

#Include <Excel Rewrite.au3>


$prog_excel = _Excel_Open() ;opens a new instance of the Excel software

if $prog_excel == 0 then

   msgbox("","Error", "Unable to open MS Excel")

   exit

endif


$workbook = _Excel_BookOpen($prog_excel, "Path to file\Excel_file.xlsx") ; open an existing excel file

if $workbook == 0 then

   msgbox("","Error", "Unable to open the specified excel file")

   exit

endif


;read or write values


$read = _Excel_RangeRead($workbook, $workbook.Activesheet, "A1", 1) ; read the content of the A1 case of the excel file

_Excel_RangeWrite($workbook, $workbook.Activesheet, "TEST", "A1") ; write "TEST" in the A1 case of the excel file


; changing fonts / case styles


$prog_excel.Activesheet.range("A1").Font.ColorIndex = 3 ; set font color to red

$prog_excel.Activesheet.range("A1").Font.Bold = True ; set font to bold

$prog_excel.Activesheet.range("A1").HorizontalAlignment = -4108 ; center text in cell


;exit Excel

_Excel_Close($prog_excel) ; closes the instance of the Excel software
 

Edited April 17, 2014 by Neutro

[water]

Released Beta 5 of the UDF.

For download and a history of changes please see post #1.

[KLM]

Hello Everyone,

How to change or apply background color for a cell(s) in a entire row or column ?

I tried font.background, which didn't worked.


Can anyone share an example for changing background color ?

[JLogan3o13]

See here for a quick and dirty.

One cell:

#include <Excel.au3>

$oExcel = _ExcelBookNew()
    $oExcel.Cells(1,2).Interior.ColorIndex = 44

Row:

#include <Excel.au3>

$oExcel = _ExcelBookNew()
    $oExcel.Cells(1,2).EntireRow.Interior.ColorIndex = 44

Column:

#include <Excel.au3>

$oExcel = _ExcelBookNew()
    $oExcel.Cells(1,2).EntireColumn.Interior.ColorIndex = 44

[KLM]

Thanks JLogan3o13.

It Works.

I had tried this:-

expandcollapse popup

;;;; Author: KLM

#include <Excel.au3>
#include <Array.au3>
#include <String.au3>

Func1()

Func Func1()

    Local $cols[5] = ["4", "No.", "Name", "Address", "Phone"]

    Local $data1[5] = ["4", "1", "Hello", "Somewhere", "1234567890"]

    Local $data2[5] = ["4", "2", "World", "Thereandhere", "9012345678"]

    Local $i, $j = 1

    $oExcel = _ExcelBookNew(1)  ;;;; 0 => Not Visible

    _ExcelColumnInsert($oExcel, 1, 4)

    $oExcel.ActiveSheet.Rows(1).RowHeight = 40 ;;;; Only For First Row

    For $i = 1 To $cols[0] Step 1

        _ExcelWriteCell($oExcel, $cols[$i], 1, $i)  ;;;; 1 => First Row

        _ExcelFontSetProperties1($oExcel, 1, $i, 1, $i, True, True, True)

        _ExcelHorizontalAlignSet($oExcel, 1, $i, 1, $i, "center")

        _ExcelVerticalAlignSet2($oExcel, 1, $i, 1, $i, "center")


        ;$oExcel.ActiveSheet.Rows($i).ColumnWidth = 40 ;;;; Also Works For All Rows

        $oExcel.ActiveSheet.Columns($i).ColumnWidth = 40

    Next

    CommonExcelWrites($oExcel, $data1, 2)

    CommonExcelWrites($oExcel, $data2, 3)

    MsgBox(-1, "", @error)

    _ExcelBookClose($oExcel)

EndFunc

Func CommonExcelWrites($oExcel, $data, $RowIndex)

    Local $i

    For $i = 1 To $data[0] Step 1

        _ExcelWriteCell($oExcel, $data[$i], $RowIndex, $i)  ;;;; 1 => First Row

        _ExcelFontSetProperties2($oExcel, $RowIndex, $i, $RowIndex, $i, False, False, False)

        _ExcelHorizontalAlignSet($oExcel, $RowIndex, $i, $RowIndex, $i, "center")

        ;$oExcel.ActiveSheet.Rows($i).ColumnWidth = 40 ;;;; Also Works For All Rows

        $oExcel.ActiveSheet.Columns($i).ColumnWidth = 40

    Next

EndFunc

Func _ExcelFontSetProperties1($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Name = "Tahoma"
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Size = 22 ;.Name = "Arial"
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Bold = $fBold
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Italic = $fItalic
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Underline = $fUnderline
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Color = 0xFFBC7A

        ;$oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Background = 0xFFBC7A


        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Borders.Color = 0x000000
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Borders.Value = 4 ;;;; ( < 14 )
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Borders.Weight = 4

    Else
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Name = "Tahoma"
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Size = 22
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Bold = $fBold
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Italic = $fItalic
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Underline = $fUnderline
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Color = 0xFFBC7A


        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Borders.Color = 0x000000
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Borders.Value = 4 ;;;; ( < 14 )
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Borders.Weight = 4

    EndIf
    Return 1
EndFunc


Func _ExcelFontSetProperties2($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Name = "Tahoma"
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Size = 22 ;.Name = "Arial"
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Bold = $fBold
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Italic = $fItalic
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Underline = $fUnderline
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Color = 0x000000

        ;$oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Font.Background = 0xFFBC7A

        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Borders.Color = 0x000000
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Borders.Value = 4 ;;;; ( < 14 )
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).Borders.Weight = 4

    Else
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Name = "Tahoma"
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Size = 22
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Bold = $fBold
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Italic = $fItalic
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Underline = $fUnderline
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Color = 0x000000


        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Borders.Color = 0x000000
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Borders.Value = 4 ;;;; ( < 14 )
        $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Borders.Weight = 4

    EndIf
    Return 1
EndFunc

Func _ExcelVerticalAlignSet2($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        Switch ($sHorizAlign)
            Case "left"
                $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).VerticalAlignment = $xlLeft
            Case "center", "centre"
                $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).VerticalAlignment = $xlCenter
            Case "right"
                $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd) ).VerticalAlignment = $xlRight
        EndSwitch
    Else
        Switch ($sHorizAlign)
            Case "left"
                $oExcel.Activesheet.Range($sRangeOrRowStart).VerticalAlignment = $xlLeft
            Case "center", "centre"
                $oExcel.Activesheet.Range($sRangeOrRowStart).VerticalAlignment = $xlCenter
            Case "right"
                $oExcel.Activesheet.Range($sRangeOrRowStart).VerticalAlignment = $xlRight
        EndSwitch
    EndIf
    Return 1
EndFunc   ;==>_ExcelVerticalAlignSet

[water]

Could you please post such questions in the General Help and Support forum?

This is for Example scripts like I posted in post #1 AND they are for the rewritten Excel UDF that comes with the latest beta of AutoIt.

Thanks