SorryButImaNewbie

[SOLVED] _Excel_BookOpen how to connect to already opened Excel Application, without knowing the filepath

6 posts in this topic

#1 ·  Posted (edited)

Hello dear users!

I have a bit of a problem with my excel script I try to make. My goal is that my script would read through an excelfile's columns and rows, identifiying key words, then I would like to reorganize the rows and colums in the a specified "way", "format" (we get different kind of excels in and i wish to produce a uniform excels from them, the data is the same, so I know that there is an XY column somewhere, I just have to find it, and put it in the right place)

Now I can read values from cells (or range of cells) but only if I know the exact file name and path.

Can I make autoit to somehow attach to the already opened excel? This way I would be able to make the script, so the user just open the excel which he/she wants to format, clicks on go, and viola.

Instead now I'm thinking adding 2 textboxes to my GUI so the user can write down the filename and file path, which isnt that ideal.

My original idea, was to make the script find the "borders" of the filled out excel form, copy it away to the right, delete the original data, reorganize it the way it should be (to the area which it cleared), and thats it. The user can then quickly check if everything is okey, and he/she can delete the copied original data set if its needed.

 

So in short my problem is, how to connect my script to an excel thats presumaby already opened, without knowing the filename and path?

Thank you for your help! (and sorry if there is already a post about this I havent found it)

Edited by SorryButImaNewbie
SOLVED

Share this post


Link to post
Share on other sites



You want to attach to an already open workbook, correct? The help file is your friend: Look at (funny enough) _Excel_BookAttach. You can attach to an open book based on filepath, filename, or title.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

But how?

I can attach only if i know the filepath, filename, or title. How do I get these from the already opened excel?

Share this post


Link to post
Share on other sites

Use _Excel_BookList to retrieve an array of open workbooks. Then select the needed one and use _Excel_BookAttach to connect to this workbook.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#5 ·  Posted (edited)

Once again, a trip through the help file would be beneficial. Look at _Excel_BookList.

Edit: D'oh, too slow

Edited by JLogan3o13
1 person likes this

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Thanks!

First sorry for only replying now... This is a pet project of mine, and I didn't had time to check _Excel_BookList.

Sorry I know that everything is in the help files usually :D and if not 98% of the time it can be found on this very forum, I searched both, but I was unable to find what i was looking for. (thats my mistake, sorry)

I just started to play around with my code now, adding in the displayarray from the help file.

The array display shows me the running excel programs, but also shows me 3 additional things. I have a quess what are these, but I though I would ask, just to be sure.

2 XLAM file, from the library/analysis file, called  ATPVBAEN and FUNCRES. My guess is that these are something from the analysis toolpack, I'm not sure why they are here.

The 3. one is the SOLVER.XLAM. Thats the solver, I'm not sure why it is listed with the excel workbooks, but as long as it always the last member of the array (seems like it so far) its not a problem.

I thought that I should write a regex, that search for these, and remove them from the array before I start to do other operations.

(attached a JPG from a displayed array, 0. is an open excel, 1,2 are XLAM files, 3. an other opened excel, 4. is the solver)

Thank you, and sorry again for the slow reply

ArrayList.JPG

 

So I read the data from the array, I think I will use regex to find the .xls or .xlsm (excel) files, and make an error if there are more then one excel opened, asking the user, to open only one at a time.

If IsArray($aWorkBooks) Then
        Local $array0 = $aWorkBooks[0][1]
        MsgBox($MB_SYSTEMMODAL, "Excel", " Value of array0: " & $array0)
        Local $array1 = $aWorkBooks[1][1]
        MsgBox($MB_SYSTEMMODAL, "Excel", " Value of array1: " & $array1)
        Local $array2 = $aWorkBooks[2][1]
        MsgBox($MB_SYSTEMMODAL, "Excel", " Value of array2: " & $array2)
        Local $array3 = $aWorkBooks[3][1]
        MsgBox($MB_SYSTEMMODAL, "Excel", " Value of array3: " & $array3)

        Else
            Exit
    EndIf

 

Edited by SorryButImaNewbie
adding a bit of code, just for good measure

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

    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.