Yet Another -- ExcelCOM UDF

[steve8tch]

Locodarwin (or any of the other guys that are working with Excel)

Good work with this UDF - I have this UDF involved in running quite alot of code spread around the factory where I work.

Have any of you managed to fire up a pivot chart using AutoIt to control Excel?

I have created macros using built in macro function, and got the required code from them. I have also looked at other vbs code - but I still cannot make it work. I have tried adding the macro code as a library to excel and then just control it as an inbuilt macro (that bit works OK) but I cannot add the code to a new excel spread sheet.

I suppose what I am trying to do load up a csv containing report data and programmatically help users generate a pivot table to help them analyse data.

Has anyone cracked this ...

Thanks for any help you can provide.

[Locodarwin]

Steve8tch,

Thanks for the positive feedback.

I intend to write pivot table routines Any Day Now, and definitely as a separate UDF, considering the work involved and the specialized and advanced nature of pivots. Wrapping the pivot table routines will be no small feat.

If you're absolutely chomping at the bit in the meantime, and your particular problem can be solved with the PivotTableWizard function (which is pretty good and can cover most cases), then consider the following reference information on it:

Function:

$oExcel.PivotTableWizard([sourceType], [sourceData], [TableDestination], [TableName], [RowGrand], [ColumnGrand], [saveData], [HasAutoFormat], [AutoPage], [Reserved], [backgroundQuery], [OptimizeCache], [PageFieldOrder], [PageFieldWrapCount], [ReadData], [Connection])

--------------------------------------------------------------------------------

Quickly creates a pivot table and returns a reference to the created PivotTable object.

SourceType

An xlPivotTableSourceType constant indicating the source of the data to use in the pivot table. Can be xlConsolidation, xlDatabase, xlExternal, or xlPivotTable.

SourceData

If SourceType is xlConsolidation, xlDatabase, or xlPivotTable, a Range object containing the source for the pivot table. If SourceType is xlExternal, an array containing the SQL query string used to retrieve the data for the pivot table.

TableDestination

The Range object indicating the location of the upper-left corner for the new pivot table.

TableName

A name to assign to the pivot table. Default is PivotTablen.

RowGrand

True displays grand totals for rows; False omits row totals. Default is True.

ColumnGrand

True displays grand totals for columns; False omits column totals. Default is True.

SaveData

If SourceType is xlExternal, TRue reads all of the fields from the data source and False delays retrieving the data until the pivot cache is refreshed. Default is True.

HasAutoFormat

True automatically adjusts column widths when the pivot table is refreshed; False preserves column widths when the table is refreshed. Default is True.

AutoPage

If SourceType is xlConsolidation, true automatically creates a page field for the consolidation.

Reserved

Do not use this argument.

BackgroundQuery

If SourceType is xlExternal, True queries the data source asynchronously when refreshing the pivot table; False performs synchronous queries. Default is False.

OptimizeCache

True optimizes the pivot cache; False does not optimize. Default is False.

PageFieldOrder

The order in which page fields are added to the page area of the pivot table. Can be xlDownThenOver (default) or xlOverThenDown.

PageFieldWrapCount

The number of page fields per column in the page area of the pivot table.

ReadData

If SourceType is xlExternal, true reads all of the fields from the data source; False delays retrieving the data until the pivot cache is refreshed. Default is True.

Connection

If SourceType is xlExternal, the ODBC connection string used to connect to the external data source.

---------------------------------------------------------------------------------------------

You'll have to convert the xlConstants used above and use the "Default" keyword for parameters you don't want to specify (or where the above tells you not to specify). The rest should be pretty straightforward. Just open your document containing all the data, and at some point call this function. You can use the code examples in some of my range-specifying parameters to see how to create the range objects needed to make this function work.

Otherwise, you're stuck waiting until I can build the necessary library of routines.

-S

[everseeker]

What sort of formatting? Size? Text wrapping? Joining? Colors? Borders? There are functions for a number of different formatting options.

-S

Hmmm... I am beginning to think that this question may be best placed in the General Support forum... I'll drop it in there...

Thanks for a WONDERFUL tool

[Private]

All credits to those who helped to this UDF.

GREAT WORK !

I'm creating an import function to the database we are waiting for that to develop from our software house, who can do that best as we self!

i'm lovin it!

[Kerros]

Here is a function that I put together to chart some data.

This function isn't going to work for everyone, as it assumes multiple sheets in the same workbook with the data being charted in the same range.

I was going to try pulling everything from a two or three dimension array, but that may be the next version of this function. Until then, this works for me.

