Yet Another -- ExcelCOM UDF

[StevenOfPomroyIL]

Some forum searching should have turned it up. Posted many times in various topics: $oExcel.Run("MacroName")

Do you know how to evaluate VBA inline from an AutoIt Script?

Here's an example, the AutoIt _ExcelReadArray function takes as a parameter the number of cells to be copied to an array ($iNumCells). As you know it's bad practice to use static variables. Excel can get me the dynamic number of cells ($iNumCells) by evaluating the following VBA: Cells(65536, 1).End(xlUp).Row

This VBA starts from the bottom of the spreadsheet and finds row number of the last used cell in column 1.

How do I get AutoIt to force Excel to evaluate this VBA snippet and then pass the value back to AutoIt.

Any thoughts much appreciated.

[StevenOfPomroyIL]

Do you know how to evaluate VBA inline from an AutoIt Script?

Here's an example, the AutoIt _ExcelReadArray function takes as a parameter the number of cells to be copied to an array ($iNumCells). As you know it's bad practice to use static variables. Excel can get me the dynamic number of cells ($iNumCells) by evaluating the following VBA: Cells(65536, 1).End(xlUp).Row

This VBA starts from the bottom of the spreadsheet and finds row number of the last used cell in column 1.

How do I get AutoIt to force Excel to evaluate this VBA snippet and then pass the value back to AutoIt.

Any thoughts much appreciated.

Perhaps the solution is something like:

$dateVal = $oExcel.Run("Sub Lastrow()As Date Lastrow = Cells(65536, 1).End(xlUp).Row End Sub")

[PsaltyDS]

That would look more like a COM method using the current worksheet:

Global CONST $xlUp = -4162

; ...

$iLastRow = $oExcel.ActiveSheet.Range.Cells(65536, 1).End($xlUp).Row

(Not tested.)

[StevenOfPomroyIL]

That would look more like a COM method using the current worksheet:

Global CONST $xlUp = -4162

; ...

$iLastRow = $oExcel.ActiveSheet.Range.Cells(65536, 1).End($xlUp).Row

(Not tested.)

Thank you for the quick response. It's not working yet, but I will play around with it. So, just so I understand. You got $xlUp = -4162 from the OLE/COM Object Viewer?

[PsaltyDS]

Thank you for the quick response. It's not working yet, but I will play around with it. So, just so I understand. You got $xlUp = -4162 from the OLE/COM Object Viewer?

Nope, Googled "Excel Constants xlUp".

[enaiman]

Here is a list of all Excel constants; I have attached it to a post in the General Support forum.

It is a csv format (just change the extension to "csv").

Excel Constants

[PsaltyDS]

I didn't get a valid range object that way, but this tested correctly with Excel 2003:

#include <Excel.au3>

Global CONST $xlUp = -4162

Global $oExcel = _ExcelBookAttach(@ScriptDir & "\Test1.xls")

$iLastRow = $oExcel.ActiveSheet.UsedRange.Rows.Count ; last row in any column
ConsoleWrite("$iLastRow = " & $iLastRow & @LF)

$iLastRow = $oExcel.ActiveSheet.Range("B65535").End($xlUp).Row ; last row column 'B' only
ConsoleWrite("$iLastRow = " & $iLastRow & @LF)

[BlueLord]

Hy,

Where is pivot tabel function, or whow to use it?

sorry for studip question

[Zxian]

I'm having trouble with the _ExcelBookClose() function. It seems as though the function call doesn't recognize my opened Excel file as a valid object type (_Workbook or _Application). Below is my code that I'm trying to do.

expandcollapse popup

#include <Excel.au3>
#include <File.au3>

Local $pathtowow = "C:\Users\Public\Games\World of Warcraft\"
Local $account = "ACCOUNT"
Local $auctionator = "Auctionator.lua"
Local $savedvariables = $pathtowow & "WTF\Account\" & $account & "\SavedVariables\"
Local $aucdatabase = $savedvariables & $auctionator
Local $pathtoMilling = "D:\Documents\My Dropbox\WoW\WoW - Milling Prices AU3.xls"

Local $herbs[7]
$herbs[0] = "Icethorn"
$herbs[1] = "Lichbloom"
$herbs[2] = "Adder's Tongue"
$herbs[3] = "Talandra's Rose"
$herbs[4] = "Goldclover"
$herbs[5] = "Deadnettle"
$herbs[6] = "Tiger Lily"

