ExcelCOM UDF

[DaLiMan]

Hi Randall,

I'm implementing the

_XLSaveAs($CQRFilePath,$CQRNewPath)

_XLwrite($CQRNewPath,1,"G",3,$Today)

_XLshow($CQRNewPath,1)

in a script I have running.

But I'm having trouble with the "manual" calculation because I do not close the file with _XLclose.

I need the file to be left open and be altered by the the user for some extra options.

So, I like the calculation to be left at "automatic"

Can this be done somehow??

[randallc]

Hi,

I have not checked lately, but "_XLCalc($sFilePath)" or "_XLCalc($CQRNewPath)" should just reset that open file to "automatic"

Let me know; Best. Randall

[DaLiMan]

Hi,

I have not checked lately, but "_XLCalc($sFilePath)" or "_XLCalc($CQRNewPath)" should just reset that open file to "automatic"

Let me know; Best. Randall

Don't work, but changing this line (136+137) in your ExcelCom works!!!

$Calculation=.Application.Calculation

.Application.Calculation = $xlCalculationAutomatic

Why do you change the calculation to manual???

[randallc]

oK, thanks,

i'll fix it soon; the issue is related to speed if I have a large sheet to calculate, and want to enter a number of different data; ;I did not want it to calc each time ;I could change it around to have a manual override, though then not consistent...

Oh well.

Randall

[DaLiMan]

the issue is related to speed if I have a large sheet to calculate,

I figured as such....

[randallc]

Hi,

probably best to add a parameter to "Calc", to keep it visible if needed (Show turns it off again) 1_9_28

"_XLCalc($sFilePath,$Visible)"

best, Randall

Edited October 20, 2005 by randallc

[ptrex]

@randallc

I have a question relating to exporting data to excel.

You have a lot of Excel UDF' s available, with a lot of functions. Great job by the way.

Is there a function available to read data from a ListView, and save that to a Excel file.

This is a common feature we might all find handy.

It might be already available, if yes could you point it out in an example.

Than I can include that to my SQLite Application example for exporting data from the database to Excel.

Thanks

[randallc]

Hi, thanks for your comments.

I am sorry I cannot rspond in kind, as i have not been able to test the SQL gear adequately yet. My work SQL program is mostly still hidden tables as far as I can see, and I don't know SQL.

No, i don't, I'm afraid....

Can you get the data from the list view into a 2D array (if it is multiple selected items) and then use my paste (Array) commands? ..XLArrayWrite

or would it be one item at a time (from a string or value) - XLWrite

or one line at a time - XL paste if delimiters can be tabs, (or paste to columns) etc.??...

Best, Randall

[randallc]

PS

If you are asking about writing the GetlistViewItems (or whatever the command!) obviously gary frost for advice; there are usage examples in his "Codewizard" which i used in my "GUICtrlCreateListViewItemExample2.au3" as in my link below too - but none of this is integrated to Excel!

Best, randall

[ptrex]

@randallc

Thanks for your feedback.

I see there is no direct solution.

I will try to make one. As you can see I already did it for exporting to a .TXT file from a ListView.

See my SQLite thread in the Scripts and Scraps.

look for the Gui example :

?http://www.autoitscript.com/forum/index.ph...15entry118281

So it can not be that hard to make it work for Excel

I will let you know, maybe you can add the solution in a standard UDF. Together with the other Excel UDF.

And hopefully your UDF might become a standard UDF.

PS : what do you mean with "Codewizard" from GaFrost. Where can I find it

[randallc]

Hi,

See my SQL query in your thread....

Here's Frosty... and gcriaco;

Codewizard

http://www.autoitscript.com/forum/index.ph...opic=8311&st=45

CodeWiz1.4ZIP

Best, Randall

Edited October 21, 2005 by randallc

[ptrex]

@randallc

Thanks I will reply to your questions in my thread.

Thanks for the link as well.

[randallc]

Hi, @ptrex

I have added

2_3; Oct 23rd05; _XLFromListView(ByRef $sFilePath,$h_Listview,$iListSelected=0,$si_Visible="NotVisible"){swapping Error Handler}

XLFromListView is slow, though, as is ListView generally, especially with sort!

[Also fixed error handler in ExcelCom to swap in and out with yours]

Best, randall

Edited October 23, 2005 by randallc

[ptrex]

@randallc

Sorry for the late reply but i was tied up with some other things.

No succes so far, lots of errors in you ExcelCOM

See here

>"C:\Program Files\AutoIt3\SciTe\CompileAU3\CompileAU3.exe" /run /beta /ErrorStdOut /in "C:\_\Apps\AutoIT\SQLIte\SQLcom2.au3" /autoit3dir "C:\Program Files\AutoIt3\beta" /UserParams

>Running AU3Check...C:\Program Files\AutoIt3\SciTe\Defs\Unstable\Au3Check\au3check.dat

