ExcelCOM UDF

[randallc]

Hi @ptrex

Many thanks for your extra help.

If you do get a chance (no urgency!), please check this one, number 9, which does not include the Excel function at all and should have no calls to Excel.

It only checks that your list view is working (it sounds as though it may not be). [debug should be easy and mostly in your original script except for the listview function]

Best, Randall

Edited November 8, 2005 by randallc

[ptrex]

@Hi Randallc

Can you explain how I should use this SQLiteComXLTxt9.au3 script.

When I run this there is file open dialog looking for a text file called AnswerFolders.txt ?

What should be in the file ?

Thanks

[randallc]

Sorry;

The idea was for you to select any text file from the dialogue box to import into the database and listview.

Best, Randall

[ptrex]

OK I will give it a try.

Till later.

[blink314]

Hey Randallc, just downloaded your ExcelCom udf to start seeing if I can use it in my SQLite script. I didn't know what was the most current version so I downloaded the last one in the first post.

Quick bug report: I ran Tidy on your code and it caught 2 IF statements that were not closed with ENDIF. They are on lines 924 and 952.

Kevin

[randallc]

Hi,

thanks for looking; however, I don't get that error report. I did have a special version of tidy because there was an error using only "indent", but I thought with the latest version, 87, that would be a new version. What version are you using of the Auotoit beta and of tidy?; can you also see the date of the tidy.exe file?

Best, Randall.

I would like to know also, because I am due to upload a new version ofExcelCOM!

Edited November 14, 2005 by randallc

[blink314]

I am using 87 beta and Tidy's date says Jan 30 2005. However, when I looked at the code, you were missing the ENDIF's.

Kevin

[randallc]

hi, how strange!

I have downloaded again; I have version 2.49 from the forum site.

There are 953 lines.

lines 923 to end as follows [EndIf on 926 and 952]

if $s_i_Column=1 then

$NewLine=$r

$e=$Line

EndIf

$Array[$r+($n_Index=0)]= $ar_Array[$e][$NewLine]

Next

;_ArrayDisplay($Array,$s_Title&"Line"&$Line)

Return $Array

EndFunc ;==>lf_Array2dDisplay

Func _StringSplit_0($s_String,$s_Delimiter="|",$i_Flag="0")

; SYNTAX _StringSplit_0($s_String[,[$s_Delimiter="|"],[$i_Flag="0"]])

$ar_Array=StringSplit($s_String,$s_Delimiter)

local $ar_Array_0[ubound($ar_Array)-1]

for $i=0 to ubound($ar_Array)-2

$ar_Array_0[$i]=$ar_Array[$i+1]

Next

return $ar_Array_0

EndFunc ;==>_StringSplit_0

func _ArrayTranspose2D( ByRef $ar_Array)

if IsArray($ar_Array) Then

dim $ar_ExcelValueTrans[ubound($ar_Array,2)][ubound($ar_Array,1)] ;ubound($s_i_ExcelValue,2)-1, ubound($s_i_ExcelValue,1)-1)

for $j =0 to ubound($ar_Array,2)-1

for $numb=0 to ubound($ar_Array,1)-1

$ar_ExcelValueTrans[$j][$numb] = $ar_Array[$numb][$j]

Next

Next

$ar_Array=$ar_ExcelValueTrans

Else

MsgBox(0,"","No Array to transpose")

EndIf

EndFunc ;===>_ArrayTranspose2D

perhaps you just had a bad download?

Best, Randall.

Edited November 14, 2005 by randallc

[blink314]

Yeah, what you posted looks fine. Maybe I didn't grab the newest version. Could you make it a little clearer in the first post what exactly is needed to use the udf? It appears there are multiple versions and a few different flavors (short names, normal, etc). Thanks for checking though,

kevin

[randallc]

Hi,

Version number is on the second line so you can check.

there should be only one possible version at the top of the 1st post in 1st thread available; sometimes a recent version at bottom of first post after a recent change.

I have had glitched downloads before; including first try today, and I think that is what happened to you.

Thanks, Randall

[randallc]

Hi,

New Example and new upload Excelcom;

ExcelCOM UDF to "include" directory\\2.62 - Added SheetAdd/ GetSheeetName SheetName, new properties to 2D Array

AutoIt Link; Beta version here //** Beta version of AutoIT3 [3.1.1.87..etc] required for Excel COM ;

Best, randall

EXAMPLE SCRIPS BELOW=================================================================

Add Sheet Example; also 2D Array of open WorkBook and sheetnames

Edited November 14, 2005 by randallc

[ptrex]

@randallc

I have tested your examples using the latest version of XLScom.

- XLRowToStringExample.au3 = OK

- XLReadOnlyExample.au3 = OK

- XLSortExample = OK

- XLRowToStringExample.au3 = OK

- XLActivePropsExample.au3 = OK

- _XLCopyRangeExample.au3 = OK

