Update the Excel UDF that comes with AutoIt

[BrewManNH]

Do you have the Beta version installed in C:Program Files (x86)AutoIt3 or in C:Program Files (x86)AutoIt3Beta? Because if it's the latter, you're still running it with the production version.

[Tjalve]

Do you have the Beta version installed in C:Program Files (x86)AutoIt3 or in C:Program Files (x86)AutoIt3Beta? Because if it's the latter, you're still running it with the production version.

ill be damed. I thought it "replaced" the old version...

of course, when use the "run beta" command from the menu. Ot works. Sorry for the trouble

[water]

Glad the problem could be solved and it wasn't caused by the UDF.

[gmacted]

Does anyone have any insight into my problem?

I can save a workbook using "Global Const $xlOpenXMLWorkbook = 51 ; Open XML Workbook", but

cannot save a workbook using "Global Const $xlOpenXMLWorkbookMacroEnabled = 52 ; Open XML Workbook Macro Enabled"

I can import the macro into the workbook, but I cannot save it.  I have administrative privleges.  Any ideas?

[water]

Can you please post the statement you use to save the workbook?

Do you know what the enumerations $xlOpenXMLWorkbook and $xlOpenXMLWorkbookMacroEnabled are used for?

[gmacted]

Can you please post the statement you use to save the workbook?

Do you know what the enumerations $xlOpenXMLWorkbook and $xlOpenXMLWorkbookMacroEnabled are used for?

 

I figured out what I was doing wrong:

This statement would cause the workbook not to be saved:

_Excel_BookSaveAs($oWorkbook, "C:temptemp.xlsx", $xlOpenXMLWorkbookMacroEnabled, True)

The file extension was incorrect.  It should be:

_Excel_BookSaveAs($oWorkbook, "C:temptemp.xlsm", $xlOpenXMLWorkbookMacroEnabled, True)

[water]

Glad the problem could be solved and it wasn't caused by the UDF

[FlashpointBlack]

Hey again!

I've run into a little trouble using the _Excel_RangeWrite function. I have a 2D array that is parsed from a CSV file which contains about 46,000 rows and 7 columns of data. When trying to import this into excel using the rangewrite function, it returns "$vRange.Value = $oExcel.Transpose($vValue)^ ERROR".

#include <Excel Rewrite.au3>
#include <CSV.au3>

consolewrite("Opening Excel..." & @CRLF)
$oExcel = _Excel_Open()
consolewrite("Creating Workbook..." & @CRLF)
$oWorkBook = _Excel_BookNew($oExcel, 1)
consolewrite("Adding Sheets..." & @CRLF)
_Excel_SheetAdd($oWorkbook, -1, False, 1, "Master List")
consolewrite("Deleting First Sheet..." & @CRLF)
_Excel_SheetDelete($oWorkbook, 1)
consolewrite("Parsing master..." & @CRLF)
$ArrMaster = _ParseCSV($SaveDirectory & "\Master.csv")
consolewrite("Writing Master to Excel..." & @CRLF)
_Excel_RangeWrite($oWorkbook, 1, $ArrMaster)

Opening the file directly in excel from the _Excel_BookOpenText function works perfectly, but I'm trying to combine multiple large CSV files into one excel book with multiple sheets. any help you could give would be GREATLY appreciated! thank you!

[water]

Depending on the Excel version you use there is a limit of the number of elements the Excel transpose method can process. In this case set parameter $bForceFunc to True.

[FlashpointBlack]

Hi Water, thanks for the quick reply!

I've just tried this, but now I'm getting an unknown function name for _ArrayTranspose. I've double checked, but I don't see that I'm missing any required files. I've got ExcelConstants.au3 and Excel Rewrite.au3 both in my includes directory. is there another that I need?

Thanks again!

[water]

You need to run the Excel rewrite with the latest AutoIt beta version.

[FlashpointBlack]

Aha, thanks! I appreciate the time you've taken to get this working! what a huge improvement from the included.

[water]

Thanks

If there is anything missing I would like to know

[water]

Released Beta 3 of the UDF.
For download and a history of changes please see post #1.

[FlashpointBlack]

One more issue I'm running into: When I use the _Excel_BookOpenText function, it doesn't appear to be returning a valid workbook object. here is a simplified version of the code I'm using:

#include <Excel Rewrite.au3>

$CSVFileName = "C:\test.csv"
$XLSFileName = "C:\test.xls"

consolewrite("Opening Excel..." & @CRLF)
$oExcel = _Excel_Open(true)
$oWB = _Excel_BookOpenText($oExcel, $CSVFileName)
ConsoleWrite("_Excel_BookOpenText @Error: " & @Error & @CRLF)
ConsoleWrite("_Excel_BookOpenText @Extended: " & @Extended & @CRLF)
_Excel_BookSaveAs($oWB, $XLSFileName)
ConsoleWrite("_Excel_BookSaveAs @Error: " & @Error & @CRLF)
ConsoleWrite("_Excel_BookSaveAs @Extended: " & @Extended & @CRLF & @CRLF)

Running this extremely simple script produces the following output in the console:

Opening Excel...
_Excel_BookOpenText @Error: 0
_Excel_BookOpenText @Extended: 0
_Excel_BookSaveAs @Error: 1
_Excel_BookSaveAs @Extended: 0

and results in the file actually being successfully opened, so I'm sure that part works. any suggestions?

edit: and I did just update to beta 3, but it seems like the problem persists in beta 2 as well.
 

second edit: I was thinking as well... is there an option somewhere that I've missed to set column width and row height?

Edited November 2, 2013 by FlashpointBlack

[water]

I will have a look.

BTW:

ConsoleWrite("_Excel_BookOpenText @Error: " & @Error & @CRLF)
ConsoleWrite("_Excel_BookOpenText @Extended: " & @Extended & @CRLF)

will always return 0 for the second line because the first ConsoleWrite resets @error and @extended

[water]

I found the bug. Method OpenText doesn't return the Workbook object

Please replace the last line in function _Excel_BookOpenText

Return $oWorkbook

with

Return $oExcel.ActiveWorkbook ; Method OpenText doesn't return the Workbook object

[FlashpointBlack]

Thanks for the fix and the tip! I had no idea. I guess that's the problem with just ghettoing your way through writing scripts!

Thanks again! I use this UDF constantly, so if I run into any other troubles, I'll be certain to let you know!

[water]

Thanks a lot!

I'm glad someone uses this UDF so bugs will be discovered and it can mature over time. Before it can replace the current UDF that come with AutoIt it should be as bug free as possible

[water]

second edit: I was thinking as well... is there an option somewhere that I've missed to set column width and row height?

No, you didn't miss this functions. This UDF has no formatting functions at the moment.

As most of the Excel formatting methods would lead to an AutoIt function with a single line I haven't implemented any of them.

If anyone can show me a sensible way to implement formatting functions I will be happy do add them.