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-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
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

    • 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
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
    • 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!
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
    • tuffgong
      By tuffgong
      Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:
      #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) barcodePrint() Func barcodePrint() $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) FileOpen($sFilename, $FO_OVERWRITE) ;Global $oWorkbook = _Excel_BookAttach($oExcel) Global $sSTCArray = _Excel_RangeRead($Read) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i]) Next _Excel_Close($oExcel) FileMove($sFilename, "***file path***\print.bch") EndFunc However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:
        
      Global $sPrefix Global $oPath Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) Global $aArray Global $bExcel _Excel_BookNew($bExcel) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm") $sPrefix = $aArray[1][1] $oPath = $aArray[2][1] barcodePrint() EndSwitch WEnd Func barcodePrint() $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx") $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@" Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) $oCopy = _Excel_RangeRead($sWorkbook) _Excel_RangeWrite($oWorkbook, Default, $oCopy) FileOpen($sFilename, $FO_OVERWRITE) $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3) ;_ArrayDisplay($sSTCArray) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i]) Next _Excel_Close($oExcel) ;FileMove($sFilename, $oPath) Exit EndFunc This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!
×