- AddSheetNameView2DExample.au3 = ?? Creates a blank XLS with a sheet ListView but no data ??

- XLRowToArrayExample.au3 = ?? The numbers in showing as columns, I don't know what they refer to.

The numbers showing as rows, the 0 element of the Array is 11, but what does it refer to ?

- _XLArrayExample.au3 = ?? I am not sure if this does, what I should do ?

[randallc]

Hi @ptrex,

Thanks!... OK, I'll look at that and get back to you. It should give me a clue where the glitch lies...

Best, randall

[blitzkrg]

@randallC

is it possible to add a "search" function?

for example.

i have a list of serial number in a spread sheet.

someone emails me a list of 10 and says can you see if those are in your spreadsheet..

i'd like to do something like

_xlsearch (worksheet, <value>)

value being the serial number (or whatever data you need to search for)

is something along those line possible?

thanks

[randallc]

hi,

I am sure it is possible, and I will add something in time;

if you have access to running your own macro in Excel meantime, you could write a macro and call it with ExcelCom macroRun command.

best, Randall

[randallc]

hi,

I am sure it is possible, and I will add something in time;

if you have access to running your own macro in Excel meantime, you could write a macro and call it with ExcelCom macroRun command.

best, Randall

..... based on, perhaps,

With Worksheets(1).UsedRange

FoundList = "Nothing"

Set FoundObject = .Find(5)

If Not FoundObject Is Nothing Then

FoundList = FoundObject.Address

firstAddress = FoundObject.Address

Do

Set FoundObject = .FindNext(FoundObject)

FoundList = FoundList + "|" + FoundObject.Address

Loop While Not FoundObject Is Nothing And FoundObject.Address <> firstAddress

End If

End With

MsgBox (FoundList)

Randall

[randallc]

OK,

Here's a prototype, not yet in Excelcom with its protection!

;func _XLSearch($s_FilePath,$s_i_Sheet,$s_i_ExcelValue,$s_i_Visible)

func _XLSearch($s_FilePath,$s_i_Sheet,$s_i_ExcelValue,$s_i_Visible)

local $s_FirstAddress,$s_FoundObject

$o_Excel=objget($s_FilePath)

if not isobj($o_Excel) then MsgBox(0,"","Error")

$o_Excel.Windows (1).Visible = 1; Set the first worksheet in the workbook visible

$o_Excel.Worksheets ($s_i_Sheet).Activate

$o_Excel.ActiveSheet.Visible = $s_i_Visible

With $o_Excel.Worksheets($s_i_Sheet).UsedRange

$s_FoundList = "Nothing"

$s_FoundObject = .Find($s_i_ExcelValue)

If isobj($s_FoundObject) Then

$s_FoundList = $s_FoundObject.Address

$s_FirstAddress = $s_FoundObject.Address

While 1

$s_FoundObject = .FindNext($s_FoundObject)

if not isobj($s_FoundObject) then exitloop

if $s_FoundObject.Address = $s_FirstAddress then exitloop

$s_FoundList = $s_FoundList & "|" & $s_FoundObject.Address

WEnd

EndIf

EndWith

$o_Excel.close(0)

return $s_FoundList

EndFunc ;==>_XLSearch

Randall

[blitzkrg]

randallc

thanks for that.. i'll give it a try as soon as i get home.. i'm on vacation for the weekend..

does the output of your code return the cell?

xlsearch (worksheet,<value>

result would be (for example) A3

or multiple A3,B3,D5

?????????

or is it just true/false output?

[randallc]

Hi,

Gives string "Nothing"

or

String with delimiter "|" for easy Array of addresses

Best, Randall

Edited December 16, 2005 by randallc

[blitzkrg]

@randallC - Your search function is working out nice.

However i'm having trouble with "saving"

basically what i have is this

2 spreadsheets with many columns and many rows.

I'm reading (using xlread) a column of serial numbers, i'm doing this cell by cell.

so once it reads the value (works fine)

it then takes that result and goes to the other spreadsheet.

it then searches that spreadsheet for the result from the 1st spreadsheet.

it will either find it or not.

If it does, it will write the location into the 1st spreadsheet, or write "not found" into the 1st spreadsheet.

The problem is this - after every single write, it save the spreadsheet.

i tried every options "other", "NOSave", 0, 1

tried with XLwrite, XLpaste, excelcom(xxxxxxx,"Into","nosave") (formatting aside of course)

here is a snip of what i tried

;   $writeme = _xlwrite($readxlpath,1,"N",$rownum2,"Not Found",1)
;   $writeme = _xlpaste($readxlpath,1,"N",$rownum2,"Not Found","NOSAVE",0,1)
    $writeme = _ExcelCOM($readxlpath,1,"N",$rownum2,"Into",0,"Not Found",1,0,0,0)

it seems i cant turn off the "save" feature to save my life..

any ideas?