Update the Excel UDF that comes with AutoIt

[lolipop]

Thanks. It work fine.

[water]

Thanks for the feedback

I will change the UDF accordingly.

[lolipop]

I had another problem. I tired using the _Excel_BookSaveAs function with the overwrite option set as true but it seems to be unable to save properly. I encounter @error number 4 which I understand is workbook file exist but unable to be deleted. What do you think is the problem?

Btw, when excel is excessing the workbook, I tried using windows explorer to delete the workbook but was denied as well so I don't think the workbook can be deleted when excel is still holding on to the workbook.

Another bug to report. I have tried the above function to save to another folder and it saved successfully but when I tried to open the workbook, the workbook is corrupted. It seems the iType saved is wrong. It should be $xlWorkbookDefault instead of $xlWorkbookNormal in your UDF.

Edited February 11, 2014 by lolipop

[water]

Do you open workbook A.xls and try to save it as A.xls using _Excel_BookSaveAs?

[lolipop]

Do you open workbook A.xls and try to save it as A.xls using _Excel_BookSaveAs?

Yup but it's .xlxs format instead. Any diff?

[water]

No. You can't use _Excel_BookSaveAs to save an opened workbook with an unchanged name or type. Means opening A.xlsx and saving it as A.xlsx with _Excel_BookSaveAs doesn't work. Use _Excel_BookSave instead.

[lolipop]

No. You can't use _Excel_BookSaveAs to save an opened workbook with an unchanged name or type. Means opening A.xlsx and saving it as A.xlsx with _Excel_BookSaveAs doesn't work. Use _Excel_BookSave instead.

If _ExcelBookSaveAs can't be used then what is the purpose for having the overwrite option? Also, I can't use _ExcelBookSave as I'm trying to save the workbook with password and _ExcelBookSaveAs is the only function that allows me to enter the password option.

Edited February 11, 2014 by lolipop

[water]

If _ExcelBookSaveAs can't be used then what is the purpose for having the overwrite option?

Let's say you open Version1.xls, change it and want to save it as Version2.xls. To overwrite an existing Version2.xls file you use the overwrite flag.

[water]

Microsoft defines the SaveAs method as: "Saves changes to the workbook in a different file".

To open a wrokbook, set the password and save it with the same name I would use:

$oWorkbook.Password = "****"
_Excel_BookSave($oWorkbook, ...)

[lolipop]

Hmm...ok...Thank you.

Also, pls note the other bug ($xlWorkbookDefault) reported in my previous post. Hope it's a valid bug.

Edited February 11, 2014 by lolipop

[lolipop]

Hmm...ok...Thank you.

Also, pls note the other bug ($xlWorkbookDefault) reported in my previous post. Hope it's a valid bug.

I'm looking at the excel saveas method define by microsoft(http://msdn.microsoft.com/en-us/library/office/ff841185%28v=office.14%29.aspx). It seems we can use the workbook.saveas method with the Application.DisplayAlerts = False option to allow saving and overwriting the existing workbook.

[water]

I will have a look at the second bug.

Have you found a description on the web what xlWorkbookDefault and xlWorkbookNormal EXACTLY do?

[lolipop]

I will have a look at the second bug.

Have you found a description on the web what xlWorkbookDefault and xlWorkbookNormal EXACTLY do?

I think I know what's wrong. If I use xlWorkbookDefault, I can save the file from .xls to .xlsx or vice versa or any other format and the workbook is works fine. But if I use xlWorkbookNormal, I can't. Saving .xls to .xlsx or vice versa or any other format will cause the excel workbook to be corrupted.

What I found from the web suggested that xlWorkbookNormal is meant for (.xls) and xlWorkbookDefault meant for (.xlsx)

http://www.pcreview.co.uk/forums/activeworkbook-saveas-t3188689.html

[water]

OK, I've changed the default to xlWorkbookDefault. I understand that on Excel < 2007 it results in an .xls file and for Excel >= 2007 in an .xlsx file.

[lolipop]

I'm looking at the _Excel_RangeRead example and I notice this

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

but the problem with this is the worksheet had to be active. Anyone have any idea how to read a whole column value of a non-active excel worksheet?
 

[water]

Replace

$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")

with

$oWorkbook.Worksheets.Item("x").Usedrange.Columns("A:A")

"x" is the index or name of the worksheet you want to access.

[lolipop]

Thanks water. Works perfectly.

Note: I notice a typo error in the _Excel_SheetAdd.htm file. The function was written as _Excel_SheetAddNew instead of _Excel_SheetAdd at the top. Might want to correct that in the future.

Another small typo error in the _Excel_RangeValidate.htm file. Under the explaination for the parameters for $sFormula2. The word validation was missing a "v".

[water]

Thanks for the info about the typos. Will be fixed in the next Beta.

[lolipop]

Hi water,

Does the new UDF comes with the ability to create and read excel form control? I had look thru and I don't think there is any. Correct me if I'm wrong.

At the moment, I need to create a combo form in the excel and place it at a specific cell and then read the value. Any advise or example you can give me on this? TIA 

[water]

You are correct, the new UDF doesn't contain functions to create/read/write Excel form controls.

Could you try the Excel macro recorder and check if VBA code is created when you create a combo form?

This would be easy to translate to AutoIt then.