Jump to content
Lights_On

Excel Loop Speed - (Moved)

Recommended Posts

Hi,

I am looping through a list of excel files, and opening each one to read a specific cell.  The aim is to then keep a list of only the excel files that have the field in them I require.

Below is an example of the code used.

 

Local   $FieldWanted            =   $arr[1]
        Local   $oExcel                 =   _Excel_Open(False)
        Local   $NumberOfRows           =   UBound($AList)-1
        
        While $NumberOfRows <> 0

            Local $File         =   _Excel_BookOpenEX($oExcel, @ScriptDir & "\The_File.xlsx", False, True)
            Local $Field        =   _Excel_RangeRead($File, Default, "C23", 3)
            _Excel_BookClose($File, False)
            If $Field <> $FieldWanted Then
                _ArrayDelete($AList, $NumberOfRows)
            Else
                $AList[$NumberOfRows]=StringRegExpReplace($AList[$NumberOfRows], "\..*", "")
            EndIf

            Local $OldNumberOfRows = $NumberOfRows - 1
            $NumberOfRows = $OldNumberOfRows
        WEnd
        
        _Excel_Close($oExcel, False, True)

 

What I have in my code works – but as more files are added it slows to a crawl as it has to open each file, read it, make a decision, then close it again.

Is there a way to make this process faster? A way I am not aware of? 

Thank you in advance.

Share this post


Link to post
Share on other sites

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.


Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team


Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

Hi Melba23,

Thank you for moving the post accordingly and apologies for using the incorrect location - have read an understood your post.

 

Share this post


Link to post
Share on other sites

Lights_On,

No probs - more people are likely to see it here anyway.

M23


Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
Posted (edited)

I'm not sure if this helps you, but you can directly check the cell values in unopened .xlsx files from a separate workbook.

Open a new book and try this as a formula to get F4 in sheet 1 of the un-opened file. Note, as this does not open the target workbook there will be no-recalc, so time dependent functions will not have updated since it was last saved. Additionally, the square brackets ARE required.

='C:\Full\path\to\file\[target.xlsx]Sheet1'!$F$4

Perhaps you could use AutoIt to build this master sheet from a directory search if your list of files is not static?

I have not bench-marked the two methods, but at the very least this method spares you an auto-recalc on each workbook load.

Edited by SlackerAl

Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites

Hi SlackerAl,

 

Thank you for taking the time to help.

 

So if i understand you correctly I can open a workbook instance, then use something like:

 

Local $Field = 'C:\Full\path\to\file\[target.xlsx]Sheet1'!$F$4

 

to get the cell value of a file.  If so then I wont need to open and close each file - which is likely what is taking the time.  I would juts need to modify the call / path dynamical from the array in the loop.

 

I will be back at my desk in 15 so will try.

 

Thank you.

Share this post


Link to post
Share on other sites

Perhaps something like this would work faster.

Global $FieldWanted = $arr[1]
Global $oExcel = _Excel_Open(False)
Global $NumberOfRows = UBound($AList) - 1
Global $File, $Field
For $loop = $NumberOfRows to 0 Step - 1
    $File = _Excel_BookOpenEX($oExcel, @ScriptDir & "\The_File.xlsx", False, True)
    $Field = _Excel_RangeRead($File, Default, "C23", 3)
    _Excel_BookClose($File, False)
    If $Field <> $FieldWanted Then
        _ArrayDelete($AList, $loop)
    Else
        $AList[$loop] = StringRegExpReplace($AList[$loop], "\..*", "")
    EndIf
Next

_Excel_Close($oExcel, False, True)

First, don't keep redeclaring your variables inside the loop, it's not necessary and slows things down.

Second, when deleting rows from an array, you should always go from the highest to the lowest rows or you'll end up with an array element that doesn't exist eventually.

Third, unless this is inside a function, don't use Local for your variables, as they're not Local unless they're in a function. It won't affect speed any, it's just not good coding.

Fourth, this is totally untested ;)

 


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

Hi BrewManNH,

 

Quote

First, don't keep redeclaring your variables inside the loop, it's not necessary and slows things down.

Did not know that - thank you - will implement.

Quote

Second, when deleting rows from an array, you should always go from the highest to the lowest rows or you'll end up with an array element that doesn't exist eventually.

Yes agreed - it did not break yet and I had not noticed i did it that way, would not normal - will change that. thank you.

Quote

Third, unless this is inside a function, don't use Local for your variables, as they're not Local unless they're in a function. It won't affect speed any, it's just not good coding.

All in a function so should be all okay.  

 

I will try this also - I think the main lag is the need to open and close each file.  I should have coded the file name to hold the field I need then the could have just read all names to the array and sorted them there - would have been far faster.  If I cant get a suitable work around I will likely re code and make broader changes.

Share this post


Link to post
Share on other sites

SlackerAl

I miss understood you earlier - I see now what you mean and have tested this - but sadly the sheets are also password protected etc and this method - while an interesting view - it not best suited to my need.  Thank you for the idea however.

 

BrewManNH

Thank you for the suggestions.  I played with my code making the suggestions you stated but sadly no real 'considerable' speed increase was seen.

 