expandcollapse popup

; new constants
Const $xlLineMarkers = 65
Const $xlPrimary = 1
Const $xlSecondary = 2

;===============================================================================
;
; Description:      Creates a chart
; Syntax:           _CreateChart($oExcel, $sSheetnames, $sRange, $sXvalues = '',  $sTitle = '', $chartType = $xlLineMarkers, $yAxisTitle = '', $xAxisTitle = '', $sHasLegend = True, $sHasDataTable = False, $sShowLegendKey = False)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sSheetnames - A listing of the sheets that contain data to chart
;                   $sRange - Data Range must be in "R2C8:R17C8" format R = Row C = Column
;                   $sXvalues - values for x axis format is 'Sheet1!R2C1:R17C1' R = Row C = Column
;                   $sTitle - Chart title
;                   $chartType - Chart types a listing of known chart types was found @ http://blogs.ittoolbox.com/visualbasic/munk/archives/excel-charts-9429
;                   $yAxisTitle - Y Axis title
;                   $xAxisTitle - X Axis title
;                   $sHasLegend - True if there is a legend on the right hand side of the chart
;                   $sHasDataTable - True for a data table listing values 
;                   $sShowLegendKey - True to show the legend key
; Requirement(s):   Data to create chart
; Return Value(s):  On Success - Returns 1
; Author:           Kerros at gmail dot com
; Note(s):          This function is setup to take data from multiple sheets from the same workbook, using the same range. It also uses the sheetname as the series name 
;
;===============================================================================
Func _CreateChart($oExcel, $sSheetnames, $sRange, $sXvalues = '',  $sTitle = '', _
        $chartType = $xlLineMarkers, $yAxisTitle = '', $xAxisTitle = '', $sHasLegend = True, $sHasDataTable = False, $sShowLegendKey = False)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    ; We need an empty sheet to make sure nothing is selected when creating a new chart with Charts.Add
    $oExcel.ActiveWorkBook.WorkSheets.Add.Activate
    $oExcel.ActiveSheet.Name = "Delete_Me"

    $sSheetnames_split = StringSplit($sSheetnames, ',')
    $oExcel.Charts.Add
    $oExcel.ActiveChart.ChartType = $chartType
    For $icc = 1 To UBound($sSheetnames_split) - 1
        If $sSheetnames_split[$icc] <> "" Then
            $oExcel.ActiveChart.SeriesCollection.NewSeries
            $oExcel.ActiveChart.SeriesCollection($icc).Values = "=" & $sSheetnames_split[$icc] & "!" & $sRange
            $oExcel.ActiveChart.SeriesCollection($icc).Name = $sSheetnames_split[$icc]
            
            Sleep(100)
        EndIf
    Next
    $oExcel.ActiveChart.SeriesCollection(1).XValues = "=" & $sXvalues
    If $xAxisTitle <> '' Then
        $oExcel.ActiveChart.Axes($xlCategory, $xlPrimary).HasTitle = True
        $oExcel.ActiveChart.Axes($xlCategory, $xlPrimary).AxisTitle.Characters.Text = $xAxisTitle
    EndIf
    If $yAxisTitle <> '' Then
        $oExcel.ActiveChart.Axes($xlValue, $xlPrimary).HasTitle = True
        $oExcel.ActiveChart.Axes($xlValue, $xlPrimary).AxisTitle.Characters.Text = $yAxisTitle
    EndIf
    If $sTitle <> '' Then
        $oExcel.ActiveChart.HasTitle = True
        $oExcel.ActiveChart.ChartTitle.Characters.Text = $sTitle
    EndIf
    $oExcel.ActiveChart.HasLegend = $sHasLegend
    $oExcel.ActiveChart.HasDataTable = $sHasDataTable
    If $sHasDataTable = True Then $oExcel.ActiveChart.DataTable.ShowLegendKey = $sShowLegendKey
    $oExcel.ActiveWorkbook.Sheets("Delete_Me" ).Delete ;Deleting sheet created in the beginning.
    Return 1
EndFunc   ;==>_CreateChart

Questions/constructive comments or ideas welcome.

Kerros

[cramaboule]

This is a great script !

I am using it for one of my script that I made (for my job...) and it is working sooooo good even with the Office 2007 (fr)

Can we add this in autoit in the "offical UDF" ???

[tenken]

Hi everybody,

Someone suggest me to post here for my question. I'm just begin with AutoIT, and I wonder if a command or function exist to move the current active cell ?

In example, I want to make active the cell B2, is it possible and how ?

Thanks

