ExcelCOM UDF

[randallc]

Hi,

Glad of that!

I found some references to .Application.DislpayAlerts not working, and have changed it in the next version anyway!

Let's hope that doesn't cause trouble!

best, Randall

EDIT- Woops- fixed in 1.6.7

EDIT; It did cause trouble; now ? fixed in func 21stSept

Edited September 22, 2005 by randallc

[DaLiMan]

Let's hope that doesn't cause trouble!

If it does, I'm probably the first to let you know....hahahahaaaa

[is8591]

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

Network admin disabled macros. So I have to rely on script only. Besides I don't think VBA will be able to cross boundaries for processing and updating data into another non-ms application distributed on different stations and in separate subdirectories and files.

I will try to get into COM a little more myself and let you know how it looks.

BTW your function is great.

Thanks

[randallc]

That certainly seems rather unreasonable of administration doesn't it!

In any case please try Excelcom version 1.6.3 now.

It is rather rather than ready, but returns $var as a string, the limited by |, and very easy to display the array or use the array. Please let me know if there are further troubles

#include<ExcelCom.au3>

#include<Array.au3>

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

$var=_XLSheetProps($FilePath)

_ArrayDisplay($var,"Number and name of each sheet")

; read [A1]in each worksheet

global $var1[$var[0]+1]

for $i = 1 to $var[0]

$var1[$i]=_XLread($FilePath,$i,"A",1)

next

_ArrayDisplay($var1,"Cell contents of A1 in each sheet")

Best, Randall.

PS What other properties do you need?

**EDIT; I changed this in 1.6.4 to have the array already available as "return"

Edited September 21, 2005 by randallc

[randallc]

@is8591

Read -only; a workaround for now?;

To open "read only", you may need to open and re-name your file, at least before using my functions; eg

; To open "read only", you may need to open and re-name, at least before using my functions; eg

#include<ExcelCom.au3>

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

$NewPath=@ScriptDir&"\book3.xls"

_XLshow($NewPath,1)

msgbox (0,"_XLread=","_XLread=")

_XLclose($NewPath,0)

$FileRead=FileOpen($FilePath,0)

If $FileRead = -1 Or Not StringInStr($FilePath, ".xl") then

$FileRead = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)

EndIf

_XLSaveAs($FilePath,$NewPath)

_XLclose($FilePath,0)

_XLshow($NewPath,1)

msgbox (0,"_XLread=","_XLread=")

Best, Randall

EDIT -EDIT- Woops- fixed ; needs 1.6.7

Edited September 21, 2005 by randallc

[randallc]

Hi,

I have put that in as a function now;

#include<ExcelCom.au3>

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

_XLReadOnly($ReadOnlyFile)

_XLshow($ReadOnlyFile,1)

msgbox (0,"$FilePath=","$FilePath now ="&$ReadOnlyFile)

1.6.9 Sept21st ReadOnly function -better, ?got it with com

Best, Randall

Edited September 21, 2005 by randallc

[randallc]

Copy from a range in a csv or WorkBook, much like csv, and option for total range, or where to past; in 2 commands, first to copy, second to paste

1.7.1 Sept21st - =_XLCopy, _XLCopyTo

#include<ExcelCom.au3>

$cFilePath=@ScriptDir&"\book3.xls";"\book4xls.csv"

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

$XLCopyRange=_XLCopy($cFilePath,1,"UsedRange")

_XLCopyTo($FilePath,3,"A",5,$XLCopyRange)

MsgBox(0,"","")

_XLshow($FilePath,3)

_XLexit($FilePath,1)

Best, Randall

[randallc]

_XLArrayRead, _ XLArrayWrite _AddGeneral 1.7.8

#include<ExcelCom.au3>

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

;$XLArray=_XLArrayRead($FilePath,1,"A1:D6")

;Make any 2D array you like and call it "$XLArray"

$XLArrayAddress=_XLArrayWrite($XLArray,$FilePath,2,"A1",1)

_XLsort($FilePath,"A1",1,"C1",2,$XLArrayAddress,2)

$XLArray=_XLArrayRead($FilePath,2,$XLArrayAddress)

_XLClose($FilePath,1)

MsgBox(0,"","Sorted")

Randall

[randallc]

Hi blitzkrg

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

Her it is, but pretty basic; can't use many graph types unless developed.

Best randall

#include<ExcelCom.au3>; 1.9.1

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

_XLCreateBlank($FilePath)

$DataString="12,7,6,9,23,45,3,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"

$DataString=StringReplace($DataString,",",@TAB)

_XLpaste($FilePath,1,"A",1,$DataString,1)

$xl3DLine=-4101

$xlRows=1;$xlColumns=2;

;_XLChart($FilePath,$ChartType,$PlotBy,$Title,$Xaxis,$Yaxis,$Zdata,$SortRange,$Sheet)

_XLChart($FilePath,$xl3DLine,$xlRows,"aTitle","aXaxis","aYaxis","aZdata","A1:J2",1)

_XLshow($FilePath,1)

msgbox (0," visible if shown", " visible if shown")

_XLclose($FilePath); no changes and close workboot; not Excel

[blitzkrg]

Awesome! keep up the great work!

[randallc]

Hi,

Some development ? better...

1_9_6; Chart types added as func, and select by name, or default, with options; "Chart" option in Show

Sept 29th 2005; More graph options; paste returns paste range now; blank improved?

