ExcelCOM UDF

randallc · July 31, 2005

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.au3 xlpassword.au3 _XLArrayExample.au3

_XLCopyRow.au3 XLsyntaxshort3.au3

_XLArrayByLastRowEx2.au3 _XLArrayExample2Single.au3 _XLRowToArrayExample.au3 _XLFindRow3.au3

XL_Search.au3 ExcelFormulae_Ex2.au3 XLActivePropsExample2.au3

XLArrayFromCol.au3 XLBookAddExample1.au3 XLChart11.au3 XLGetSheetNameEx2.au3

XLFormatCurrency.au3 XLFormatSortExample.au3 XLGetSheetNameEx2.au3 XLListViewSel2.au3

XLReadOnlyExample.au3 XLRowToStringExample.au3 XLSheetsProps.au3 XLsyntaxshort1.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 · August 1, 2005

Hi,

Quick fix;

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

Best, Randall

redndahead · September 5, 2005

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 · September 5, 2005

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 · September 5, 2005

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 · September 5, 2005

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 · September 5, 2005

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 · September 5, 2005

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 · September 5, 2005

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 · September 6, 2005

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 · September 7, 2005

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 · September 7, 2005

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 · September 7, 2005

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 · September 7, 2005

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 · September 7, 2005

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 · September 7, 2005

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...... :dance:

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

Thanx for sharing. :dance:

Edit: Little typo.

Edited September 7, 2005 by DaLiMan

randallc · September 7, 2005

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 · September 7, 2005

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. :whistle:

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

Greetings,

randallc · September 7, 2005

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 · September 7, 2005

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

Sign In

ExcelCOM UDF

Recommended Posts

randallc

Top Posters In This Topic

Top Posters In This Topic

randallc

redndahead

randallc

randallc

redndahead

redndahead

redndahead

randallc

randallc

randallc

DaLiMan

randallc

DaLiMan

randallc

DaLiMan

randallc

DaLiMan

randallc

DaLiMan

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta