ExcelCOM UDF

[randallc]

Hi,

sorry about that, and thanks for letting me know; I will try and have a look; in the meantime, you might try the previous version (2.62?) Which is actually at the bottom of that first posting this thread.. .I think the paste function was the one that I adjusted in the most recent function.[EDIT - ** -No- see next post 2.67 fixes this]

All the best, Randall.

Edited December 21, 2005 by randallc

[randallc]

Hi,

No, its been wrong all along; about line 409;

should be;

If $s_i_Save == "Save" Or $s_i_Save = 1 Then

not

If $s_i_Save = "Save" Or $s_i_Save = 1 Then

Sorry; I'll repost with new function

Best, Randall

[blitzkrg]

Hi,

No, its been wrong all along; about line 409;

should be;

not

Sorry; I'll repost with new function

Best, Randall

I think that did the trick..

Awesome job!

[randallc]

Hi,

regards the problem with opening password-protected sheets, I will soon incorporate these 2 udfs into ExcelCom;

"_XLReadPassword"

"_XLSaveAsPassword"

As below;

;xlpassword1.au3

#include<ExcelCom.au3>

global $oXLApp

$PassPath="c:\backup\password1.xls"

$OrigPath=$PassPath

$NewPath="c:\backup\book3.xls"

$OrigNew=$NewPath

$NewPath2="c:\backup\book4.xls"

$Pass1="hi"

$PassWrite="hi"

_XLReadPassword( $PassPath,$NewPath, $Pass1 , $PassWrite , 1)

_XLshow($PassPath,1)

MsgBox(0,"","You can now use $NewPath as the functioning copy; save later")

;==============================================================

;processes with $PassPath [is now actually the proxy];==============================================================

_XLSaveAsPassword( $PassPath,$OrigPath, $Pass1 , $PassWrite , 1)

_XLSaveAsPassword( $OrigNew,$NewPath2, $Pass1 , $PassWrite , 1)

_XLshow($OrigNew,1)

msgbox (0," visible if shown", "Proxy file still present, opens OK")

_XLshow($NewPath2,1)

msgbox (0," visible if shown", "Demo 2nd password file has password (as does orig and now changed Password file), opens OK")

if IsObj($oXLApp) then $oXLApp.quit

_XLexit($NewPath,0); no changes and close workboot; not Excel

Func _XLReadPassword(ByRef $ssFilePath,$NewPath, $Pass1 ="", $PassWrite="", $s_i_Visible = 0)

$oXLApp = ObjCreate("Excel.Application")

$oNewPath = ObjGet($NewPath)

If IsObj($oNewPath) Then _XLClose($NewPath, 0);Or (Not StringInStr($ssFilePath, ".csv") And Not StringInStr($ssFilePath, ".xl") And Not StringInStr($ssFilePath, ".txt"))

If Not FileExists($ssFilePath) Then

$ssFilePath = FileOpenDialog("[_XLReadOnly] Go - Choose your input file as inbuilt one not exists", @ScriptDir, "Comma /XL* Files" & " (" & "*.csv;*.xl*;*.txt" & ")", 1);+ $Recurse+ $Recurse

$oTempPath = ObjGet($ssFilePath)

$oTempPath.close (0)

EndIf

$oXLApp.Workbooks.Open ($ssFilePath, 0,0,1,$Pass1 , $PassWrite)

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

$oXLApp.Worksheets (1).Activate

$oXLApp.ActiveSheet.Visible = $s_i_Visible

$oXLApp.Application.DisplayAlerts = 0

$oXLApp.Application.ScreenUpdating = 0

If FileExists($NewPath) Then

FileDelete($NewPath)

EndIf

$oXLApp.ActiveWorkbook.SaveAs ($NewPath, -4143,"" , "")

$oXLApp.Application.DisplayAlerts = 1

$oXLApp.Application.ScreenUpdating = 1

$ssFilePath = $NewPath

EndFunc ;==>_XLReadOnly

Func _XLSaveAsPassword(ByRef $ssFilePath,$PassPath, $Pass1 ="", $PassWrite="", $s_i_Visible = 0)

$oNewPath = ObjGet($ssFilePath)

$oNewPath.Application.DisplayAlerts = 0

