ExcelCOM UDF

[randallc]

ExcelExamples.zipExcelCOM UDF to "current" directory [or ? "include"] (** parameters explained near top of ExcelCOM udf**)

** I am recommending people consider LocoDarwin's

(Yet Another) ExcelCOM UDF

becuse i think it will ultimately do all these functions and get to standard UDF;

Not all there yet, but some features are better already.

cf GioVit has some useful features in his;

2_99 June17th 06 ;paramater for suppress macro (? does not wqork OfficeXP) @cameronsdad

2_95 June17th 06 ; FontSize @blitzkrg; suppress macro security warning; Checkbox read tristate @cameronsdad

2_92; SimuCal DeleteRow, DeleteColumn [****change parms "_XLSetColumnWidth"]

2_90; May02 06; More Borders, etc DickB / corrected single row

2_88; ; May02 06; More Font, etc DickB // &Passwords [rightjustify OK]

Version 2_84; May01 2006; Added Format cell funcs ; Thanks Simulcal

AutoIt Link; Beta version here //** Beta version of AutoIT3 [3.1.1.120..etc] required for Excel COM ;

To simplify ; You only need the file above in include directory; all the other files are examples of usage

EXAMPLE SCRIPS BELOW========================================================

_XLCheckboxExample.au3

_XLSetBorders.au3_XLCopyRangeExample.au3xlpassword.au3_XLArrayExample.au3

_XLCopyRow.au3XLsyntaxshort3.au3

_XLArrayByLastRowEx2.au3_XLArrayExample2Single.au3_XLRowToArrayExample.au3_XLFindRow3.au3

XL_Search.au3ExcelFormulae_Ex2.au3XLActivePropsExample2.au3

XLArrayFromCol.au3XLBookAddExample1.au3XLChart11.au3XLGetSheetNameEx2.au3

XLFormatCurrency.au3XLFormatSortExample.au3XLGetSheetNameEx2.au3XLListViewSel2.au3

XLReadOnlyExample.au3XLRowToStringExample.au3XLSheetsProps.au3XLsyntaxshort1.au3

_XLDeleteRow.au3_XLFormatExample.au3_XLFontsAndFormatExample.au3

ParametersBest, Randall

ExcelCOM Documentation - to be improved!

fast import EDIT Sept17th 1_3_7 - added short fast import facility without having to use macro or Excel import function.

#include<ExcelCom.au3>

_XLcsvPaste(@ScriptDir&"\book1.xls",1,3,5,"Save",@ScriptDir&"\test1.csv")

ExcelCOM Documentation

Best, randall

Edited October 30, 2006 by randallc

[randallc]

Hi,

Quick fix;

Fixed Column [was not accepting "number", only "letter" or "field" before] on above post in this thread.

Best, Randall

[redndahead]

I have been trying to use this. Is there a way to stop having excel open and close for every _ExcelCOM I run?

I've tried the visible and no exit. None of it works.

red

[randallc]

Hi,

Thanks for the feedback; I'll check it again; was working the last time I looked; Excel runs in background and does not open unless I have "Visible" in that parameter.

Can you check AutoIT Beta version number for me (that you are using) and show an example of your scripts?

Thanks, Randall

eg

