ExcelCOM UDF

[randallc]

Hi

[1]***

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

- Did you try this?

[2]***

$f.WriteLine(@CRLF & "The number of Columns in the database is : " & $Count )

Fixed line 218 and re-posted SQLcom6; I am not sure if I ever re-posted after removing commas?; now as SQLcom7;

..... never mind; thanks for your stimulation.. you are a busy boy..?

Best, Randall

Edited October 25, 2005 by randallc

[ptrex]

Hi again,

Still the errors before run !!

(By the way I don' t have the errors If I run scripts without your ExcelCOM included.)

I run the last SQMcom7 with your version of ExcelCOM 2.17 in the include.

The export to XLS still works, the export to TXT still works.

The selective export does not work. It creates a blank excel file.

Maybe we should just hold here.

I will try to review some of the code. But this is not for in the next comming weeks.

Thanks again for as far as we got.

You'll hear from me later on.

[randallc]

Hi,

I have managed to reproduce your error!

It was on a Win2000 machine; all WinXP running OK; I may have to report it as a bug if you are on 2000?

thanks, Randall

[randallc]

@ptrex,

I am looking into the Checker problem (apparently not win2000 problem); meantime you can get it to run by avoiding Scite; just run the Autoit.exe from beta directory, and , when prompted, choose the SQLite.au3 file to run! It overcomes the problem (I think it must be newest version of AUchecker which is the problem!) [maybe re-install older version?....]

best, randall

[randallc]

Or better; there is a "Continue anyway" which will get it all going from Scite; just ignore the aUchecker problem altogether!

Best, Randall

[ptrex]

I was running on an XP machine with a rather new version of the SciTE and the Checked update.

Like you said pressing "Continue any" it runs OK. But it is not as it should.

Let me know when the SciTE are solved, I will give it a try again.

Regards, ptrex

[randallc]

OK -i have now to deal with other errors from AU3check which are real "declaration" issues, so I'll re-post ExcelCom

Here is the "dat" file till next release;

AU3Check.dat

[ptrex]

@randallc

Hi, I installed the latest SCiTE 1.66 and the errcheck.dat along with it. Like suggested in your BUG report post.

Still a lot of errors.

Status at the moment is that ExcelCOM version 2 is still working the best for me.

The export to XLS works fine and the export to TXT works fine.

(all apart from the errors messages before running of cource)

I will wait untill there is a more stable version available.

[randallc]

hi,

Sorry for the hassles.

I have removed the error handler that is not right in Au3Check, and with lots of help, I think have the GUIListView working, no errors coming up.

Let me know......

[2.45; Oct28th - fixes, no errors?....2_3; Oct 23rd05; _XLFromListView//,$si_Visible="NotVisible"]

best, Randall

[ptrex]

Hi,

I tested your last revision and there no errors anymore !! Congratulation.

But this doesn' t mean that all problems have gone. Let me explain.

When I use the Selection Export a blank XLS files appears, no data ?

Than functionaly speaking I think there is still some work to be done. When you compare the data in the listView and in the XLS file after export they are not 1 to 1 equal.

This is because you use the same routine for exporting to XLS as for exporting to TXT.

That' s not the correct methode because it's writing the data from a new SQL query, and not taking it straight from the ListView.

This is an example of reading data from a ListView.

#include <GuiConstants.au3>

#include <GuiListView.au3>

Opt ('MustDeclareVars', 1)

Dim $listview, $Btn_Get, $Btn_Exit, $msg, $Status, $ret

GUICreate("ListView Get Item Text", 392, 322)

$listview = GUICtrlCreateListView("col1|col2|col3", 40, 30, 310, 149)

GUICtrlCreateListViewItem("line1|data1|more1", $listview)

GUICtrlCreateListViewItem("line2|data2|more2", $listview)

GUICtrlCreateListViewItem("line3|data3|more3", $listview)

GUICtrlCreateListViewItem("line4|data4|more4", $listview)

GUICtrlCreateListViewItem("line5|data5|more5", $listview)

$Btn_Get = GUICtrlCreateButton("Get data", 75, 200, 90, 40)

$Btn_Exit = GUICtrlCreateButton("Exit", 300, 260, 70, 30)

$Status = GUICtrlCreateLabel("", 0, 302, 392, 20, BitOR($SS_SUNKEN, $SS_CENTER))

GUISetState()

While 1

$msg = GUIGetMsg()

Select

Case $msg = $GUI_EVENT_CLOSE Or $msg = $Btn_Exit

ExitLoop

Case $msg = $Btn_Get

GUICtrlSetData($Status, "")

Local $a_list, $x

For $x = 0 To _GUICtrlListViewGetItemCount($listview) - 1

If IsArray($a_list) Then

ReDim $a_list[uBound($a_list) + 1]Else

Dim $a_list[1]

EndIf

$a_list[uBound($a_list) - 1] = _GUICtrlListViewGetItemText ($listview, $x)

Next

For $x = 0 To UBound($a_list) - 1

MsgBox(0,"a_list[" & $x & "]",$a_list[$x])

Next

EndSelect

WEnd

Exit

Hope this can help.

You' re not for of anymore.

Regards

[randallc]

Hi,

I don't get a blank file; see image; I'll have to look again what's ahppening to you...

[PS I use the inbuilt ListView UDFs for the listview; I presume you have the latest version]

Randall.

PS - I have had the blank sheets sometimes if there is another window open with "Excel" in the title, which may get activated by mistake during the script;

I need help with those lines?

if $s_i_Visible=1 and WinExists("Microsoft Excel") then

$WinGetTitle=WinGetTitle("Microsoft Excel")

WinActivate($WinGetTitle)

EndIf

Randall

Edited October 29, 2005 by randallc

[ptrex]

As I can see you don' t have the problem. Strange ?

I will try some more. But I have use already a few versions. And no success yet.

PS: to get the tumbnail you need to press the button "include picture into post" are something simular.

[randallc]

Hi,

I have put my func into your main script instead of calling it in ExcelCom; I wonder if it works there; I haven't tried yours yet; does it just plug in to your script?

Best, Randall

[ptrex]

@randallc

Still no succes.

I will try to puzzle something together myself. Just hold on till you hear something from me again.

Regards

[randallc]

hi @ptrex,

1. Can you please check sometime if your "csv" file is being produced for execl and has the line data? It may be that Excel does not recognize your international format as a "csv" file if it has tabs??

$CsvFile =@ScriptDir&"\"&"ListView.csv"

If it is being produced, can Excel open it manually, and does it tab correctly into cells?

2. I know you are looking into how to integrate/ plugin; but are you taking the sqlite commands further too? Can you show me examples of the commands for making, say

"createdb" (already there, but indices and their meanings?...)

"add to row of db", one item of data

"insert to row of db", one item of data

"delete row of data"

"change sort criteria" to a different column

etc

Thanks if you are able; or can direct me somewhere to find the commands. I don't want to work on it if you are already doing it?....

Best, Randall

PS - I have re-written the Excel component in this script; can you tell me if paste line works OK on selected lines from the ListView on your machine?

Thanks, Randall

Edited November 5, 2005 by randallc

[ptrex]

@randallc

I am a little short of time right now. I wll get back to you on Monday OK.

Thanks for the feedback so far.

Till later.

[randallc]

OK

** This version gets the index from the ListView, but quicker; the data from the database

Also, clicking on ListView columns sorts much quicker as data from the database

ListView export with no lines selected exports with the currrent sort

Excel using 2DArray in Array version; text file in TXT version

best, Randall

Edited November 6, 2005 by randallc

[ptrex]

Hi Randallc,

Sorry to say that neither of your last 2 examples work.

The SQLiteComXLArr5.au3 give an error (294,56) : ERROR: _XLArrayWrite() called with wrong number of args

The other one doesn' t run at all.

Your example ExcelCom16. This one runs but only shows the the content of my clipboard entry in it.

Which was "(294,56) : ERROR: _XLArrayWrite() called with wrong number of args" ?

Anyhow. even it all of them were working it would not be correct !!

In the post of Posted Oct 28 2005, 04:39 PM I included an example of how to read data from a listView.

What you did is reading data from an SQL query. Which is not the same.

Look at the export from excel and compare the data you see in the ListView. The sort order is not the same because the source is not the same.

Best of all is to start trying to integrate the example I posted the 28 of October into you ExcelCOM function.

http://www.autoitscript.com/forum/index.ph...ndpost&p=120183

This way the source data would be correct, and then take it a little further with the select rows from the Listview.

I will sent you a message regarding your other questions of SQL statements.

What I did so far, is trying to integrate the example in your ExcelCOM but I had no success. This is because I don' t have the time for it to concentrate on it long enough.

I can only jump in and out, because I am working on other projects at work.

Hope to hear from you soon.

[randallc]

Hi @ptrex, thanks for your comments.

I have explained before that they do work on my machine.

The one most likely to work on your machine, I believe, is the array function, but you had not used the latest version of the Excel UDF.

Additionally, I had already made of the "transfer from ListView", and it is incorporated in the Excel COM UDF.

In the new scripts, I have made to show that using the database (and only the indices from the ListView) were very much faster and also the sorting was dramatically faster, after clicking on the column headers. I'm sorry that you been unable to see all this happening. I will be interested to hear if anybody else is able to get it running!

I take your point as well about the SQL learning; on the other hand I can't improve on the transfer from ListView when it is all working for me!

Randall

[ptrex]

@randallc

Hi randallc. I did a test using your latest ExcelCOM.au3 version 2.49 and SQLiteComXLArr5.au3

And indead the export from LV is working including the sorting and everything.

Stil I have a problem with the export by selection. I get not some data in excel but only 1 record.

And this row is not the one I selected ?

I always seem to get the 1st line in Excel.

If I test your XLListViewSel1.au3 than I still get a blank file. No data is readable. Nevertheless the XLS file is 15 K large. Also a second file .CSV is generated.

BTW : if I sounded a bit negative, don't take it personal, you did a great job so far !!

Let me know if the errors on my site rings a bell on yours.

Regards,