$oNewPath.Application.ScreenUpdating = 0

If FileExists($PassPath) Then

FileDelete($PassPath)

EndIf

$oNewPath.SaveAs ($PassPath, -4143,$Pass1 , $PassWrite)

$oNewPath.Application.DisplayAlerts = 1

$oNewPath.Application.ScreenUpdating = 1

EndFunc

[blitzkrg]

@randallc

just wondering if your still working on this?

I use excel all day long at work and i love this UDF i've made tons of scripts to help out all the crap i have to do in excel..

i'm constantly finding new uses for it.

[randallc]

Hi,

Glad to hear it's useful.

I still want to hear of anything that needs fixing....

I'm scared to add new things in case I mess it up; I have other things under construction and don't have the time for de--bugging new stuff a lot.

I want to re-write it sometime as it dates from when I didn't know AutoIt very well; it reallly needs re-structuring.

3 things on the burner sometime.

1. Password funcs [done, not added]

2. Suppress opening error messages [done, not added]

3. printSheet [done, not added]

4. import data [rather than paste] - researched, not yet written [posssible now parameters can be multiple in com; that was only fixed 3 months ago in autIt com function]

5. Import SQL, do queries [not done - looking at it]

Best, randall

[PS Bugs...?

A. XLWrite not working?... use XLPaste

B. Some people; XLArrayWrite not working .. (@ptrex?)

C. Some people; XLAdd not working .. (@daliman?) - ? OK with XLAdd2 - ? OK since "General" fixed in AutoIt com?...]

Also;

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

Edited March 1, 2006 by randallc

[WFC]

Randall, sounds like you are doing great work with Excel. Have you ever thought of doing this with OpenOffice? Open Office can read and write Excel and is free (like AutoIt). I would do it myself if I was knowledgeable enough. There is information on OpenOffice.org about its API and I have found scripts and macros in VB that allow conversion of any MS Office files to OpenOffice files. Also Perl programs to convert .XLS and .SXC files to CSV. I don't know VB or Perl but did play around with the Perl programs enough to allow me to change the program that writes to a CSV to write as an HTML table. Python apparently is another language used with UNO. I don't know any of those languages and have never found anything documented and with examples that allow me to learn it like AutoIt does. My AutoIt program can call the OpenOffice macros and convert XLS or SXC files directly to HTML tables which is really all I care about doing with either Office. Open Office can also create PDF files.

Seems like AutoIt and OpenOffice would be a good fit. Please think about it.

WFC

[blitzkrg]

i tried _getsheetname for the first time and it didnt work

it always gave me the 1st sheet no matter what

i had to add this code to my script to get the active sheet

$oExcelc = ObjGet("","Excel.Application")

$var1e = $oExcelc.ActiveSheet.Name

[randallc]

Hi,

@blitzkrg

i tried _getsheetname for the first time and it didnt work

Sorry about that!

I'll have a look....Workaround; use the array item for the sheet in;

$XLSheetProps = _XLSheetProperties ($s_FilePath, 1)

(see Example for usage)

@WFC

call the OpenOffice macros and convert XLS or SXC files

Hi,

thanks for the info..

[maybe you could give an example of what you mean by "using autoIt to run OO macros..?"]

I really started these funcs to learn obj programming with excel.

I don't have any knowledge of direct use of the API, and I understood there is no OBJECT to use in OpenOffice..

Is this correct?

Best, Randall

Edited March 2, 2006 by randallc

[randallc]

@blitzkrg;

i tried _getsheetname for the first time and it didnt work

Seem to work OK here;

Can you give me an example?

randall

Edited March 2, 2006 by randallc

[blitzkrg]

@blitzkrg;

Seem to work OK here;

Can you give me an example?

randall

Perhaps i'm using it wrong?

Basically I needed a way to get the active sheet.

$var1 = _getsheetname ($app1)

(where $app1 is the handle for my spreadsheet)

msgbox(0,debug,$var1)

That should give me the name of the active sheet right?

It always gave me the name of the 1st sheet

I had to use this to get the active sheet

$oExcelc = ObjGet("","Excel.Application")

$var1 = $oExcelc.ActiveSheet.Name

msgbox(0,debug,$var1)

edit :