$var=_ExcelCOM($FilePath,1,"E",7,"Read","NoSave",4,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7"

Beta version up to 3.1.1.73 Edited September 5, 2005 by randallc

[randallc]

Hi,

You might be using the UDF original at the top of the post? messed up "Save", so perhaps it kept opening.

I have simplified the first post; only 1 UDF file so I know we are talking about the same thing; correct file posted at bottom; let me know if you can...

Thanks, Randall

EDIT [if you read this in the 10mins I thought there was a error, I withdrew that]

Edited September 5, 2005 by randallc

[redndahead]

Hi,

Thanks for the feedback; I'll check it again; was working the last time I looked; Excel runs in background and does not open unless I have "Visible" in that parameter.

Can you check AutoIT Beta version number for me (that you are using) and show an example of your scripts?

Thanks, Randall

eg

Beta version up to 3.1.1.73

<{POST_SNAPBACK}>

3.1.1.73

[redndahead]

I have redownloaded it and it does the same thing.

Here is a test script change it so it works.

#include <ExcelCOM.au3>

_ExcelCOM(@ScriptDir & '\Test.xls','Sheet1','A',1,'Into',0,99,0,0,'NOTLastRow','NOTToColumn')
_ExcelCOM(@ScriptDir & '\Test.xls','Sheet1','B',1,'Into',0,98,0,0,'NOTLastRow','NOTToColumn')
_ExcelCOM(@ScriptDir & '\Test.xls','Sheet1','C',1,'Into',0,97,0,0,'NOTLastRow','NOTToColumn')
_ExcelCOM(@ScriptDir & '\Test.xls','Sheet1','D',1,'Into',0,96,0,0,'NOTLastRow','NOTToColumn')
_ExcelCOM(@ScriptDir & '\Test.xls','Sheet1','E',1,'Into',0,95,0,0,'NOTLastRow','NOTToColumn')
_ExcelCOM(@ScriptDir & '\Test.xls','Sheet1','F',1,'Into',0,94,0,0,'NOTLastRow','NOTToColumn')

[redndahead]

Also just a suggestion.

Change

_ExcelCOM($sFilePath,$Sheet="Sheet2",$Column="A",$Row=1,$MEExcelCom="Read",$Save="Save",$ExcelValue=1,$Visible=0,$Exit=0,$LastRow=0,$ToColumn=0)

to

_ExcelCOM($sFilePath,$Sheet="Sheet2",$Column="A",$Row=1,$ExcelValue=1,$MEExcelCom="Read",$Save="Save",$Visible=0,$Exit=0,$LastRow=0,$ToColumn=0)

It's now in order of what you need to use the most so you don't have to enter so many optional paramaters.

red

[randallc]

Hi,

Thanks again.

Your commands work with quotes around the string parameters.

_ExcelCOM(@ScriptDir & '\book1.xls','Sheet1','B',1,'Into','0',98,'0','0','NOTLastRow','NOTToColumn')

I don't understand "func" well enough to know if I have produced this problem or it is intrinsic?

I agree with you idea about the order; i would perhaps have to fiux it so the commands could be redundant a t the end; do you know how/ or want to keep developing it better?

Best randall

PS probably my code is a problem;

if IsString($Sheet) and Number($Sheet)<>0  then $Sheet=Number($Sheet)

if IsString($Column) and Number($Column)<>0  then $Column=Number($Column)

if IsString($Save) and Number($Save)<>0  then $Save=Number($Save)

if IsString($Visible) and Number($Visible)<>0  then $Visible=Number($Visible)

if IsString($Exit) and Number($Exit)<>0  then $Exit=Number($Exit)

if IsString($LastRow) and Number($LastRow)<>0  then $LastRow=Number($LastRow)

if IsString($ToColumn) and Number($ToColumn)<>0  then $ToColumn=Number($ToColumn)

  ;MsgBox(0,"$Column",$Column)

  ;MsgBox(0,"$Save",$Save)

if $Save="Save" then $Save=1

if $Visible="Visible" then $Visible=1

if $Exit="Exit" then $Exit=1

if $LastRow="LastRow" then $LastRow=1

if $ToColumn="ToColumn" then $ToColumn=1

Edited September 5, 2005 by randallc

[randallc]

OK,

Stimulated to improve syntax;

see new post (just variations using original UDF in include, unchanged)

ExcelCOM_UDF brief syntax

Examples;

#include<ExcelCom.au3>

; none of these shorter commands are visible until you say "_XLshow"

dim $FilePath,$var,$LastRow

$FilePath="c:\winword\Excel\Book1.ls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

  $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)

EndIf

$var=_XLread($FilePath,"Sheet2","A",1)

$var=_XLwrite($FilePath,"Sheet2","A1:B7",1,"HELLO crows!")

$var=_XLlastRow($FilePath,"Sheet2")

$var=_XLread($FilePath,"Sheet2","A",1)

$var=_XLread($FilePath,"Sheet2","A",1)

_XLsave($FilePath,"Sheet2")

_XLshow($FilePath,"Sheet2")

msgbox (0,"A1=",$var)

_XLexit($FilePath); Save changes and exit Excel

Best, Randall

[randallc]

Hi,

Update again; file in first post on this thread;

_XLmacroRun added to ExcelCom.au3 UDF [Parameter "Run"] and as brief command.

It seems to me that more complex macros will just use macros already developed in the "Excel" VBA environment, and all we will need to do from AutIT3 is call them (eg as here in "Macro1" in a hidden "Personal.xls" sheet loaded from Office11 "XLStart" folder automatically with every spreadsheet)

_XLmacroRun($FilePath,"Sheet2","persoNAL.XLS!Macro1")

Best, Randall

[DaLiMan]

Hi Randallc,

Just downloaded your files but I got an error.

P:\Downloads\_AutoIt\Excel\ExcelOLD1.au3 - 2 error(s), 2 warning(s)
>AU3Check Ended with Error(s).
>Running: (3.1.1.66):C:\Program Files\AutoIt3\beta\autoit3.exe "P:\Downloads\_AutoIt\Excel\ExcelOLD1.au3"   
C:\Program Files\AutoIt3\Include\ExcelCom.au3 (148) : ==> The requested action with this object has failed.: 
.activesheet.range ($Range).NumberFormat = "General" 
.activesheet.range ($Range).NumberFormat = "General"^ ERROR
>AutoIT3.exe ended.
>Exit code: 0   Time: 6.891

Seems usefull though for I use Excell pretty often.

[randallc]

Hi,

Thanks for looking.

This was a project for which I had a special purpose [serves it well!], and , unfortunately, I am only a hacker of a programmer!

I think my UDF cannot handle cells which cannot be changed to "general" format; and I could not get any advice as to how to give error messages.

