Jump to content
Ahmed101

_Excel_BookAttach is too slow!

Recommended Posts

Ahmed101
Posted (edited)

I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
Is there any solution for that ?

Edited by Ahmed101

Share this post


Link to post
Share on other sites
water

Depends on the code you run ;)

  • Like 1

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
Ahmed101

I am attaching to 12 workbooks and it takes a lot of time more than 2 minutes i think.
and the code is so simple, the attach is going by the Path Name :

$attach1 = _Excel_BookAttach(@Scriptdir&"\File1.xlsx")

    if $attach1 = 0 Then
        MsgBox(64,"Error","Not attached")
        Exit
    EndIf

http://i.epvpimg.com/lwt6dab.png
Attaching to the last workbook opened will take a lot of time beside attaching to the first workbook.

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Ahmed101, you could use a For...Next loop to attach workbook in an array of Workbooks :) I think it could be a little faster, and surely more compact :)

 

Best Regards.

  • Like 1

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

ALWAYS GOOD TO READ:

In case I am violating some pseudo Forum rule, or I am doing something wrong, I want let you know that I'm not doing this on purpose, and for this, I kindly ask you to send me a private message about the "bad content" I posted, in order to edit/remove it, and to not be warned wrongly.
Thanks in advance.

 

Share this post


Link to post
Share on other sites
Ahmed101

@FrancescoDiMuro Thanks for your reply bro,
Could you give me an example please ? because i think it's the same as the normal way.

Share this post


Link to post
Share on other sites
FrancescoDiMuro
; Declare your Global array of Worbooks
Global $arrWorkbooks[10]

; Here, it dipends on how your workbooks are named...
; You can think to define an array of workbooks names, or just a prefix ( if they have same name, but are numbered )

For $i = 0 To UBound($arrWorkbooks) - 1
    $arrWorkbooks[$i] = _Excel_BookAttach()
    If @error Then
        ; Error Handling
    EndIf
Next

Something like this :)

  • Like 1

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

ALWAYS GOOD TO READ:

In case I am violating some pseudo Forum rule, or I am doing something wrong, I want let you know that I'm not doing this on purpose, and for this, I kindly ask you to send me a private message about the "bad content" I posted, in order to edit/remove it, and to not be warned wrongly.
Thanks in advance.

 

Share this post


Link to post
Share on other sites
water
$arrWorkbooks[$i] = _Excel_BookAttach()

Doesn't work. _Excel_BookAttach needs at least one parameter.

  • Like 1

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
water

Ahmed101, which version of Excel do you run?

  • Like 1

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

Hey @water :)

I am writing from smartphone...

I thought that the parameters to be filled were implied :D

I know that you can't call that function without parameters.. 

 

Best Regards.

Edited by FrancescoDiMuro
  • Like 1

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

ALWAYS GOOD TO READ:

In case I am violating some pseudo Forum rule, or I am doing something wrong, I want let you know that I'm not doing this on purpose, and for this, I kindly ask you to send me a private message about the "bad content" I posted, in order to edit/remove it, and to not be warned wrongly.
Thanks in advance.

 

Share this post


Link to post
Share on other sites
Ahmed101

@water i am using Excel 2016 professional plus.
And _Excel_BookAttach() function is so slow even with the arrays way that doesn't make sense.

Share this post


Link to post
Share on other sites
water

When you have a look at the Task Manager - how many Excel processes do you see?


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
Juvigy

How about if you use this code:

$oExcel = ObjGet("", "Excel.Application")
$oExcel.Application.Workbooks("book1.xls").Sheets(1).....

Is it faster?

Share this post


Link to post
Share on other sites
water

I tested with 12 open workbooks and I can recreate the problem. It takes up to 45 seconds to access a workbook.

This is caused by the way ObjGet with index seems to work.


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

  • Similar Content

    • Daniza
      By Daniza
      Hello! where should I start, if I want to have a Progress Bar while waiting for my File to be open, can I use WinWaitActive? Thanks,
    • Evolutionnext
      By Evolutionnext
      I am still a noob and not a programmer, would greatly appreciate your help.
       
      Task:
      Open Excel file with file path and name: C:\Users\GENOBEAUTYPC1\Desktop\ACTIVE BEAUTY LABELS\BeautyMe Label 200ml ACTIVE VERSION.xlsx
      This file path and name is saved in the variable: $sAnswer
      Go to Excel Tab called "formular"
      Go to Cell A1
      Insert the text saved in the variable: $sAnswer2
      ATTENTION!!! This has 2 problems.
      Problem number 1: This text contains special characters that need to be interpreted as raw text. (content is: Gemischt für#30 ml#Mindestens haltbar bis#Maria Wallerstorfer#Anwendung: Täglich 1x morgens auf das gereinigte Gesicht auftragen. Augenkontakt vermeiden.#Über 0 C° und unter 25 C° lagern.#Lot:N8A1028/D30/V2.1#Genome Plus GmbH#Georg-Wrede-St. 13, D-83395 Freilassing#GEN SERUM#DAY)
      Problem number 2: This textis longer than 255 characters.
       
      Can anyone help me?
       
      I try to do it really primitively by opening the excel, waiting until it is open, clicking where the tab is, clicking where the cell is and inserting the content of the variable, but I am stuck at the point where I am limited by 255 characters.
       

      ; Opening the right excel FileChangeDir
                  tooltip("File exists and is called:"&$sAnswer ,300,300)
                  ShellExecute($sAnswer ,"" ,"" ,"" , @SW_MAXIMIZE)
                  sleep(7000)
                  
                  tooltip("Now lets insert the right content into the excel",300,300)
                  MouseClick("left",226,1004)
                  MouseClick("left",52,179)
                  sleep(500)
                  Send("A1")
                  sleep(500)
                  send("{enter}")
                              tooltip("inserting label content",300,300)
                  sleep(500)
                  Send($sAnswer2,  1)
                                          tooltip("inserting INCIS",300,300)
                  sleep(5000)
                  Send($sAnswer3, 1)
                  sleep(5000)
       
       
       
    • AzgarD
      By AzgarD
      Hi guys. I know this is a newbie topic, very newbie, but i've read a lot of stuff and still don't get it. I just need to copy something from Excel cell, paste this in other program, copy something in this program and paste in other Excel cell. Something like...
      Copy A2 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C2 Then Copy A3 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C3 ... And it goes on The problem is, how can i "communicate" with Excel and do this row change? Like A2 to C2 and A3 to C3 ... In a efficient way that can be done like hundreds of times.
      Very newbie question but still not understanding this.
       
      Ty guys.
    • 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.
    • 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
×