P.S : sorry for my english, I'm french and don't practice a lot ^^

[cramaboule]

Hi everybody,

Someone suggest me to post here for my question. I'm just begin with AutoIT, and I wonder if a command or function exist to move the current active cell ?

In example, I want to make active the cell B2, is it possible and how ?

Thanks

P.S : sorry for my english, I'm french and don't practice a lot ^^

read here !

[tenken]

read here !

Erf, so its not possible, thanks for your answer

[Eigensheep]

so its not possible

Actually, that's not true, but you need the object identifier. Try something like:

$oExcel = _ExcelBookNew()
$oExcel.Range("B2").Activate

Replacing _ExcelBookNew and "B2" as necessary.

[tenken]

Actually, that's not true, but you need the object identifier. Try something like:

$oExcel = _ExcelBookNew()
$oExcel.Range("B2").Activate

Replacing _ExcelBookNew and "B2" as necessary.

Thanks its working

[TomTJ]

Here's a sample code to create a ComboBox

http://www.autoitscript.com/forum/index.ph...t=0#entry524719

I will make a beautiful function later...

Regards

Tom

[Vakari]

I just thought I would throw this in here incase it hasn't been done/mentioned before.

I created a small program that would fill out spreadsheets for me. I discovered that if I accidentally used a locked spreadsheet, or a spreadsheet that contained a few locked cells I was trying to write to, the program would crash hard when it tried to write to the locked cell.

I made a slight modification of the _ExcelWriteCell() function, though it might be best to use your own error check before using _ExcelWriteCell().

Anyway, below is the code. Please note I'm still a relative newbie and it may not be the best thing to use, but it has helped me so far. I have it return 3 when trying to write to a cell that is locked.

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        If $oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Locked = 0 Then;   Added
            $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
            Return 1
        Else;   Added
            SetError(3, 0, 0);  Added
        EndIf;  Added
    Else
        If $oExcel.ActiveSheet.Range($sRangeOrRow).Locked = 0 Then; Added
            $oExcel.Activesheet.Range($sRangeOrRow).Value = $sValue
            Return 1
        Else;   Added
            SetError(3, 0, 0);  Added
        EndIf;  Added
    EndIf
EndFunc

Edited May 21, 2008 by Vakari

[Spiff59]

Can someone explain the unwanted behavior the simple script below exhibits?

When run, it will leave you with copies of all the accessed Excel files sitting in your 'My Documents' folder.

From the documentation, what I was doing seemed pretty straightforward. I thought perhaps it was OS-related, since I was running on Serverr 2003 x64 SP2, but I got the same behavior when ran on XP Pro SP2. I'm either improperly using the Excel functions (leaving out a step or parm?), or maybe it's a timing issue where my loop needs a pause, or to wait for a certain event?

The Excel 2003 spreadsheets are protected, with some cells locked, and I'm running the 1.4 version of the Excel UDF.

Any help is appreciated.

You'll of course have to create dummy spreadsheets as 1.xls, 2.xls, 3.xls and 4.xls in your root directory if you want to try it for yourself).

#include <ExcelCOM_UDF.au3> 

-----------------------------------------------------------------------------
for $x = 1 to 4
    Access_Workbook()
Next
Exit

-----------------------------------------------------------------------------
Func Access_Workbook()
    $FilePath = "C:\" & $x & ".xls" 
    $oExcel = _ExcelBookOpen($FilePath, 0, 1, "", "")
    $Cell1 = _ExcelReadCell($oExcel, 1, 1)
    $Cell2 = _ExcelReadCell($oExcel, 2, 1)
    _ExcelBookClose($oExcel)
EndFunc

Edited May 23, 2008 by Spiff59

[Vakari]

#include <ExcelCOM_UDF.au3> 

for $x = 1 to 4
    Access_Workbook()
Next
Exit

Func Access_Workbook()
    $FilePath = "C:\" & $x & ".xls" 
    $oExcel = _ExcelBookOpen($FilePath, 0, 1, "", ""); You are opening the spreadsheet as read-only
    $Cell1 = _ExcelReadCell($oExcel, 1, 1)
    $Cell2 = _ExcelReadCell($oExcel, 2, 1)
    _ExcelBookClose($oExcel); You are saving the spreadsheet. This defaults the save directory to 'My Documents' when the file is opened as read-only
EndFunc

Try this instead

#include <ExcelCOM_UDF.au3> 
for $x = 1 to 4
    Access_Workbook()
Next
Exit

