Sign in to follow this  
Followers 0
nikosliapis

Excel book get name

5 posts in this topic

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


Coding can be fun when you do it your own.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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:

#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 by 99ojo

Share this post


Link to post
Share on other sites

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


Coding can be fun when you do it your own.

Share this post


Link to post
Share on other sites

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.


Coding can be fun when you do it your own.

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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
Sign in to follow this  
Followers 0