I suspect, if you look, your cell being written has an unusual (or at least non-number) format and I would not know how to fix it.

If that is not the case, send me your script and a scrap of the part of the spreadsheet and I'll try it.

I'm still waiting for someone knowledgeable to get enthusiastic (or frustrated!) enough to do a better, more general UDF. (including error checking!)

Best, Randall

Edited September 7, 2005 by randallc

[DaLiMan]

Hi,

Thanks for looking.

This was a project for which I had a special purpose [serves it well!], and , unfortunately, I am only a hacker of a programmer!

I think my UDF cannot handle cells which cannot be changed to "general" format; and I could not get any advice as to how to give error messages.

I suspect, if you look, your cell being written has an unusual (or at least non-number) format and I would not know how to fix it.

If that is not the case, send me your script and a scrap of  the part of the spreadsheet and I'll try it.

I'm still waiting for someone knowledgeable to get enthusiastic (or frustrated!) enough to do a better, more general UDF. (including error checking!)

Best, Randall

<{POST_SNAPBACK}>

Well accually it was your script ExcelOLD1.au3 which I tested from the first post.

It is a DEMO right?

And I do not understand what U mean by:

I think my UDF cannot handle cells which cannot be changed to "general" format;

But that's probably my lack of knowledge......

[randallc]

Hi,

Sorry.

1. "Your script" - I meant where in my script, to be accurate; My script works fine on my computer and all my spreadsheets. which line did not work? - I presume line12 "Add" ?

2. If so, it would be the cell "E7" in your worksheet, or maybe the whole worksheet is protected?; or in some other way prevented from changing cell format as my UDF wanted before "read" or "write"?

3. If you do not use that line , do the other lines all work/ work with different sheets or cells/ ?...... how precise can you be about the problem?

best, Randall

[DaLiMan]

Hi,

Sorry.

1. "Your script" - I meant where in my script, to be accurate; My script works fine on my computer and all my spreadsheets. which line did not work? - I presume line12 "Add" ?

2. If so, it would be the cell "E7" in your worksheet, or maybe the whole worksheet is protected?; or in some other way prevented from changing cell format as my UDF wanted before "read" or "write"?

3. If you do not use that line , do the other lines all work/ work with different sheets or cells/ ?...... how precise can you be about the problem?

best, Randall

<{POST_SNAPBACK}>

Hi,

I commented out all lines now except "write", after some while I got it to work.

But indeed line 12 the "add" function will not work.

No protection is set anywhere in the file and I have "5" in cell E7 so after running I expect it to be "14" but no changes where made.

I am using the complete _ExcelCOM() function instead of the _XLwrite().

Still can't seem to make that one work......

Cool script BTW, I think it will be a nice UDF for future updates.

Thanx for sharing.

Edit: Little typo.

Edited September 7, 2005 by DaLiMan

[randallc]

PS I have made a couple of syntax fixes tonight, but I don't imagine that fixed your running of the demo?.....

{EDIT} - thanks for trying it out and your comments; I'll keep looking at XLwrite and add functions.

Randall

Edited September 7, 2005 by randallc

[DaLiMan]

PS I have made a couple of syntax fixes tonight, but I don't imagine that fixed your running of the demo?.....

{EDIT} - thanks for trying it out and your comments; I'll keep looking at XLwrite and add functions.

Randall

<{POST_SNAPBACK}>

Sorry randal my mistake!!!

_XLwrite() works fine I just forgot to "save" in the script. :"> :">

The Add function I still do not understand.

Does it work at your PC? Do you have a working demo?

Greetings,

[randallc]

Hi,

glad to hear it. -I found some problem spreadsheets where there were named sheets;

I tested more and found I had NAMED the Sheet as "sheet2" in the short calls;

renamed to just number 1 sheet, not named; I hope it has fixed the short calls anyway; -uploaded UDF again.

"Add" works in my "demo" as you called it; I'll have to look at that tomorrow!

Best, Randall

[EDIT] EDIT Sept7th added short "Add"

Edited September 7, 2005 by randallc

[DaLiMan]

Hi,

glad to hear it. -I found some problem spreadsheets where there were named sheets;

I tested more and found I had NAMED the Sheet as "sheet2" in the short calls;

renamed to just number 1 sheet, not named; I hope it has fixed the short calls anyway; -uploaded UDF again.

"Add" works in my "demo" as you called it; I'll have to look at that tomorrow!

Best, Randall

[EDIT] EDIT Sept7th added short "Add"

<{POST_SNAPBACK}>

I don't get it, why won't it work....

Here's what I use. (copied from you)

#include<ExcelCom.au3>
; none of these shorter commands are visible until you say "_XLshow"
dim $FilePath,$var1,$LastRow
$FilePath="C:\A3Test.xls"
If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then
  $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)
EndIf
_XLadd($FilePath,3,"E",7,30)
_XLshow($FilePath,3)
msgbox (0,"_XLadd=",$var1)
_XLexit($FilePath); Save changes and exit Excel

and below is my XL-file for testing.....

A3Test.zip