Excel book get name

[nikosliapis]

Hello everybody.

I am new to autoit so plese don't laugh with my question

I am already familiar with the function "_Excelsheetnameget"

what i want is a function that returns the name of the excel BOOK opened (something like "_excelbooknameget")

The second step of my project requires a function that will return the name of every excel book i open

I would apreeciate any help given

Thanks in advance

[99ojo]

Hi,

it's tested with german Excel 2000. You may to change the windowtitle "Microsoft Excel - " string.

#include <array.au3>
#include <excel.au3>

MsgBox (0, "Opened Excelbook is .....", _getopenexbook ())
$var = _getnameworkbooks ()
_ArrayDisplay ($var)

;get open workbook
Func _getopenexbook ()
    $excel = WinList ("Microsoft Excel")
    Return StringReplace ($excel [1] [0], "Microsoft Excel - ", "")
EndFunc

;get names of all workbooks
Func _getnameworkbooks ()
    Local $anzahl, $workbooks [1]
    $oExcel = _ExcelBookAttach ("Microsoft Excel - " & _getopenexbook (), "Title")
    $anzahl = $oExcel.Application.Workbooks.Count
    ReDim $workbooks [$anzahl]
    For $i = 1 To $anzahl
        $workbooks [$i - 1] = $oExcel.Application.Workbooks($i).Name
    Next
    Return $workbooks
EndFunc

;-))

Stefan

[EDIT1] Some modification of code above.

[EDIT2] Some modifications and some comments:

expandcollapse popup

#include <array.au3>
#include <excel.au3>
If ProcessExists ("excel.exe") Then
    MsgBox (0, "Opened Excelbook is .....", _getopenexbook ())
    $var = _getnameworkbooks ()
    _ArrayDisplay ($var)
Else
    MsgBox (0,"Error", "Please start Excel first!")
EndIf

;get open workbook
Func _getopenexbook ()
    ;get windowstitle of Microsoft Excel Windows, e.g. "Microsoft Excel - temp.xls"
    ;see also helpfile function WinList
    $excel = WinList ("Microsoft Excel")
    If $excel [0] [0] = 0 Then
        Return 0
    Else
        ;as return cut off "Microsoft Excel -" and you get the name of the recent open excelfile
        ;the windowstitle is arrayelement [1] [0] see WinList
        ;works only if every excelfile is opened with one excel process
        Return StringReplace ($excel [1] [0], "Microsoft Excel - ", "")
    EndIf
EndFunc

;get names of all workbooks
Func _getnameworkbooks ()
    Local $anzahl ;variable to store amount of open excel files
    Local $workbooks [1]
    ;create an excel object, attaching to an existent excel
    $oExcel = _ExcelBookAttach ("Microsoft Excel - " & _getopenexbook (), "Title")
    If @error Then
        MsgBox (0,"Error", "Error Attaching Excel.")
        ;return with 1.st element of array is set to 0
        $workbooks [0] = 0
        Return $workbooks
    EndIf
    ;get the count of open excel files, see msdn excel objects
    $anzahl = $oExcel.Application.Workbooks.Count
    ;Redimming array size for storing name of excel files to fit
    ReDim $workbooks [$anzahl]
    ;loop over count
    For $i = 1 To $anzahl
        ;get the names of open excel files
        $workbooks [$i - 1] = $oExcel.Application.Workbooks($i).Name
    Next
    ;return the array
    Return $workbooks
EndFunc

Edited December 2, 2009 by 99ojo

[nikosliapis]

Thanks very much Stefan it works great

I don't yet understand how you did it but in time i hope i will

Thanks again

[nikosliapis]

I really appreciate your comments regarding the functions you showed me.

I couldn't find the commands you used (for example : "$oExcel.Application.Workbooks.Count") on the autoit help file

So i believe that they are Macros or comands from another language , maybe Virtual basic or C++.

My first question is where can i read so that i can learn more about this Kind of commands

Secondly i will explain my full project because i can't figure it out.

I want to make a Excel autosave script. A script that will run continously, detect the opened excel workbooks, create a folder for each workbook and inside this folder saves a copy of the workbook with a time stamp.

I thought that it was gonna be easy if i "had" the names of the opened excel workbooks ,but of course i was wrong.

I tried to search the forum but couldn't find anything usefull.

I believe that it is a very usefull script especially if you accidentally press "NO" when Excel prompts to save

changes.

[PsaltyDS]

I really appreciate your comments regarding the functions you showed me.

I couldn't find the commands you used (for example : "$oExcel.Application.Workbooks.Count") on the autoit help file

So i believe that they are Macros or comands from another language , maybe Virtual basic or C++.

My first question is where can i read so that i can learn more about this Kind of commands

Your example uses the COM (Common Object Model) interface to Excel. The specific objects used, and their methods and properties, are defined by the application that provides the interface (in this case, Excel). The $oExcel.Application.Workbooks object is a collection object of all open workbooks, and the .count property obviously indicates how many workbooks there are in that collection. Essentially, almost any example you can find written in VBA (Visual Basic for Applications) can be easily translated to AutoIt.

There is a learning curve, but it's well worth climbing since there are so many Windows applications that provide this type of interface, and AutoIt can use them. One of the most popular UDFs for AutoIt, IE.au3, is all about using the COM interface of IE to manipulate the DOM (Document Object Model) in the same ways.