It seems to me that the 'open' and 'close' of each respective document is what is taking the time.  As such I think I need to change the naming convention of the file(s) so I can asses in an array of data without the need to open each document.

 

Share this post


Link to post
Share on other sites

I have a question, is this the actual code you're using, or is there more to this section? What I mean is, you're opening and closing the same file over and over again and checking the same spot in the same file every time for the number of loops it's doing, why?

Post your actual code so we can see what is happening in your real script.


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

Hi BrewManNH,

The code was just an example and yes it is part of a far larger script.

The only main difference is list of files, they are all different, hence the need to open each one separately (not the same one over and over). some are relevant, some are not - the code assesses what I need.

The list of files is ever expanding - hence why initially it was not really that slow but as the number of files gradually increases so does the time it takes to asses things. Understandable of course.

An updated example to better show this could be:

 

Local   $FieldWanted            =   $arr[1]
        Local   $oExcel                 =   _Excel_Open(False)
        Local   $NumberOfRows           =   UBound($AList)-1
        
        Local $NumberOfRows = _FileListToArray(@scriptdir) ; in this directory a new file is added daily thus it is ever increasing in size
        While $NumberOfRows <> 0

            Local $File         =   _Excel_BookOpenEX($oExcel, @ScriptDir & "\The_File.xlsx", False, True)
            Local $Field        =   _Excel_RangeRead($File, Default, "C23", 3)
            _Excel_BookClose($File, False)
            If $Field <> $FieldWanted Then
                _ArrayDelete($AList, $NumberOfRows)
            Else
                $AList[$NumberOfRows]=StringRegExpReplace($AList[$NumberOfRows], "\..*", "")
            EndIf

            Local $OldNumberOfRows = $NumberOfRows - 1
            $NumberOfRows = $OldNumberOfRows
        WEnd
        
        _Excel_Close($oExcel, False, True)

 

The above has not adopted the suggestions you made in the prior three points but does hopefully address your question.

 

 

Share this post


Link to post
Share on other sites
3 minutes ago, Lights_On said:

Local $NumberOfRows = _FileListToArray(@scriptdir) ; in this directory a new file is added daily thus it is ever increasing in size

This returns an array of file names, the rest of your code won't run as is. Please post the real code you're using because this clearly isn't it. 


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
Posted (edited)

Hi BrewManNH,

As requested:

Local   $List                   =   _FileListToArrayRec(@ScriptDir & "\Path_To_Folder\", "*.xlsx|~$*.xlsx|", 1, 0, 0, 0) 
        Local   $Person                 =   "Me"
        Local   $oExcel                 =   _Excel_Open(False)
        Local   $NumberOfRows           =   UBound($List)-1
        Local   $Incriment              =   100 / $NumberOfRows
        While $NumberOfRows <> 0
            Local $File                 =   _Excel_BookOpenEX($oExcel, @ScriptDir & "\Path_To_Folder\" & $List[$NumberOfRows], False, True)
            Local $Field                =   _Excel_RangeRead($File, Default, "C23", 3)
            _Excel_BookClose($File, False)
            If $Field <> $Person Then
                _ArrayDelete($List, $NumberOfRows)
            Else
                $List[$NumberOfRows]=StringRegExpReplace($List[$NumberOfRows], "\..*", "") 
            EndIf

            Local $OldNumberOfRows = $NumberOfRows - 1
            $NumberOfRows = $OldNumberOfRows
        WEnd
        _Excel_Close($oExcel, False, True)

 

Edited by Lights_On

Share this post


Link to post
Share on other sites
Posted (edited)

Another thought.... whilst I'm not helping 🙂 

Do all your files change regularly? If not, you might want to log the data and the file timestamp each time you run your script, then only check the files with new time stamps.... Edit: Or even just log the last time you ran your script and check for timestamps after that.

Edited by SlackerAl

Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites

Hi SlackerAI,

You are indeed helping - and I appreciate it! :-)

This too crossed my mind but sadly yes - file content changes daily so a full re asses is required each time.

Share this post


Link to post
Share on other sites

Using _ArrayDelete with a single number to delete also means that it's using ReDim repeatedly which will slow things down. Try building a delete string instead and only call _arrayDelete once. Or don't call it at all seeing as how the array probably isn't used more than one tim through the loop.


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

Interesting.  I had not considered this. So I could either list all elements that need deleting and delete them at the end in one go or perhaps if a match is found simply copy that match to a new array; both options I assume would be a faster options.  Thank you - I will try this.

Share this post


Link to post
Share on other sites
Quote

The list of files is ever expanding

This makes me think that the long term strategy is not to open and parse each file - what will be the state of play in 5 years time? How about adding some vba to the files so that they automatically post the filename, date stamp and data to a central repository whenever they are saved?


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites

Hi SlackerAI,

I agree - long term even changing the file name and then reading these to an array so it is faster to asses is somewhat flawed.

 

I like the idea of VBA with a central repository but I have not yet tried any au3 with external data stores.  I did do some searching on this once before but i did not seem to get anywhere too fast and at the time the need was less.  if you have any references to point me in the right direction I am happy to go do some more reading.

 

 

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

×
×
  • Create New...