ExcelCOM UDF

[DaLiMan]

Hi Randal,

I just downloaded and installed everything at home and tested the 'add' - funtion.

It must be me doing something wrong or just having the wrong settings but still I am getting the errors.

Well I think I'll just leave it there and if needed try to go around or something.........

This NEW funtion of yours seems cool.

I have a program using an Excel file which uses an csv import.

I think using your script will be of use for I don't accually have to import the file in Excell.

( At least I think you just hardcopy the data after reading your report.....)

To be continued........

[randallc]

OK!

I have eliminated the format controls from"Add"; hope it works for you now?.... I'm not sure I lose anything by it; was a hangover from a past script, I think, where some formats will not read...

version 1_4_5

best, Randall

[jefhal]

I tried the test file from your first post and got this error message:

>Running: (3.1.1.75):C:\Program Files\AutoIt3\beta\autoit3.exe "C:\Program Files\AutoIt3\Examples\TestExcelUDFs.au3"

C:\Program Files\AutoIt3\Include\ExcelCom.au3 (180) : ==> The requested action with this object has failed.:

.Application.Run($ExcelValue)

.Application.Run($ExcelValue)^ ERROR

>AutoIT3.exe ended.

>Exit code: 0 Time: 110.265

I discovered that to run this test, a macro named Macro1 must exist in the spreadsheet. Got it!

Edited September 18, 2005 by jefhal

[blitzkrg]

everything was working fine here.. no problems..

thanks for the new feature.. i will put it to good use

[DaLiMan]

OK!

I have eliminated the format controls from"Add"; hope it works for you now?.... I'm not sure I lose anything by it; was a hangover from a past script, I think, where some formats will not read...

version 1_4_5

best, Randall

JJJAAAAAAHHHOEOEOEOEOE.........

You've done it!!!

Haven't tried the csv yet, but just thought you'd like to know this first!!!

[randallc]

@Daliman - good news!

Others;

Added sort command; usage;

#include<ExcelCom.au3>

$FilePath=@ScriptDir&"\book1.xls"

$xlAscending = 1

$xlDescending = 2

;_XLsort($FilePath,$Column1,$xlAscending,$Column2,$xlAscending,$SortRange)

;_XLsort($FilePath,"A1",$xlAscending,"B1",$xlAscending,"A1:H22")

_XLsort($FilePath,"H1",$xlAscending,"B1",$xlAscending,"")

Best, Randall

[is8591]

Hi Randall

I just switched to Beta and using excel almost in every script. Could you tell me where can I get a copy of your UDF. would love to give it a run.

Thanks

[randallc]

Hi,

First post in this thread; link also from my signature; let me know problems...

Best, Randall

[DaLiMan]

Hi Randall,

Al works great incl. the new functions!!!

If I want the "long" functions can I just copy the second line from the "short" ones in the ExcelCom UDF??

Greeting,

DaLiMan

[is8591]

Hi,

Just check couple of things - looks good.

But error once - when tried to open file on network and it was already opened by someone else.

If you don't mind - couple suggestions:

#1 - Capability to open file for read only

#2 - Function to get WorkBook info like sheets and ranges

Thanks

[blitzkrg]

something else that would be cool

specify graph type and throw values at it. and it will plot a graph for you.

but that may not be possible?

[randallc]

Hi,

All good so far.

@Daliman; long functions shold work...

@is8591

read only; I will need to look into it.

Copy ranges, you mean?; my next script!

Copy worksheets; next project, I agree.

@blitzkrg

graphs; possible, but a bigger project; do you have any working vba macros that already do this; I only work by converting others' scripts usually, and will have to find one.

best, Randall

** PS for all the above;******

Don't forget you can simply use AutoIt ExcelCom to run your own macros; if you have one working well, show me and I can usually convert it.

Best, Randall

** PS for all the above;******

Edited September 19, 2005 by randallc

[is8591]

@is8591

read only; I will need to look into it.

Copy ranges, you mean?; my next script!

Copy worksheets; next project, I agree.

** PS for all the above;******

Don't forget you can simply use AutoIt ExcelCom to run your own macros; if you have one working well, show me and I can usually convert it.

Best, Randall

** PS for all the above;******

Hi Randall,

What I mean is properties of a workbook. I have couple of scripts that need to operate on all worksheets in a workbook in sequential order so that script can screen special strings in certain locations. Users usually add worksheets to existing workbook as wanted so they can choose the name. So in order to get all worksheets I controlclick File menu select properties and proper Tab - then use onscreen OCR to read the contents. That gives me all worksheets in the workbook. So I can go through each worksheet. OCR takes long time - about 20 secs per screen. I have about 20 stations; each station 3-4 directories and each directory 10 to 15 workbooks. The script becomes slow - OCR itself takes anywhere from 10 to 20 minutes.

May be this type of function has very small usage in general community. But could you at least give me application property to get file properties. I am very new to COM environment but hoping to improve ASAP.

Thanks

[randallc]

You are probably making all this too complex.

You should just be able to go to your Visual Basic editor, find the appropriate commands and how they work; but it script there for a macro

for each workSHEET in workbooks

do your thing

Next

once again, I can convert this to aUTOiT3 com later if needed

Best, Randall

Edited September 20, 2005 by randallc

[randallc]

My goodness!

How come your machine always is different to mine!

Case StringInStr($MEExcelCom, "SaveAs")

.Application.DisplayAlerts = False

.SaveAs ($ExcelValue)

This works on WinXP with Excel97 or Excel 2003.

What versions are you using?

do you have network permissions set or something?

Best, Randall

Edited September 20, 2005 by randallc

[DaLiMan]

Randal, you're the BEST!!!

I just found one minor bug, that is when you save the xlt to an xls wich already exists. (overwrite)

Then this error occurs:

>Running: (3.1.1.73):C:\Program Files\AutoIt3\beta\autoit3.exe "P:\Test\TestScripts\Excel\ExcelTest3.au3"   
C:\Program Files\AutoIt3\Include\ExcelCom.au3 (190) : ==> The requested action with this object has failed.: 
.SaveAs ($ExcelValue) 
.SaveAs ($ExcelValue)^ ERROR
>AutoIT3.exe ended.
>Exit code: 0   Time: 1.976

[randallc]

My goodness!

How come your machine always is different to mine!

Case StringInStr($MEExcelCom, "SaveAs")

.Application.DisplayAlerts = False

.SaveAs ($ExcelValue)

EDIt - Oh, I wonder why it does work for me!

should be

.Application.DisplayAlerts = 0

???

Best, Randall

Edited September 20, 2005 by randallc

[randallc]

Can you try, about line 191..

.Application.DisplayAlerts = 0

Thanks, Randall

[DaLiMan]

I don't know, maybe because it's IBM???

Nope, no setting / restictions as far as I know.

Installed with WinXP and Off.2003

So nothing strange there also.....

Tried changing

.Application.DisplayAlerts = False

into

.Application.DisplayAlerts = 0

without luck.

When I change my xls-filename it works fine, but when I run it again it doesn't

[DaLiMan]

My goodness!

How come your machine always is different to mine!

???

Best, Randall

I know!!!!

Because I'm an IDIOT !!!

Forgot this one....

_XLexit($FilePath); Save changes and exit Excel

Everything works fine now!!!