242 replies to this topic

[randallc]

 ExcelExamples.zip   24.04K   3674 downloadsExcelCOM 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   1.31K   853 downloads
 _XLSetBorders.au3   738bytes   646 downloads  _XLCopyRangeExample.au3   214bytes   696 downloads  xlpassword.au3   1.02K   645 downloads[attachment=8325

:attachment]
 _XLCopyRow.au3   720bytes   650 downloads[attachment=8429

:attachment]
 _XLArrayByLastRowEx2.au3   811bytes   566 downloads  _XLArrayExample2Single.au3   643bytes   534 downloads  _XLRowToArrayExample.au3   720bytes   593 downloads[attachment=8413

:attachment]
 XL_Search.au3   969bytes   716 downloads  ExcelFormulae_Ex2.au3   779bytes   598 downloads  XLActivePropsExample2.au3   1.55K   553 downloads
 XLArrayFromCol.au3   219bytes   520 downloads  XLBookAddExample1.au3   342bytes   523 downloads  XLChart11.au3   1.44K   600 downloads[attachment=8420

:attachment]
 XLFormatCurrency.au3   1.57K   570 downloads  XLFormatSortExample.au3   1.57K   464 downloads  XLGetSheetNameEx2.au3   954bytes   439 downloads[attachment=8424

:attachment]
 XLReadOnlyExample.au3   477bytes   681 downloads  XLRowToStringExample.au3   732bytes   549 downloads  XLSheetsProps.au3   440bytes   505 downloads[attachment=8428

:attachment]
 _XLDeleteRow.au3   1.17K   552 downloads  _XLFormatExample.au3   1.75K   521 downloads[attachment=8699

:attachment]

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

Attached Files

•  _XLArrayExample.au3   458bytes   723 downloads
•  _XLFindRow3.au3   1.37K   565 downloads
•  XLGetSheetNameEx2.au3   954bytes   483 downloads
•  XLListViewSel2.au3   2.59K   520 downloads
•  XLsyntaxshort1.au3   1.26K   491 downloads
•  XLsyntaxshort3.au3   787bytes   486 downloads
•  _XLFontsAndFormatExample.au3   3.83K   530 downloads

Edited by randallc, 30 October 2006 - 12:25 PM.

[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 by randallc, 05 September 2005 - 08:08 AM.

[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 by randallc, 05 September 2005 - 01:02 PM.

[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 by randallc, 05 September 2005 - 02:31 PM.

[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 by randallc, 07 September 2005 - 09:34 AM.

[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 by DaLiMan, 07 September 2005 - 11:54 AM.

[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 by randallc, 07 September 2005 - 12:39 PM.

[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 by randallc, 07 September 2005 - 01:32 PM.

[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.....

Attached Files

•  A3Test.zip   1.61K   352 downloads