ExcelCOM UDF

DaLiMan · September 18, 2005

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

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

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

everything was working fine here.. no problems..

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

DaLiMan · September 19, 2005

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

randallc · September 19, 2005

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

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

Hi,

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

Best, Randall

DaLiMan · September 19, 2005

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

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

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

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

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

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

Best, Randall

Edited September 20, 2005 by randallc

randallc · September 20, 2005

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

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

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

Can you try, about line 191..

.Application.DisplayAlerts = 0

Thanks, Randall

DaLiMan · September 20, 2005

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

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

Installed with WinXP and Off.2003

So nothing strange there also..... :oops:

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

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!!!

ExcelCOM UDF

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Top Posters In This Topic

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members