Jump to content

Excel UDF - Help & Support


 Share

Recommended Posts

  • Replies 54
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Can you please try the latest AutoIt beta version? It comes with the most current version of the new Excel UDF included.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • Moderators

I have been promising myself to look into the Excel rewrite for some time but haven't had the opportunity, so I know I am coming late to the party.

I am now working on a script that requires the latest beta, so figured it was the perfect time. I am noticing one function in particular that I use a lot did not make it into the rewrite (_ExcelReadSheetToArray). Can you please explain if there is a method for doing this in the new UDF (ExcelRangeRead?)?

Edited by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

According to the help file

$aAllCells = _Excel_RangeRead($oWorkbook) ; Read all used cells of the active worksheet
$aAllCells = _Excel_RangeRead($oWorkbook, 2) ; Read all used cells of worksheet 2
$aAllCells = _Excel_RangeRead($oWorkbook, "Test") ; Read all used cells of worksheet named "Test"

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • Moderators

Thanks, must have skipped over that as I do not see that simple a notation in the help file.

The closest I came was something like this: _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange.Columns("A:F"), 1). I will try your example.

Edited by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

Hi guys, I have been using this UDF and found a problem, I am using BEx Analyzer wich is an addin for Excel, this addin connects the aplication to a SAP server with a little login dialog, In my script I need to automatize this, so I do this:

-Open the Excel with the addin with its own launcher             [OK]

-Open the XLS file I need            [OK]

-Call from Autoit the Addin macro that refresh the file with the last data   [OK]

*At this step the login dialog appears, now the problem is that my script stops here until I close this dialog, but I need to continue the script so it can enter the credentials automatically.

 

I know I can use another process to put the login info, but I want to keep it as simple as possible (one .exe only).

I even did some Event handler in the worksheet so it trigger the macro when a cell value was 1, so I only had to write this value from Autoit, but the result is the same, the execution stops until I close the login window.

How can I return to the code execution as soon as I trigger the macro?

Thanks!

Edited by KarlosTHG
Link to comment
Share on other sites

As AutoIt does no multitasking and a Window waiting for user input is a blocking function there is nothing you can do but start a second script to handle the window before you run the macro.

Please have a a look at the OutlookEX UDF where this approach is used to click away Outlook security warnings.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Hey Water!

Love the Excel UDF's.  Took me a second to get used to it but they work great.

Where can I learn more about all the extensions (not sure if I said that the right way) like $oExcel.Columns.AutoFit ?

I just saw another user's script with this example and was wondering what all you can do with the code.

Wanted to get a better understanding.

Thanks!

Link to comment
Share on other sites

On MSDN you'll find the Excel Object Model Reference.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 weeks later...

Due to power issues on my desktop I built myself a new one. When I downloaded AutoIt and ran my latest project I freaked out when I got slammed with all kinds of errors. That will teach me not to pay attention to what version I'm downloading.

Anyway,

I have a function that writes lots of stuff based on the last empty row. You may remember from my last topic started Water. That function was

_ExcelWriteCell($oExcel, $Data, $newRow, #)

I have, the excelwrite command(the excel book, the data, the next empty row, number of the column to write data to).

Now, do I need to convert the row to a number using _Excel_ColumnToNumber to be able to get this working again?

$newRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 ;This is working great still when finding the next empty row.

$ACol = _Excel_ColumnToNumber('A')
_Excel_RangeWrite($oBook, 'Sheet1', $Data, $ACol)

This is what I have been beating around with, obviously it is wrong. Do I need to start listing the columns into arrays now or do I just suck and the answer is much easier? If I specify the cell, ie A2875 instead of $ACol, then the data gets written.

Link to comment
Share on other sites

Use

$iNewRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 ;This is working great still when finding the next empty row.
_Excel_RangeWrite($oBook, 'Sheet1', $Data, "A" & $iNewRow)

because the UDF only supports "A1" notation.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I spent so much time in rewriting the UDF - it's easy to be smart then ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...