$oExcelFile = _ExcelBookOpen($pathtoMilling,0)
$oExcelerror = @error
If $oExcelFile = 0 Then
    MsgBox(0,"Excel Open Error",$oExcelerror)
    Exit
EndIf   

MsgBox(0,"Excel handle",$oExcelFile)
For $herb In $herbs
    $oAuction = FileOpen($aucdatabase)
    If $oAuction = -1 Then
        MsgBox(0,"Error","Unable to open Auctioneer database.")
        Exit
    EndIf
    
    While 1
        $line = FileReadLine($oAuction)
        If @error = -1 Then ExitLoop
        
        $regexp = StringFormat("\[\""%s\""\] = ([0-9]+)",$herb)
        $price = StringRegExp($line, $regexp, 1)
        $regexperror = @error
        If $regexperror = 0 Then
            If StringIsInt($price[0]) Then
                $priceint = Int($price[0])
                _ExcelWriteCell($oExcelFile,$priceint,"A15")
            EndIf
        EndIf
    Wend
    
    FileClose($oAuction)
Next

$close = _ExcelBookClose($oExcelFile,1)
$closeerror = @error
If $close = 0 Then
    MsgBox(0,"Close Error",$closeerror)
EndIf

I've changed my account for privacy purposes, and the entire file works and runs as expected when I change the visibility of the open to 1. At the end though, the file closing throws an error of 1, indicating that the 'Specified Object does not exist'. The code is very rudimentary right now, and obviously doesn't put the data in the correct location in the spreadsheet (it just overwrites the previous entry), but I'm just trying to get the open/write/close process working.

Any help would be greatly appreciated.

Note: For anyone interested, this is a script to simply read data from one file and import it into another. Yes, it's related to World of Warcraft, but it does not constitute botting.

[cadeleon]

hi there, I need to get a result for a vlookup on an excel spreadsheet. Anything you can suggest?

[skin27]

I'm having trouble with the _ExcelBookClose() function. It seems as though the function call doesn't recognize my opened Excel file as a valid object type (_Workbook or _Application). Below is my code that I'm trying to do.

expandcollapse popup

#include <Excel.au3>
#include <File.au3>

Local $pathtowow = "C:\Users\Public\Games\World of Warcraft\"
Local $account = "ACCOUNT"
Local $auctionator = "Auctionator.lua"
Local $savedvariables = $pathtowow & "WTF\Account\" & $account & "\SavedVariables\"
Local $aucdatabase = $savedvariables & $auctionator
Local $pathtoMilling = "D:\Documents\My Dropbox\WoW\WoW - Milling Prices AU3.xls"

Local $herbs[7]
$herbs[0] = "Icethorn"
$herbs[1] = "Lichbloom"
$herbs[2] = "Adder's Tongue"
$herbs[3] = "Talandra's Rose"
$herbs[4] = "Goldclover"
$herbs[5] = "Deadnettle"
$herbs[6] = "Tiger Lily"

$oExcelFile = _ExcelBookOpen($pathtoMilling,0)
$oExcelerror = @error
If $oExcelFile = 0 Then
    MsgBox(0,"Excel Open Error",$oExcelerror)
    Exit
EndIf   

MsgBox(0,"Excel handle",$oExcelFile)
For $herb In $herbs
    $oAuction = FileOpen($aucdatabase)
    If $oAuction = -1 Then
        MsgBox(0,"Error","Unable to open Auctioneer database.")
        Exit
    EndIf
    
    While 1
        $line = FileReadLine($oAuction)
        If @error = -1 Then ExitLoop
        
        $regexp = StringFormat("\[\""%s\""\] = ([0-9]+)",$herb)
        $price = StringRegExp($line, $regexp, 1)
        $regexperror = @error
        If $regexperror = 0 Then
            If StringIsInt($price[0]) Then
                $priceint = Int($price[0])
                _ExcelWriteCell($oExcelFile,$priceint,"A15")
            EndIf
        EndIf
    Wend
    
    FileClose($oAuction)
Next

$close = _ExcelBookClose($oExcelFile,1)
$closeerror = @error
If $close = 0 Then
    MsgBox(0,"Close Error",$closeerror)
EndIf

I've changed my account for privacy purposes, and the entire file works and runs as expected when I change the visibility of the open to 1. At the end though, the file closing throws an error of 1, indicating that the 'Specified Object does not exist'. The code is very rudimentary right now, and obviously doesn't put the data in the correct location in the spreadsheet (it just overwrites the previous entry), but I'm just trying to get the open/write/close process working.

Any help would be greatly appreciated.