C:\Program Files\AutoIt3\Include\ExcelCom.au3(77,38) : ERROR: ObjEvent() [built-in]called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(84,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(88,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(390,57) : WARNING: $listview: possibly used before declaration.

for $i=0 to _GUICtrlListViewGetItemCount ($listview)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(522,38) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(529,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(546,33) : WARNING: $oMyError: possibly used before declaration.

$HexNumber=hex($oMyError.number,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(554,38) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(561,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(565,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(616,45) : WARNING: $FilePath: possibly used before declaration.

$Var=$Address&"|"&$ExcelValue&"|"&$FilePath&

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(618,38) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(625,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(616,45) : ERROR: $FilePath: undeclared global variable.

$Var=$Address&"|"&$ExcelValue&"|"&$FilePath&

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\_\Apps\AutoIT\SQLIte\SQLcom2.au3 - 11 error(s), 3 warning(s)

As of SqlCom2.au3 it went from OK to worse.

More errors and also the export doesn' t work properly anymore, since you changed to COMMA seperated.

Be aware that in other parts of the world they use comma's for decimal seperaters and a dot for thausend seperators.

So to biuld a universal UDF that support all internationalities, you need to make something that takes care of these differences.

I think it is better to leave the error handler out, untill everything is stable and than take it from there.

Regards,

[randallc]

Hi,

Point taken about the commas; fixed in SQLcom6?

I don't think you've given it much chance, though, as you don't seem to be using the Beta version here?; hence the errors. Your script would probably give similar errors if you used the stable version?

Best, Randall

EDIT SQLcom6 repaired

Edited October 25, 2005 by randallc

[ptrex]

@randallc

I don' t understand if you say I am not using Beta. Line 1 of the errors says this :

>"C:\Program Files\AutoIt3\SciTe\CompileAU3\CompileAU3.exe" /run /beta

Can you explain this ?

I will try your version 6 again today.

Till later.

[ptrex]

Hi again,

This is the result of the last version

The Comma seperator works fine again for the export to Excel.

When using your last ExcelCom version I get COM errors all over.

So I swithed back to the previous version and it works OK.

Apart from the errors before running (and I did use the Beta version).

The export from ListView works as well but still has the Comma seperator problem.

>"C:\Program Files\AutoIt3\SciTe\CompileAU3\CompileAU3.exe" /run /beta /ErrorStdOut /in "C:\_\Apps\AutoIT\SQLIte\SQLcom6.au3" /autoit3dir "C:\Program Files\AutoIt3\beta" /UserParams

>Running AU3Check...C:\Program Files\AutoIt3\SciTe\Defs\Unstable\Au3Check\au3check.dat

C:\Program Files\AutoIt3\Include\ExcelCom.au3(77,38) : ERROR: ObjEvent() [built-in]called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(84,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(88,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(390,57) : WARNING: $listview: possibly used before declaration.

for $i=0 to _GUICtrlListViewGetItemCount ($listview)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(522,38) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(529,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(546,33) : WARNING: $oMyError: possibly used before declaration.

$HexNumber=hex($oMyError.number,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(554,38) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(561,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(565,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(616,45) : WARNING: $FilePath: possibly used before declaration.

$Var=$Address&"|"&$ExcelValue&"|"&$FilePath&

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(618,38) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(625,39) : ERROR: ObjEvent() [built-in] called with wrong number of args.

$sFuncName = ObjEvent("AutoIt.Error")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\_\Apps\AutoIT\SQLIte\SQLcom6.au3(218,7) : ERROR: syntax error

f.

~~~~~^

C:\Program Files\AutoIt3\Include\ExcelCom.au3(616,45) : ERROR: $FilePath: undeclared global variable.

$Var=$Address&"|"&$ExcelValue&"|"&$FilePath&

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\_\Apps\AutoIT\SQLIte\SQLcom6.au3 - 12 error(s), 3 warning(s)

>AU3Check Ended.

>Running: (3.1.1.84):C:\Program Files\AutoIt3\beta\autoit3.exe "C:\_\Apps\AutoIT\SQLIte\SQLcom6.au3"

Till later

[randallc]

Hi,

Can you please check your "autoit3/include" directory, version of excel.com there? - should be 2.17

[And maybe just try the simple example "XLListViewSel1.au3 " - does it give those same errors? - I don't get any!]

Sorry, randall

Edited October 25, 2005 by randallc

[ptrex]

The version in the Include dir is ; Version: 2_3;

I have tried also a more recent one like I told you.

But I hard to find the correct version in you post #1. so many versions available ??

I will try to find version 2.17

[ptrex]

@randallc

Sorry, tested version 2.17 but this one give lot's of COM errors and script errors as mentioned before.

So I best revert to the 2.3 version, which works best for me. besides the script error before running.

like mentioned in the previous posts.