Jump to content
water

Update the Excel UDF that comes with AutoIt

Recommended Posts

I am about to add functions to import text files into Excel.

Does anyone need a function to import fixed width files?

@water.. I know the Import CSV wizard is a part of Excel. But it COULD be argued that if the UDF offers possibility to insert data (by cell or by row), it's actually redundant to have this wizard. It may be just as good to work with CSV data as arrays in AutoIT and insert directly.

But perhaps for non-programmers, it would be good to have. And fixed-width data actually is still being used. At least my customers (in the printing industry) often use fixed-width typically for mailing actions. Yes, old habits never die.

Will there be an export function as well (_Excel_SaveAs_CSV($options...) ) ?

When you develop/test this, make sure to test in different system locale combinations, as I know Excel sometimes play games with you if you f.ex don't have the same Excel locale as your Windows system. You may f.ex experience that suddenly "," is preferred over ";" as separator etc.

Also try to test for unicode compatability and requirements on source encoding ? (yea I know you're busy. Just in case you get 3 rain days ;) )

Will be happy to assist any way I can...


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

Share this post


Link to post
Share on other sites

Hi Myicq,

the function to insert data lets you insert a string, an 1D or 2D array. The goal is to let the user insert data he created in his script etc.

The main purpose of the CSV import function is to read, parse and insert a CSV file. The difficult part is to parse the CSV file (different text qualifiers, number separators, column delimiters etc.). Inserting the data into Excel is the easy part.

So I think the functions aren't very redundant.

To create a function to import fixed width text files is more complex. I will have a look but can't promise anything.

To export an Excel sheet as CSV you use function _Excel_BookSaveAs and specify $xlCSVWindows as fileformat. A PDF export function will be available too.

Unfortunately I have only two PC's available to test. Windows XP with Office XP and Windows 7 with office 2010 - both german locale. So the testing with other locales needs to be done by the users of this forum.

The same is true for unicode tests. I can only test with German Umlauts.

I hope to soon release the Alpha 3 where you then can play with the _Excel_BookOpenText (CSV import) function (and all other functions as well) :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

To create a function to import fixed width text files is more complex. I will have a look but can't promise anything.

Water,

did you come by these examples ?

Some C code to import CSV files programmatically

Some VBA code do import fixed-width CSV

Some more examples of the same

Seems like they set some 20 parameters + the array of widths. What is the best way (in terms of coding style), or preferred way, to transfer that many parameters to an AutoIT function ?

Eagerly await your next alpha :)


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

Share this post


Link to post
Share on other sites

I used Excel's macro recorder to see what goes on under the covers when importing a fixed width file. I got the same "QueryTables.Add" result.

I'm sure some of the parameters are not needed or can be derived from a single parameter.

But I will first add the CSV import feature plus export to PDF before I release the next Alpha (hopefully this weekend).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Alpha 3 of the UDF has been released.

Please tell me what you think!

For download please see post #1.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Another question:

The current Excel UDF allows up to 4 parameters when a range needs to be passed as parameter (_ExcelFontSetproperties, _ExcelHorizontalAlignSet, _ExcelNumberFormat etc.). This makes a function call lengthy and quite unreadable.

I tend to reduce this to a single parameter: This could be either a range object, an A1 range ("A1:B17") or a string with two or four values (e.g. "1,3,3,5" = range from row one, column 3 to row 3 column 5). This would be converted to a range object so all functions work with ranges internally.

What do you think?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

So, been testing out your V3.0 Its definetely faster at the tasks it currently has. That being said, its missing some pretty huge/basic functions I think. Like the ability to delete rows/columns. It also doesnt play nice with the current excel.au3 so I cant run both with #include and mix and match functions "as needed". From what i can tell its a Const issue, I wonder since so many are similar, if you could comment out the overlapping ones and #include the current excel to make the systems play nicely together? Also, just to make sure this is what it takes to open a file.

Global $sPriceFile = @DesktopDir & "nova systemtrade dataTCN3.csv"

Global $in = _Excel_Open()

$in = _Excel_BookOpen($oExcel, $sPriceFile)

The #in = _excel_open() thing was kind of confusing as I am not sure what that is, how it works or why its needed :P

Thanks for your assistance.

Share this post


Link to post
Share on other sites

That being said, its missing some pretty huge/basic functions I think. Like the ability to delete rows/columns.

Correct. The version you downloaded is a very early alpha. The functional range hasn't been defined and will be enhanced upon sensible user request. Functions to insert/delete/move rows/columns will definitely be in the first beta version.

It also doesnt play nice with the current excel.au3 so I cant run both with #include and mix and match functions "as needed".

Correct. I had run a poll and people didn't have much problems with a complete rewrite of the UDF. The new UDF has a lot of design changes that lead to script breaking code.

This means: You can't mix the current Excel UDF and the new one!

Also, just to make sure this is what it takes to open a file.

The $in = _excel_open() thing was kind of confusing as I am not sure what that is, how it works or why its needed :P

One of the design changes was that every function does exactly what the name tells you it does. The current UDF does a lot of things under the covers.

To open an Excel workbook you now need to call this functions:

_Excel_Open - Connect to a running instance of Excel or create a new application object. Do all application related stuff (set visibility, screen updating, re-calculation setting ...)

_Excel_BookOpen - Open an existing Excel workbook and do some book related stuff

_Excel_BookClose - Close the book

_Excel_Close - Close the application

All clear? ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Correct. The version you downloaded is a very early alpha. The functional range hasn't been defined and will be enhanced upon sensible user request. Functions to insert/delete/move rows/columns will definitely be in the first beta version.

Correct. I had run a poll and people didn't have much problems with a complete rewrite of the UDF. The new UDF has a lot of design changes that lead to script breaking code.

