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-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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

    • ScriptJunky
      By ScriptJunky
      I noticed a lack of a constants file for _WinAPI_GetSystemMetrics() so I made this for anyone who wants to add it to their library. Enjoy!  (file attached below)
      #include-once ; #INDEX# ======================================================================================================================= ; Title .........: WinAPI GetSystemMetrics Constants ; AutoIt Version : 3.3.14.5 ; Language ......: English ; Description ...: Constants for _WinAPI_GetSystemMetrics(). ; Author(s) .....: ScriptJunky ; =============================================================================================================================== ; #CONSTANTS# =================================================================================================================== ; _WinAPI_GetSystemMetrics() Global Const $ARRANGE = 56 Global Const $CLEANBOOT = 67 Global Const $CMONITORS = 80 Global Const $CMOUSEBUTTONS = 43 Global Const $CONVERTIBLESLATEMODE = 0x2003 Global Const $CXBORDER = 5 Global Const $CXCURSOR = 13 Global Const $CXDLGFRAME = 7 Global Const $CXDOUBLECLK = 36 Global Const $CXDRAG = 68 Global Const $CXEDGE = 45 Global Const $CXFIXEDFRAME = 7 Global Const $CXFOCUSBORDER = 83 Global Const $CXFRAME = 32 Global Const $CXFULLSCREEN = 16 Global Const $CXHSCROLL = 21 Global Const $CXHTHUMB = 10 Global Const $CXICON = 11 Global Const $CXICONSPACING = 38 Global Const $CXMAXIMIZED = 61 Global Const $CXMAXTRACK = 59 Global Const $CXMENUCHECK = 71 Global Const $CXMENUSIZE = 54 Global Const $CXMIN = 28 Global Const $CXMINIMIZED = 57 Global Const $CXMINSPACING = 47 Global Const $CXMINTRACK = 34 Global Const $CXPADDEDBORDER = 92 Global Const $CXSCREEN = 0 Global Const $CXSIZE = 30 Global Const $CXSIZEFRAME = 32 Global Const $CXSMICON = 49 Global Const $CXSMSIZE = 52 Global Const $CXVIRTUALSCREEN = 78 Global Const $CXVSCROLL = 2 Global Const $CYBORDER = 6 Global Const $CYCAPTION = 4 Global Const $CYCURSOR = 14 Global Const $CYDLGFRAME = 8 Global Const $CYDOUBLECLK = 37 Global Const $CYDRAG = 69 Global Const $CYEDGE = 46 Global Const $CYFIXEDFRAME = 8 Global Const $CYFOCUSBORDER = 84 Global Const $CYFRAME = 33 Global Const $CYFULLSCREEN = 17 Global Const $CYHSCROLL = 3 Global Const $CYICON = 12 Global Const $CYICONSPACING = 39 Global Const $CYKANJIWINDOW = 18 Global Const $CYMAXIMIZED = 62 Global Const $CYMAXTRACK = 60 Global Const $CYMENU = 15 Global Const $CYMENUCHECK = 72 Global Const $CYMENUSIZE = 55 Global Const $CYMIN = 29 Global Const $CYMINIMIZED = 58 Global Const $CYMINSPACING = 48 Global Const $CYMINTRACK = 35 Global Const $CYSCREEN = 1 Global Const $CYSIZE = 31 Global Const $CYSIZEFRAME = 33 Global Const $CYSMCAPTION = 51 Global Const $CYSMICON = 50 Global Const $CYSMSIZE = 53 Global Const $CYVIRTUALSCREEN = 79 Global Const $CYVSCROLL = 20 Global Const $CYVTHUMB = 9 Global Const $DBCSENABLED = 42 Global Const $DEBUG = 22 Global Const $DIGITIZER = 94 Global Const $IMMENABLED = 82 Global Const $MAXIMUMTOUCHES = 95 Global Const $MEDIACENTER = 87 Global Const $MENUDROPALIGNMENT = 40 Global Const $MIDEASTENABLED = 74 Global Const $MOUSEPRESENT = 19 Global Const $MOUSEHORIZONTALWHEELPRESENT = 91 Global Const $MOUSEWHEELPRESENT = 75 Global Const $NETWORK = 63 Global Const $PENWINDOWS = 41 Global Const $REMOTECONTROL = 0x2001 Global Const $REMOTESESSION = 0x1000 Global Const $SAMEDISPLAYFORMAT = 81 Global Const $SECURE = 44 Global Const $SERVERR = 289 Global Const $SHOWSOUNDS = 70 Global Const $SHUTTINGDOWN = 0x2000 Global Const $SLOWMACHINE = 73 Global Const $STARTER = 88 Global Const $SWAPBUTTON = 23 Global Const $TABLETPC = 86 Global Const $XVIRTUALSCREEN = 76 Global Const $YVIRTUALSCREEN = 77  
      WinAPISystemMetricsConstants.au3
    • rcmaehl
      By rcmaehl
      A UDF with Extended Functions for Window Management
       
      Notes:
      Fixes WinGetClassList's barbaric returning of a @LF separated string instead of an array.
       
      Potential Uses:
      Automating applications that change their controls' handles/classes on each launch (e.g. half of Cisco's programs)
       
      Functions:
      _WinGetClassList
      _WinGetClassNNList
      _WindowGetHandleList
      _WindowGetHandleListFromPos
       
      Download: 
      WindowEx.zip  (v0.4)
       
      Changelog:
      10/04/2016 (v0.4): _WinGetClassNNList Fixed : Not Returning an Index when using $2D_ARRAY _WinGetClassNNList Fixed : Not Properly returning $aArray[x][1] on Classes with instances > 9 when using $2D_ARRAY 10/03/2016 (v0.3): _WinGetClassList Added : Exactly the same as WinGetClassList but returns a more civilized Array _WinGetClassNNList Added : Returns Classes and their instances in either a 1D or 2D array depending on Flags _WindowGetHandleList Renamed: _WinGetHandleList SCRIPT BREAKING! _WindowGetHandleListFromPos Renamed: _WinGetHandleListFromPos SCRIPT BREAKING! 10/01/2016 (v0.2): WindowsExConstants.au3 Added : Flags in _WindowGetHandleListFromPos _WindowGetHandleListFromPos Removed: ConsoleWrite left in during debug _WindowGetHandleListFromPos Added : Flag for if part of a Control is at $X, $Y return it as well. 10/01/2016 (v0.1): _WindowGetHandleList Added : Retrieves the handles of classes from a window. _WindowGetHandleListFromPos Added : Retrieves the handles of classes at a specific position from a window. Known and Reported Bugs:
      None reported To Do:
      To Be Decided. Opinions welcome! Upcoming Changes:
      To Be Decided.
    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
       
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning everyone

      I was playing a little bit with "Screen Capture" UDF, and I was trying to make a "Window" capture, but, since I made a GUI which through I fire the event "Capture", my GUI is captured as well, and I don't want to
      This is the line of code that makes the capture:
       
      _ScreenCapture_CaptureWnd($strScreenCaptureFileName, $objActiveWindow, 0, 0, -1, -1, False) And these are the lines of code which select the "active" window:
       
      Local $objCurrentWindow = 9999 If _IsPressed("01") Then $objCurrentWindow = WinGetHandle("[ACTIVE]") If $objCurrentWindow <> $objMyGUI Then $objActiveWindow = $objCurrentWindow EndIf EndIf Sorry If I made stupid mistakes
      Thanks in advance.

      Francesco
    • MrCheese
      By MrCheese
      Hi guys,
      without including everything (unless you want it)
      I am copying data from a table in chrome and wanting to paste it into excel.
      Copying in Chrome works.
      I can paste it into the field i want by emulating goto -> ctrl V:
      WinActivate($dataload) WinWaitActive($dataload) Sleep(500) $oWorkbook1.Sheets("ItemReturn").Activate Sleep(500) $msg = "Measuring Sheet" conwrite() ttips2() Local Const $xlUp = -4162 With $oWorkbook1.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, "B")).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number EndWith $NewStartCell = $iLastCell + 2 $msg = "moving to location" conwrite() ttips2() Sleep(250) Send("^g") WinWait("Go To") Sleep(100) Send("B" & $NewStartCell) Sleep(100) Send("{ENTER}") Sleep(500) Send("^v")  
      But, I want to use _excel_rangecopypaste, pasting from the clipboard
      _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, default, "B" & $NewStartCell,default,$xlPasteValuesAndNumberFormats) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error pasting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) however, this gives me error 4 , extended@:  -2147352567
      How can i fix this or find out how to debug this error?
       
      Thanks
×