#include<ExcelCom.au3>;1.9.6
;more work with no axis titles; not bubble, though
;$Type options are"Line", "Pie","Column","Bar","XYScatter","Surface","Area"
;$Special options are "BarOf","PieOf","Lines","Smooth"; all others are 0/1
;Syntax; $XLChartType=_XLChartType([$Type,[$Special,[$3D,[$Clustered,[$Stacked,[$100,[$Markers,[$Exploded]]]]]]]])
;Syntax; _XLChart($FilePath,$ChartType,$PlotBy,$Title,$Xaxis,$Yaxis,$Zdata,$SortRange,$Sheet)
;other examples;
;$XLChartType=_XLChartType("Area",0,1);,0,0,0,0,0)
;$XLChartType=_XLChartType("Pie",0,1,0,0,0,1,0)
;$XLChartType=_XLChartType()

$FilePath=@ScriptDir&"\MyNewSheet.xls"
_XLCreateBlank($FilePath)
$DataString="Bolts,12,7,6,9,23,45,3,17,18,9"&@CRLF&"Nuts,3,12,7,6,9,23,45,3,17,18"
$DataString=StringReplace($DataString,",",@TAB)
$PasteRange=_XLpaste($FilePath,1,"A",1,$DataString,1)
$xlRows=1;$xlColumns=2;
$XLChartType=_XLChartType("Surface",0,1);,0,0,0,1,0)
_XLChart($FilePath,$XLChartType,$xlRows,"aTitle","aXaxis","aYaxis","aZdata",$PasteRange,1)
_XLshow($FilePath,1,1,1,"Chart")
msgbox (0," visible if shown", " visible if shown")
_XLclose($FilePath,"NoSave");  no save or changes and close workboot; not  Excel

[Bert]

I'm getting a error on line 64, "duplicate function name" I'm running the latest beta.

#include<ExcelCom.au3>

$ReadOnlyFile=@ScriptDir&"\example.xls"

_XLReadOnly($ReadOnlyFile)

_XLshow($ReadOnlyFile,1)

msgbox (0,"$FilePath=","$FilePath now ="&$ReadOnlyFile)

I was trying to do this as a test to see what it does.

Is something wrong, or am I being real stupid here? I made a example file to see if I could get a msgbox to show up.

[Bert]

Some reason, now its working..

[randallc]

hi,

thanks for trying it out;

is it just that example script, or all of them?

That script works fine for me if I just copy it as is.....?

Is that your total script..? it is as though you have "excelCom" loaded or included somewhere else? - I don't understand..

Maybe download ExcelCom again in case download error?.. puzzled!

Best, Randall

[Bert]

I was looking at your script, and wondered if it could do the following: Copy from a row a string in column C to be used in GUICtrlCreateList. The only way to find the row in question is to use is what string is listed in column B. The use of row numbers or column numbers will not work due to the locations are not stable (the spreadsheet is frequently changed)

[Bert]

I was thinking you could change the following function to work like this:

_XLread($sFilePath,$Sheet,[$Column or $ExcelValue], [$Row or $ExcelValue])

Not sure on how to do it, but it would allow for data to be read even if the cell location changes.

[randallc]

Hi,

OK I'll check; I have found a bug too, so will post later.

Best, Randall

[randallc]

hi,

I'm not sure if it helps, but you can read any row number in a range to a String (with commas) from newest function, in 1_9_16.

in relation to your queries, I am not sure what you mean;

Copy from a row a string in column C to be used in GUICtrlCreateList

What format does the string need to be in for this? - do you mean a string with commas from a number of cells, or just a single string?

The only way to find the row in question is to use is what string is listed in column B

Do you mean a range is given in column "B", or a row number, or a range name? -any of those would be easy to read, if in a single cell, then use as "Row" numbers; as below

_XLread($sFilePath,$Sheet,[$Column or $ExcelValue], [$Row or $ExcelValue])

I think that is already the case; see in "parameters"; "Column" can be a name range, "MyData", a Column by letter or number (then need to provide the Row by number), or a range such as "A1", "A10:B20", "C:C", etc. Admittedly, in "Read", it can only refer to one cell, so for rows or ranges;

1. use "Array Read" for reading a range to 2D Array, or, now,

2. use "RowToString" for csv string, or

3. use "CopyFrom" and "CopyTo" and "SaveAs" [csv option ] to put in a DosCSV text file with commas to read as you want with "FileRead" etc.

Best, Randall

Edited October 4, 2005 by randallc

[Bert]

This is what I'm looking for:

(see spreadsheet1.bmp)

The above is a small spreadsheet. The way I'm understanding how your code works, if I need to capture the information in cell B6, I would have to use B6 as the pointer to where in the spreadsheet I need the information. Thats all well and good. If I hard code this location it will work fine.

The problem occures when there is a change in the spreadsheet AFTER I coded it. If I use B6 as my pointer, and someone added a line, say on line 2, everything below that point now points to the wrong item.

(see spreadsheet2.bmp)

I had B6 pointed so I would get LAN, but instead I now get Office 2003, which is incorrect for my needs.

I need it so it would look for the text in column A for Network, and then report back what is posted in the adjacent cell. This way, if a line is added, my code isn't broken. Is this possible?

[randallc]

Hi,

Yes, use the new "_XLRowToString"; easiest to understand?...

for $i=0 to 100

;_XLRowToString($FilePath,$Sheet, $CopyRange,$Line)

$RowAsString=_XLRowToString($ReadXLPath,1, "A:B",$i)

if StringInStr($RowAsString,"network") then exitloop

Next

$AnswerArray=StringSplit($RowAsString,",")

msgbox (0," visible if shown", "$RowNumberToReadToString ="&$AnswerArray[2])

best, Randall

(PS if there is a big range, or a lot of lookups to do, it would be more efficient, probably, to use "Copy" options [se my previous post in this thread] above to a csv file for the range, then process the lines in the file)

Edited October 4, 2005 by randallc