also i was wondering if there was another way to approach the passwords in excel?

I use alot of shared spreadsheets, so saving new copies are not an option.

edit2: I cant seem to get either to work now. i dont know what fell apart, but something did.

Here's what i'm trying to do.

I have a spreadsheet with about 10 tabs(sheets)

each spreadsheet contains the same "types" of information

(for example column AJ is going to be serial number on every tab, column AK is going to be asset tag on ever tab, etc etc)

so each tab contains different data, but the same "type" of data.

so i wrote a script to process all this using autoIT v2, it works but it's slow.

i decided to re-write in autoit v3 the other day and am heavily using this UDF to get things done.

when the script runs it ask's me which line number i want to process, at this point i usually pick the tab that i want to process

and then enter the line number to process.

So i need to pass the ACTIVE tab as a variable to the script.

but for some reason no matter what i do, it will jump to some other tab and process

when i enter debugging code like "msgbox" to throw up a box with my variable so i can see it's working right, it always shows me the 1st tab name. and that may or may not be my active tab.

hope all that made sense

Edited March 2, 2006 by blitzkrg

[randallc]

Hi,

Basically I needed a way to get the active sheet.

So your way works!

That's OK?...

USAGE;

1. I have never used filehandles in these UDFs; how do you get the handle? (ie open as read or write?...)

2. You need to supply the sheet number for my macros.[default is always "1"]

3. If you use the 2D Array of sheetprops, the most recently made sheet will be "ubound($ArrayProps,2)-1" ; see that last attached macro. [though most recently made will not necessarily be activesheet...]

