Update the Excel UDF that comes with AutoIt

[FlashpointBlack]

Does this work for you?

 

Hey water, sorry about the long delay, I completely missed this! yes, this works just fine now, thank you!

[FlashpointBlack]

Out of curiosity, is there a short list somewhere you have of functionality that's lost by using Excel 2013, or does it simply not work? I've been thinking on upgrading, and this is the only thing that holds me back from updating from 2007 which turns 7 years old in two days.

[water]

Unfortunately I have no Office 2013 available, so can't test. Just Office 2010.
Maybe on MSDN there is a list. Will check ...

Edited February 9, 2014 by water

[water]

I just scanned this thread. Can't find a problem with 2013.

Which function are you talking about?

[FlashpointBlack]

I haven't tried it at all as I haven't yet purchased it. I noticed in your OP, it says to test with 2003/2007, and I was just wondering if that meant that there were incapatibilities with 2013. If there aren't any known, I'll update! Thanks!

Edited January 28, 2014 by FlashpointBlack

[water]

Just wanted that users with older Office versions and hence older file formats should test. I can't as I have only Office 2010 to play with.

[lolipop]

Hi water,

Hope you can advise me on this. I use to be able to use _ExcelSheetNameGet and _ExcelSheetNameSet from the older Excel UDF to search the excel sheet for some specific name and then do a rename but with the newer Excel Rewrite UDF, I can't seem to be able to do that as those two command are no longer in use. Any advise or example? TIA.

[water]

_ExcelSheetNameGet is one statement:

$sName = $oExcel.ActiveSheet.Name

_ExcelSheetNameSet is one statement:

$oExcel.ActiveSheet.Name = $sName

That's the reason why the functions have been dropped. A single line doesn't need to be wrapped in a function.

Users need to be familiar with the Excel COM a bit.

Edited February 6, 2014 by water

[lolipop]

Hi water,

Thanks for the information above.

Btw, I'm not sure if I'm the only one experiencing this bug but can you try this out and see if your excel workbook is corrupted after running this code?

First create a workbook with the filename 1.xlsx and then save and encrypt the workbook with a password. (I use password "1" in the example below.) After running the code below, the excel workbook seems to be corrupted. I can't see anything when I open the workbook using excel. The worksheets seems to have been deleted. Btw I'm using Office 2010 and Windows 7. 

PS: I have tried running with and without encrypted workbook and the results is the same. Corrupted workbook.

Opt("TrayIconHide", 1)
#include <GuiListView.au3>
#include <Array.au3>
#include <Timers.au3>
#include <Misc.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiButton.au3>
#include <DateTimeConstants.au3>
#include <Excel Rewrite.au3>
#include <ExcelConstants.au3>
#include <Constants.au3>

Global $sFilePath = @ScriptDir & "\1.xlsx"

If FileExists($sFilePath) Then
   Local $oAppl = _Excel_Open(False,Default,False,Default,Default)
   Local $oExcel = _Excel_BookOpen($oAppl,$sFilePath,Default,False,"1",Default)
   Local $aArray = _Excel_SheetList($oExcel)
   _Excel_BookClose($oExcel, True)
   _Excel_Close($oAppl, True, False)
   Exit
EndIf

Edited February 9, 2014 by lolipop

[water]

Can't test at the moment, but I see no obvious error.

_Excel_BookClose or _Excel_Close shouldn't save the Workbook because nothing has been changed.

Can you check the file date/time of the Excel file in Windows Explorer after you have run the script?

The values of @error after each function call would be good to know as well.

I will test as soon as I find some spare time the next few days.

[lolipop]

Can't test at the moment, but I see no obvious error.

_Excel_BookClose or _Excel_Close shouldn't save the Workbook because nothing has been changed.

Can you check the file date/time of the Excel file in Windows Explorer after you have run the script?

The values of @error after each function call would be good to know as well.

I will test as soon as I find some spare time the next few days.

After running the script, the excel workbook modified date/time is changed. It seems to be saving the workbook. The value of @error is 0 for both the bookclose and close function.

[water]

The problem is caused by you setting the Workbook to invisible.

This setting is then stored when the Workbook is being closed and saved.

You either need to set the workbook to visible bevore saving or after the workbook is being opened again.

[lolipop]

The problem is caused by you setting the Workbook to invisible.

This setting is then stored when the Workbook is being closed and saved.

You either need to set the workbook to visible bevore saving or after the workbook is being opened again.

Noted. Thanks...I was trying out and was about to inform you of the same finding. One question, when I try setting the workbook to be visible and exit(auto save changes) The workbook doesn't unhide itself. It seems the changes are saved when hiding(invisible)  but not when I tried to unhide them.

[water]

Seems to be an Excel thingy then.

Edited February 10, 2014 by water

[lolipop]

Seems to be an Excel thingy then.

No, it doesn't seems to be excel error.

When I use the $oExcel = _Excel_BookOpen($oAppl,$sFilePath,Default,False,"1",Default), the excel workbook is hidden so that's the correct behaviour. But if I were to use this $oExcel = _Excel_BookOpen($oAppl,$sFilePath,Default,True,"1",Default) on a already hidden workbook, it doesn't seems to unhide the workbook. Doesn't that seems to be a problem?

PS: I can hide and unhide properly using excel but I can't unhide the workbook via the function.

 

Edited February 10, 2014 by lolipop

[water]

You could change line

If Not $bVisible Then $oExcel.Activewindow.Visible = False

in function _Excel_BookOpen to

$oExcel.Activewindow.Visible = $bVisible

Setting the workbook to invisible only makes sense when _Excel_Open attaches to an already running and visible instance of Excel.

[lolipop]

Thanks water. but it doesn't seems to work. I have try changing the excel_rewrite.au3 from the excel_bookopen function

If Not $bVisible Then $oExcel.Activewindow.Visible = False

to

If Not $bVisible Then $oExcel.Activewindow.Visible = $bVisible

the hidden workbook is still hidden when I use

Local $oAppl = _Excel_Open(True,Default,False,Default,Default)
Local $oExcel = _Excel_BookOpen($oAppl,$sFilePath,Default,True,"1",Default)

water, on 10 Feb 2014 - 11:41 PM, said: 

Setting the workbook to invisible only makes sense when _Excel_Open attaches to an already running and visible instance of Excel.

 I agree on this. but the case now is even if I was to use excel_open in visible instance together with excel_bookopen in visible instance on a already hidden workbook the workbook doesn't unhide itself. Therefore I thought this is a bug which I could report to you since I don't think it make sense that the UDF can hide a workbook but unable to unhide a workbook properly. If you think this is not a bug then by all means just ignore this. thanks.

Edited February 10, 2014 by lolipop

[water]

Does the code I posted above work (without the If par)?

[lolipop]

Does the code I posted above work (without the If par)?

no

[water]

This works. Replace line

If Not $bVisible Then $oExcel.Activewindow.Visible = False

with

$oExcel.Windows($oWorkBook.Name).Visible = $bVisible