Jump to content
SorryButImaNewbie

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

Recommended Posts

SorryButImaNewbie

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
JLogan3o13

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
SorryButImaNewbie

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
water

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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
JLogan3o13

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

Edit: D'oh, too slow

Edited by JLogan3o13
  • Like 1

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

Share this post


Link to post
Share on other sites
SorryButImaNewbie

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

    • yasha
      By yasha
      i want am trying to select a nimber to run a program and then select where to save the excel result at before hand
      the problem is that it does not save in the folder i want but the folder before any solutions
      #.................
      $sFolder = ""
          ; Create a constant variable in Local scope of the message to display in FileSelectFolder.
          Local Const $sMessage = "Select a folder"
          ; Display an open dialog to select a file.
          $sFileSelectFolder = FileSelectFolder($sMessage, $sFolder)
          If @error Then
              ; Display the error message.
              MsgBox($MB_SYSTEMMODAL, "", "No folder was selected.")
          Else
              ; Display the selected folder.
              MsgBox($MB_SYSTEMMODAL, "", "You chose the following folder:" & @CRLF & $sFileSelectFolder)
          EndIf
      .......
      ........
      $oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
      $oExcel.Visible = 1                                        ; Let Excel show itself
      $oExcel.Workbooks.Open("J:\OPS\OPS_Share\Planners\2 - Weekly Reports\Auto download\"& $YY & $MM & $DD & " ORDER.xls",0)
      $oExcel.ActiveWorkbook.Saveas ( $sFileSelectFolder,""& $YY & $MM & $DD & " ORDER.xlsx", 1)
      $oExcel.ActiveWorkBook.Close
      $oExcel.Quit
      i only want to save it as ddmmyy order inside documents but it saves in libraries as documents ddmmyy order.
    • PiyushJhawar
      By PiyushJhawar
      I am part of QA team of an analytics application. We support third party tools like Excel , Tableue .
      I have to write automation script that connect Excel to our analytics application. In short i want below to automate
      > Open Excel
      > Click on "Data" option available in header and then click on "From Other Services " then click on "From Analysis Services"
      > It will open pop up and then need to write username password there.
      I am new in this tool . Can any one please provide me link of any document that help me to create above script
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • nooneclose
      By nooneclose
      My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.
      This is what I have so far.
      Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill") _ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") _Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)  
      Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 
    • nooneclose
      By nooneclose
      How do I properly convert this to Autoit? This is a VBA macro that I recorded in Excel.
       ActiveSheet.Outline.ShowLevels RowLevels:=2 I need this to close my subtotal once it is finished. 
      any help will be greatly appreciated. 
×