Note: For anyone interested, this is a script to simply read data from one file and import it into another. Yes, it's related to World of Warcraft, but it does not constitute botting.

You may try adding _ExcelBookClose($oExcelFile, 1, 0) directly after the last use of your excel. I also have a function that closes all invisible running excel instances, this may also help you.

#include <Array.au3>
#include <WinAPI.au3>

Func CloseInvisibleExcel()

    Local $excellist, $pid[1], $aWindows

    If ProcessExists("EXCEL.EXE") Then
        $excellist = ProcessList("EXCEL.EXE")
        $aWindows = _WinAPI_EnumWindowsTop()

        For $i = 1 To $aWindows[0][0]
            If $aWindows[$i][1] = "XLMAIN" Then
                _ArrayAdd($pid, WinGetProcess($aWindows[$i][0]))
            EndIf
        Next

        For $i = 1 To $excellist[0][0]
            If _ArraySearch($pid, $excellist[$i][1], 0, 0, 0, 1) = -1 Then
                ProcessClose($excellist[$i][1])
            EndIf
        Next

    EndIf

EndFunc   ;==>CloseUnactiveExcel

[PsaltyDS]

hi there, I need to get a result for a vlookup on an excel spreadsheet. Anything you can suggest?

Post to the General Help and Support forum. The UDF from this topic was adapted as one of the standard UDFs packaged with AutoIt (Excel.au3). See the _Excel* functions in the help file. I believe there was a recent topic in General Help and Support recently about running .vlookup also, so use the forum search.

[SpaceCadet]

I don't know if anyone is still working on/with this UDF, but I find it a work of art and use it constantly. Thank you.

I do seem to get unusual results from the _ExcelUsedRangeGet function. I've read that using the SpecialCells method is unreliable, and have had better results doing something like this (returning the array in the same format.) Anyone?

Func _ExcelSheetUsedRangeGet2($oExcel, $vSheet)
    Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If IsNumber($vSheet) Then
  If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
Else
  $aSheetList = _ExcelSheetList($oExcel)
  For $xx = 1 To $aSheetList[0]
   If $aSheetList[$xx] = $vSheet Then $fFound = 1
  Next
  If NOT $fFound Then Return SetError(3, 0, 0)
EndIf
$oExcel.ActiveWorkbook.Sheets($vSheet).Select
$TempR = $oExcel.Cells.Find('*', $oExcel.Cells(1, 1), Default, Default, $xlByRows, $xlPrevious).Row
$TempC = $oExcel.Cells.Find('*', $oExcel.Cells(1, 1), Default, Default, $xlByColumns, $xlPrevious).Column
$aSendBack[0] = $oExcel.Cells($TempR, $TempC).Address
    $aSendBack[1] = "R" & $TempR & "C" & $TempC
$aSendBack[0] = StringReplace($aSendBack[0], "$", "")
    $aSendBack[2] = $TempC
    $aSendBack[3] = $TempR
    If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0
    Return $aSendBack
EndFunc ;==>_ExcelSheetUsedRangeGet

Edited April 23, 2012 by SpaceCadet

[lai]

comment can't

set font and font.Name and font.FontStyle

only AutoSize

[AleksandrPrilutskiy]

In function _ExcelCellMerge error!

If $fDoMerge <> False Or $fDoMerge <> True Then Return SetError(4, 0, 0)

- Always False.

Fix if on

If ($fDoMerge <> False) And ($fDoMerge <> True) Then Return SetError(4, 0, 0)

[Rickname]

Sorry for bumping this 2 yrs old thread, but I want to know if this UDF is still valid or there are others available for Excel 2013...

[water]

The latest Excel UDF is the one that comes with AutoIt 3.3.12.0 or the latest Beta.

[gregwallace]

I am new to AutoIt but not to scripting jobs.  (Been writing WIL files for over 10 years.)  Putting into production my first AutoIt job.  Below is a step that really should be part of the UDF but it is not.  It will refresh all the table queries in Excel file linked to my databases.

send("!ara")  ;refresh all data

Sleep(30000)  ; Sleep for 30 seconds before saving the file

 

I also found that _Excel_bookSave does not work but _Excel_Close will save the file.

[water]

I suggest to open a new thread in the GH&S forum. The ExcelCOM UDF is not part of AutoIt.

And what do you mean by: _Excel_BookSave does mot work?

[Jfish]

Try this

$excel=_Excel_Open()
$workbook=_Excel_BookNew($excel)
$workbook.RefreshAll