4. I can add an "active sheet" return [or change Default to ActiveSheet?..(***Good idea, btw**0), but meantime use yours?.. Don't forget to exit your new object at some stage.

I use alot of shared spreadsheets, so saving new copies are not an option

The orig example I posted suggested using the proxy, using it writing it, then re-saving it with the password once you have finished reading/ writing thta sheet; you could also delete the proxy if too many sheets is a problem?..

[One of the reasons I need to re-write the whole thing, to allow a few extra parameters for this sort of thing to be more general and smoother..]

best, Randall

Edited March 2, 2006 by randallc

[randallc]

Hi,

Or you could change your Excelcom manually to use a parameter; about line 165

With $oExcel

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

if $s_i_Sheet="ActiveSheet" then $s_i_Sheet=.ActiveSheet.Name

.Worksheets ($s_i_Sheet).Activate

Usage;

$s_SheetName=_GetSheetName($s_FilePath,"ActiveSheet","",1)

MsgBox(0,"","_GetSheetName($s_FilePath,1,'MyOnlySheet',1)="&$s_SheetName)

Randall

[randallc]

Hi

Password files;

For ease of usage;

remember in those example scripts.

1. The proxy, after opening, was used by the variable name of the original password file anyway.

2. You need to keep a variable with the original pathname for the later save with password to the original file.

given this, is there still a problem with the large numbers of files?

Best, Randall

[blitzkrg]

Hi,

Or you could change your Excelcom manually to use a parameter; about line 165

Usage;

Randall

Yeah i think it's because i've been trying to use a variable in the _xlread

when i put the sheet number, it works.

i'll see about getting the sheet number (instead of the name) and passing that?

is that going to be a problem? can i use a $variable as long as it contains the number instead of the sheet name?

thanks for your efforts!

[randallc]

Hi,

can i use a $variable as long as it contains the number instead of the sheet name

I use variables for all these parameters; I'm not sure what you mean; I just don't use filehandles, just variables for the full pathname../ sheet number or name...

I'm really not sure; I need you to post some example scripts, I think.

Could you modify my last post to show me what you mean?

Best, Randall

[WFC]

@WFC Hi,

thanks for the info..

[maybe you could give an example of what you mean by "using autoIt to run OO macros..?"]

Actually I don't think that can be done. I have a basic macro installed in OpenOffice that can convert any MS Office file to the equivalent OpenOffice file. I wanted to do the same thing with AutoIt and have it in my program. I think the macros have to be in a language supported by OpenOffice.

[i really started these funcs to learn obj programming with excel.

I don't have any knowledge of direct use of the API, and I understood there is no OBJECT to use in OpenOffice..

Is this correct?]

I am not really knowlegable enough to answer that question but I have a autoit program named OOObject Inspector that shows interfaces, methods and properties.

All I would really like to do is open an XLS or SXC file, read the contents by row and column and convert to an html table. While OpenOffice will write HTML I don't like the format it puts it in but I can fix it once it is in that format. So just open a file and save it as CSV or HTML would be nice. I would like to be able to do it from my program with the conversion hidden.

But if you do in OpenOffice calc what you are doing with Excel you can do it for both.

Best back at you,

WFC

Best, Randall

[blitzkrg]

Hi,

I use variables for all these parameters; I'm not sure what you mean; I just don't use filehandles, just variables for the full pathname../ sheet number or name...

I'm really not sure; I need you to post some example scripts, I think.

Could you modify my last post to show me what you mean?

Best, Randall

$app1 = ("c:\Info.xls")

$ini1 = InputBox("Row Number?", "Please enter the row number:")

$asheet = _GetSheetName($app1,"ActiveSheet","",1)

msgbox(0,"debug",$asheet)

throwing this msgbox up DOES give me the active sheet but when i get to the next line,it doesnt work, it reads a different sheet

$var1 = _XLread ($app1,$asheet,"A", $ini1,1)

$var2 = _XLread ($app1,$asheet,"B", $ini1,1)

$var3 = _XLread ($app1,$asheet,"C", $ini1,1)

if i manually put in the sheet it works

$var1 = _XLread ($app1,3,"A", $ini1,1)

$var2 = _XLread ($app1,3,"B", $ini1,1)

$var3 = _XLread ($app1,3,"C", $ini1,1)

edit1: i think i may have found a temp work around

I renamed each tab

(just an example)

before tabs: computers,printers,scanners

after tabs: computers (1), printers(2), scanners (3)

i figured since i'm having a problem passing the name, i'll pass the number

$oExcelc = ObjGet($app1,"Excel.Application")

$asheet1 = $oExcelc.Application.ActiveWorkBook.ActiveSheet.name

$asheet2 = stringinstr($asheet1,"(")

$asheet = stringmid ($asheet1,$asheet2 + 1,1)

$var1 = _XLread ($app1,$asheet,"A", $ini1,1)

$var2 = _XLread ($app1,$asheet, "B", $ini1,1)

$var3 = _XLread ($app1,$asheet, "C", $ini1,1)

$var4 = _XLread ($app1,$asheet, "N", $ini1,1)

1st test worked.. so i'm gonna keep at it.

any ideas why the "name" would not be working correctly?

edit2: i noticed that i dont have this issue when writing

after I read from 1 spreadsheet, my script writes to another spreadsheet (also with multiple tabs)

however the difference here is that i specify the tab with quotes

$pvar1 = _XLWrite($app2,"Routers","A",$ini2,$var3,1)

dont know if that will help you or not

Edited March 3, 2006 by blitzkrg

[randallc]

Hi,

It all works for me, names and all;

so i am not sure of your problem;

$app1=@ScriptDir&"\Blank6.xls"

$app2=@ScriptDir&"\Blank7.xls"

;$app1 = ("c:\Info.xls")

$ini1 = InputBox("Row Number?", "Please enter the row number:")

$var1 = _XLread ($app1,2,"A", $ini1,1)

$asheet = _GetSheetName($app1,"ActiveSheet","",1)

;msgbox(0,"debug",$asheet)

;throwing this msgbox up DOES give me the active sheet but when i get to the next line,it doesnt work, it reads a different sheet

$var1 = _XLread ($app1,$asheet,"A", $ini1,1)

msgbox(0,"debug="&@CRLF&"$var1=",@crlf&$var1)

$var2 = _XLread ($app1,"Sheet1","B", $ini1,1)

msgbox(0,"debug="&@CRLF&"$var2=",@crlf&$var2)

$var3 = _XLread ($app1,$asheet,"C", $ini1,1)

msgbox(0,"debug="&@CRLF&"$var3=",@crlf&$var3)

Best, randall

[blitzkrg]

Hi,

It all works for me, names and all;

so i am not sure of your problem;

Best, randall

I still couldnt get it to work.

could it be the ver. of autoit?

i'm using 3.1.1.110