Jump to content
Sign in to follow this  
Myicq

[solved] Object type returned by Excel UDF + Thiswookbook trap

Recommended Posts

Myicq

I am developing some excel export code.

A small part is outputting some constants not in the excel UDF.

I can find the constant using VBA editor from Excel. But my question is basically (since it did not work in AutoIT):

In terms of VBA code, which object type is $oExcel in this code:

Local $oExcel = _ExcelBookOpen(@ScriptDir & "\" & "tstfile.xlsx", 1)

I tried to do this:

; VBA CODE which works
Sub s()
MsgBox Application.ThisWorkbook.FullName
End Sub

personally I think $oExcel is an application object, so this should work:

ConsoleWrite("From Excel file : " & $oExcel.ThisWorkbook.FullName & @CRLF)

.. but it does not.

What am I missing here ? Normally I know my way in and out of VBA etc.

Edited by Myicq

I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites
Myicq

Update:

This is a real trap.

ThisWorkbook is for the workbook where the macro code is running. Since AutoIT is technically not running inside a workbook, it fails.

ActiveWorkbook the the visible workbook.

So, changing code to

"From Excel file : " & $oExcel.ActiveWorkbook.FullName & @CRLF

Will leave thread for others to learn.

And found that $oExcel IS an Application object, in VBA sense.


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites
water

Correct. The object returned by _ExcelBookOpen is an application object.

Try

$oExcel.ActiveWorkbook.FullName
to get the full name of the workbook.

BTW: I'm just about to completely rewrite the that comes with AutoIt. The new UDF will get rid of all limitations the current version has.

In your case you will get the object of the opened workbook not the application.

Please have a look and tell me what you think.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • caramen
      By caramen
      I watched _OL_ItemSend&_OL_ItemFind&_OL_ItemCreate in OutlookEx UDF but hmmm
       
      Since it use the test environnement i cant get the orders of the mail creation
       
      Can someone make me win some time of reading all exemples script and lead me how to do one ?
      That whould be lovely.
      Gnight
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×