This means: You can't mix the current Excel UDF and the new one!

One of the design changes was that every function does exactly what the name tells you it does. The current UDF does a lot of things under the covers.

To open an Excel workbook you now need to call this functions:

_Excel_Open - Connect to a running instance of Excel or create a new application object. Do all application related stuff (set visibility, screen updating, re-calculation setting ...)

_Excel_BookOpen - Open an existing Excel workbook and do some book related stuff

_Excel_BookClose - Close the book

_Excel_Close - Close the application

All clear? ;)

Gotcha, for some reason I thought I had a earlier copy that had a lot of the old functionality mixed with way higher read times on readtoarray and the like. Maybe you did and decided to completely scrap the old UDF post poll?

Yeah, I tried a few ways to #include both. Unfortunately all the scripts I would love to update use functions currently unavailable. Unfortunate for me because your new functions are WAY faster on read/write to array.

Gotcha on the excel calls. Makes a lot of sense, I am a novice coder(1 yr~ on AU3, nothing else rly). Thanks for the information.

Edited by THECARDNEXUS

Share this post


Link to post
Share on other sites

The Excel poll thread can be found

As soon as I have released the Word UDF to the AutoIt source I hope to find some spare time to work on the Excel UDF again.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Probably four years ago I needed to pass through 14,000 spreadsheets and make the same format modification to each file.

I threw together a script using BookOpen and BookClose and fired off a test run. Ten minutes later I killed it when it had barely processed 1% of the files. I scrapped the Excel UDF and went with custom code that processed all 14K files in just over an hour. The UDF was truly unusable in a batch environment when opening or closing a workbook also meant starting and stopping the Excel application. I pretty much ignored the Excel UDF after that. I griped about it on the forum back then, a few times since, and often have recommended that others steer clear of it.

So, this is a long-overdue improvement, that will allow someone to loop through and process mass quantities of workbooks while only loading excel.exe once.

Looking good, Water

Share this post


Link to post
Share on other sites

small question - is there something different with 2007 vs 2010 versions for this UDF - the reason I mention it is - I used to be able to put the same file into an array and it would take on average 3.5 mins, now it is taking over 15 mins.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Nitekram,

I only have Excel 2010 available but would like to test performanace of such a large file. Can you provide more information (number of columns/rows etc.) or even the file itself?

Processing shouldn't take minutes.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Using _ExcelReadSheetToArray()

File size is about 1000 - 1500 KB

About 700+ rows

55 columns

Sorry, unable to provide file.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

I will do some tests with a workbook of this size and post the results.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Reading/writing a worksheet with 750 rows, 55 columns into/from an array takes about 1/4 second.

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

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

; Create application object
Global $oExcel = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_ErrorNotify(2)

Example1($oExcel)
Example2($oExcel)

_Excel_Close($oExcel, False)
Exit

; *****************************************************************************
; Example 1
; Write an array with 750 rows and 55 columns to a worksheet
; *****************************************************************************
Func Example1($oExcel)
    Local $aTab[751][55] = [[750, 55]]
    For $iIndex1 = 1 To UBound($aTab, 1) - 1
        For $iIndex2 = 0 To UBound($aTab, 2) - 1
            $aTab[$iIndex1][$iIndex2] = Random(1000000000,2000000000)
        Next
    Next
    _ArrayDisplay($aTab)
    Local $oWorkbook = _Excel_Booknew($oExcel)
    Local $iTime = TimerInit()
    _Excel_RangeWrite($oExcel, $oWorkbook, Default, $aTab, 1, 1, 1)
    MsgBox(0, "", "Writing an array with 750 rows and 55 columns to the worksheet took " & TimerDiff($iTime) & " milliseconds.")
    _Excel_BookSaveAs($oExcel, Default, @ScriptDir & "BigArray", $xlExcel8, True)
    _Excel_BookClose($oExcel, $oWorkbook)
EndFunc   ;==>Example1

; *****************************************************************************
; Example 2
; Read the active worksheet into an array
; *****************************************************************************
Func Example2($oExcel)
    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "BigArray.xls")
    Local $iTime = TimerInit()
    Local $aTab = _Excel_RangeRead($oExcel, $oWorkbook, Default, "A1:BC750")
    MsgBox(0, "", "Reading the workbook with 750 rows and 55 columns to an array took " & TimerDiff($iTime) & " milliseconds.")
    _ArrayDisplay($aTab)
EndFunc   ;==>Example2

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Processing 32760 rows with 225 columns takes about 32 seconds to write to the worksheet. To read this worksheet and create an array takes 90 milliseconds.

BTW: The script has to be run with AutoIt beta 3.3.9.2 or later.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

So this function _Excel_RangeRead is only in the BETA? Does this replace _ExcelReadSheetToArray()?

As I am unable to install anything on this work computer, is there a install to USB for BETA?


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

So this function _Excel_RangeRead is only in the BETA? Does this replace _ExcelReadSheetToArray()?

Correct. Correct.

As I am unable to install anything on this work computer, is there a install to USB for BETA?

Not that I know of. But there is a about to create a portable version of the production version. Maybe its possible to make the beta portable too?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

If you download the zipped beta version, you can expand it to a flash drive and start it the same way with the portable wrapper program. I have the beta and the production version on a thumb drive in different folders that I can run without ever installing anything.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

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

  • Similar Content

    • By Stormgrade
      Hello,
      I'm searching the UDF GDIpProgress.au3 from ProgAndy
      None of the links in topic
       works
      Can't someone upload it please ?
    • By Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
    • By Most
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all.
      Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. 
      What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error.

      In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). 
      I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. 

      I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. 

      Would be appreciate for any help. 
      So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. 

      I use Windows 10, Excel for Office 365. 
      Thank you in advance. 
    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
×
×
  • Create New...