Func Access_Workbook()
    $FilePath = "C:\" & $x & ".xls" 
    $oExcel = _ExcelBookOpen($FilePath, 0, 1)
    $Cell1 = _ExcelReadCell($oExcel, 1, 1)
    $Cell2 = _ExcelReadCell($oExcel, 2, 1)
    _ExcelBookClose($oExcel, 0)
EndFunc

Edited May 23, 2008 by Vakari

[Spiff59]

Thank you very much.

But now I'm a bit confused...

I assumed that BookClose just killed the handle/object.

I didn't expect a save was also (optionally) part of the process.

So, elsewhere in my program, when I've modified some cells and want to save the workbook, what do I do?

1. Use BookSave followed by BookClose with the "0" parm?

2. Use BookSave alone and omit the BookClose call?

Just to restate my new understanding... Since BookSave accepts no pathname as a parameter, it must default to the original file location. But BookClose (with no parm, or a "1") also performs a save and defaults to My Documents?

If that is correct, I'm just left wondering: Does a BookSave need to be followed by a BookClose, 0 in order to kill the process?

Thank you!

Edited May 23, 2008 by Spiff59

[Vakari]

I assumed that BookClose just killed the handle/object

I didn't expect a save was also (optionally) part of the process.

_ExcelBookClose($oExcel, 0) - Closes the book without attempting to save

_ExcelBookClose($oExcel, 1) - Saves the book then closes it

If the workbook was opened as read-only, you cannot save it to its present location. You must select a different location. It seems that the default location for this is the My Documents folder.

So, elsewhere in my program, when I've modified some cells and want to save the workbook, what do I do?

In your script, you opened each spreadsheet as read-only.

$oExcel = _ExcelBookOpen($FilePath, 0, 1) - The 0 means excel will be hidden, and 1 indicates to open the file as read-only

You'll need to open it like this:

$oExcel = _ExcelBookOpen($FilePath, 0, 0) - You don't really need the second 0, as it defaults to 0

Now, when you close it with _ExcelBookClose($oExcel, 1) it will be saved at its current location

Just to restate my new understanding... Since BookSave accepts no pathname as a parameter, it must default to the original file location.

Yes, BookSave saves it at its current location, as long as the spreadsheet is not read-only.

But BookClose (with no parm, or a "1") also performs a save and defaults to My Documents?

BookClose is a Save and Close all rolled into one function. It will always attempt to save your document unless you pass 0 as the save parameter.

If your workbook is read-only, it must save it to My Documents since it doesn't have access to the current location.

BookClose($oExcel, 1) will save the book at its current location (ONLY if it is not read-only) and will close it.

If that is correct, I'm just left wondering: Does a BookSave need to be followed by a BookClose, 0 in order to kill the process?

If you are finished modifying the book, you only need to do a BookClose, 1 in order to save, and close the workbook

$oExcel = _ExcelBookOpen($filepath, 0); Book is visible and writable
_ExcelWriteCell($oExcel, "whatever", 1, 2); Puts "whatever" into cell B1
_ExcelWriteCell($oExcel, "something", 1, 3); Puts "something" into cell C1
$oExcel = _ExcelBookClose($oExcel, 1); Saves and closes the book

Edited May 23, 2008 by Vakari

[Spiff59]

Thanks Vakari.

I had no problem with BookOpen in its differing forms. It was the BookCLose parking copies of files in an unexpected place that threw me. I'm not sure of the rationale behind the 'hidden' save attached to the close function, or why the default is not set to "0" or "No save", but now that I'm aware of that peculiarity, I won't make the same mistake again.

Your help is much appreciated.

[Vakari]

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        If $oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Locked = 0 Then;   Added
            $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
            Return 1
        Else;   Added
            SetError(3, 0, 0);  Added
        EndIf;  Added
    Else
        If $oExcel.ActiveSheet.Range($sRangeOrRow).Locked = 0 Then; Added
            $oExcel.Activesheet.Range($sRangeOrRow).Value = $sValue
            Return 1
        Else;   Added
            SetError(3, 0, 0);  Added
        EndIf;  Added
    EndIf
EndFunc

Ok I just found out this sucks.

$oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Locked on a cell marked as locked will return True whether the sheet is protected or not. So even if the sheet is unprotected, a cell marked as 'locked' will not be written to. I've played around a little in Excel to find out if a sheet is protected and I can't seem to figure it out. I can use ThisWorkbook.ProtectStructure but it only returns True if the workbook is protected. I can't find an equivalent value for just the sheet. Does anyone know how I can find this out?

[ArviUTA]

A simple "example use" script has been attached to the first post. More examples to follow.

-S

good work was very useful in my excel automation.