Jump to content
FrancescoDiMuro

Naming Excel Sheet

Recommended Posts

FrancescoDiMuro

Good morning :)
I was looking for a method with rename an Excel Sheet, but I didn't find a lot...
So, I decided to make it in another way...
Since I have to create a new Workbook, I thought that, creating a new Workbook, deleting the existing sheet, and adding a new one, would be almost the same...
But I'm encountering a lot of issues, both when I delete the sheet and when I add the new sheet...
This is what I do:
 

; Create the Excel Object...
Local $oExcel_PRV_HW = _Excel_Open(False)
    If @error Then
        MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & @CRLF & "Esteso: " & @extended)
    Else
        ; Create the Workbook with 1 worksheet...
        Local $oWorkbook_New = _Excel_BookNew($oExcel_PRV_HW, 1)
        ; Save the Workbook in order to open it and work with it...
        _Excel_BookSaveAs($oWorkbook_New, $sFilePreventivo, $xlOpenXMLWorkbook, True)
        If @error Then
            MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro." & @CRLF & "Errore: " & @error)
        EndIf
        ; Open the Workbook to work with...
        Local $oWorkbook_PRV_HW = _Excel_BookOpen($oExcel_PRV_HW, $sFilePreventivo)
        If @error Then
            MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file '" & $sFilePreventivo & "'." & @CRLF & "Errore: " & @error)
        Else
            ; Here I would add the _Excel_SheetDelete() and _Excel_SheetAdd() as I did, but they return errors...
        EndIf
    EndIf

Can someone help me out, please? Thanks :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

There is no need to save/reopen the workbook before you can work with it.

Use the following code to rename a worksheet:

$oWorkbook_PRV_HW.Sheets(n).Name = "new name" ; n can be the number of the sheet or the name in quotes like "Old Name"

 

  • 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
FrancescoDiMuro
Just now, water said:

There is no need to save/reopen the workbook before you can work with it.

Use the following code to rename a worksheet:

$oWorkbook_PRV_HW.Sheets(n).Name = "new name" ; n can be the number of the sheet or the name in quotes like "Old Name"

 

Thanks @water!
I don't know so much this syntax... I work mainly with your Excel UDF :) Is that VBA syntax? Where I can find more of it? Thanks for your reply :) 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

Thats the Excel object model. For Excel 2010 the reference can be found here: https://msdn.microsoft.com/en-us/library/ff841127(v=office.14).aspx


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
FrancescoDiMuro

Again thank you @water :)
Have a good day!
Francesco


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

:)


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
FrancescoDiMuro
2 hours ago, water said:

There is no need to save/reopen the workbook before you can work with it.

Use the following code to rename a worksheet:

$oWorkbook_PRV_HW.Sheets(n).Name = "new name" ; n can be the number of the sheet or the name in quotes like "Old Name"

 

Variable must be of type "Object".:
$oWorkbook_PRV_Manodopera.Sheets("Foglio1").Name = $sNomeMacroArea
$oWorkbook_PRV_Manodopera^ ERROR


This is the code:
 

Func AggiungiMacroArea()
    Local $a_IE_MicroArea[8] = [$checkbox_MicroArea1, $checkbox_MicroArea2, $checkbox_MicroArea3, $checkbox_MicroArea4, _
                                $checkbox_MicroArea5, $checkbox_MicroArea6,$checkbox_MicroArea7, $checkbox_MicroArea8]

    Local $aMicroArea[8] = [$input_MicroArea1, $input_MicroArea2, $input_MicroArea3, $input_MicroArea4, _
                                $input_MicroArea5, $input_MicroArea6, $input_MicroArea7, $input_MicroArea8]

    Local $aGiorno[8] = [$radio_Giorni1, $radio_Giorni2, $radio_Giorni3, $radio_Giorni4, $radio_Giorni5, $radio_Giorni6, $radio_Giorni7, $radio_Giorni8]

    Local $aACorpo[8] = [$radio_ACorpo1, $radio_ACorpo2, $radio_ACorpo3, $radio_ACorpo4, $radio_ACorpo5, $radio_ACorpo6, $radio_ACorpo7, $radio_ACorpo8]

    Local $aQuantita[8] = [$input_QuantitaManodopera1, $input_QuantitaManodopera2, $input_QuantitaManodopera3, $input_QuantitaManodopera4, _
                           $input_QuantitaManodopera5, $input_QuantitaManodopera6, $input_QuantitaManodopera7, $input_QuantitaManodopera8]

    Local $aPrezzo[8] = [$input_Prezzo1, $input_Prezzo2, $input_Prezzo3, $input_Prezzo4, $input_Prezzo5, $input_Prezzo6, $input_Prezzo7, $input_Prezzo8]

    Local $aIntestazione_Manodopera[4] = ["Descrizione Micro-Area", "Quantita'", "UDM", "Prezzo"]
    Local $aColonne_PRV_Manodopera[4] = ["A", "B", "C", "D"]
    Local $oWorkbook_PRV_Manodopera
    Local $i

    If($iNuovaMacroArea == 0) Then
        Local $sFilePreventivoManodopera = $sCartellaPreventivi & "\" & $sNomePreventivo & "_MANODOPERA.xlsx"
        Local $oExcel_PRV_Manodopera = _Excel_Open(False)
        If @error Then
            MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & "Esteso: " & @extended)
        Else
            Local $oWorkbook_PRV_Manodopera_New = _Excel_BookNew($oExcel_PRV_Manodopera, 1)
            If @error Then
                MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione del file '" & $sFilePreventivoManodopera & "'" & @CRLF & "Errore: " & @error)
            Else
                _Excel_BookSaveAs($oWorkbook_PRV_Manodopera_New, $sFilePreventivoManodopera, $xlOpenXMLWorkbook, True)
                If @error Then
                    MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro: " & @CRLF & $sFilePreventivoManodopera & @CRLF & "Errore: " & @error)
                Else
                    $oWorkbook_PRV_Manodopera = _Excel_BookOpen($oExcel_PRV_Manodopera, $sFilePreventivoManodopera)
                    $oWorkbook_PRV_Manodopera.Sheets(1).Name = $sNomeMacroArea
                    $oWorkbook_PRV_Manodopera.Sheets($sNomeMacroArea).Activate
                    For $i = 0 To 3
                        _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, $aIntestazione_Manodopera[$i], $aColonne_PRV_Manodopera[$i] & "1")
                    Next
                    Local $j = 2
                    For $i = 0 To 7
                        If(GUICtrlRead($a_IE_MicroArea[$i]) = $GUI_CHECKED) Then
                            _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, GUICtrlRead($aMicroArea[$i]), "A" & $j)
                            _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, GUICtrlRead($aQuantita[$i]), "B" & $j)
                            If(GUICtrlRead($aGiorno[$i]) = $GUI_CHECKED) Then
                                _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, "Giorni", "C" & $j)
                                _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, Chr(128) & GUICtrlRead($aPrezzo[$i]) * 8 * GUICtrlRead($aQuantita[$i]), "D" & $j)
                            ElseIf(GUICtrlRead($aACorpo[$i]) = $GUI_CHECKED) Then
                                _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, "A Corpo", "C" & $j)
                                _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, Chr(128) & GUICtrlRead($aPrezzo[$i]) * GUICtrlRead($aQuantita[$i]), "D" & $j)
                            EndIf
                        EndIf
                        $j+=1
                    Next
                    _Excel_BookSave($oWorkbook_PRV_Manodopera)
                    $iNuovaMacroArea+=1
                    Local $sRisposta = MsgBox($MB_YESNO, "Informazione:", "Macro-Area creata correttamente." & @CRLF & "Vuoi creare un'altra Macro-Area?")
                    If($sRisposta = $IDYES) Then
                        _Excel_SheetAdd($oWorkbook_PRV_Manodopera, -1, False, 1, "Foglio1")
                        _ArrayDisplay(_Excel_SheetList($oWorkbook_PRV_Manodopera))
                        If @error Then
                            MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'aggiunta di un nuovo foglio di lavoro." & @CRLF & "Errore: " & @error)
                        EndIf
                        ; "Aggiornamento" ( Eliminazione - Creazione ) della GUI per permettere l'inserimento di nuovi dati all'utente...
                        GUIDeleteCPM()                ; Eliminazione della GUICreaPreventivoManodopera
                        GUICreaPreventivoManodopera() ; Creazione della GUICreaPreventivoManodopera
                    Else
                        GUIDeleteCPM()                ; Eliminazione della GUICreaPreventivoManodopera
                        _Excel_Close($oExcel_PRV_Manodopera)
                        If @error Then
                            MsgBox($MB_ICONERROR, "Errore!","Errore durante la chiusura dell'oggetto Excel." & @CRLF & "Errore: " & @error)
                        EndIf
                    EndIf
                EndIf
            EndIf
        EndIf
    Else
        If($sNomeMacroArea <> "") Then
            $oWorkbook_PRV_Manodopera.Sheets("Foglio1").Name = $sNomeMacroArea
            $oWorkbook_PRV_Manodopera.Sheets($sNomeMacroArea).Activate
            For $i = 0 To 3
                _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, $aIntestazione_Manodopera[$i], $aColonne_PRV_Manodopera[$i] & "1")
            Next
            Local $j = 2
            For $i = 0 To 7
                If(GUICtrlRead($a_IE_MicroArea[$i]) = $GUI_CHECKED) Then
                    _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, GUICtrlRead($aMicroArea[$i]), "A" & $j)
                    _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, GUICtrlRead($aQuantita[$i]), "B" & $j)
                    If(GUICtrlRead($aGiorno[$i]) = $GUI_CHECKED) Then
                        _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, "Giorni", "C" & $j)
                        _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, Chr(128) & GUICtrlRead($aPrezzo[$i]) * 8 * GUICtrlRead($aQuantita[$i]), "D" & $j)
                    ElseIf(GUICtrlRead($aACorpo[$i]) = $GUI_CHECKED) Then
                        _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, "A Corpo", "C" & $j)
                        _Excel_RangeWrite($oWorkbook_PRV_Manodopera, $oWorkbook_PRV_Manodopera.ActiveSheet, Chr(128) & GUICtrlRead($aPrezzo[$i]) * GUICtrlRead($aQuantita[$i]), "D" & $j)
                    EndIf
                EndIf
                $j+=1
            Next
            _Excel_BookSave($oWorkbook_PRV_Manodopera)
            $iNuovaMacroArea+=1
        EndIf
    EndIf
EndFunc

Practically, I write some texts in fields of a GUI... Then, I write those texts in an Excel file which I create just 1 time, and then, if the user want to keep entering texts in the Excel file, a new sheet is added, and the same GUI used the first time is used again, since the user doesn't want to exit.

Why I get that error, dear @water?
Thanks and, I don't know if it's only me but, when I try to overwrite the file when I save it with _Excel_BookSaveAs(), the error = 4 has been returned by the function, even If I set the flag overwrite on True.

Thanks again :) 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

As I stated before there is no need to reopen the workbook. I'm even sure that opening an already open workbook will return an error.
Use:

$oWorkbook_PRV_Manodopera_New = _Excel_BookNew($oExcel_PRV_Manodopera, 1) ; Local removed as the variable is already defined
            If @error Then
                MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione del file '" & $sFilePreventivoManodopera & "'" & @CRLF & "Errore: " & @error)
            Else
                _Excel_BookSaveAs($oWorkbook_PRV_Manodopera_New, $sFilePreventivoManodopera, $xlOpenXMLWorkbook, True)
                If @error Then
                    MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro: " & @CRLF & $sFilePreventivoManodopera & @CRLF & "Errore: " & @error)
                Else
                    ; $oWorkbook_PRV_Manodopera = _Excel_BookOpen($oExcel_PRV_Manodopera, $sFilePreventivoManodopera) ; Not needed
                    $oWorkbook_PRV_Manodopera.Sheets(1).Name = $sNomeMacroArea
                    $oWorkbook_PRV_Manodopera.Sheets($sNomeMacroArea).Activate

 


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
antonioj84
10 hours ago, water said:

There is no need to save/reopen the workbook before you can work with it.

Use the following code to rename a worksheet:

$oWorkbook_PRV_HW.Sheets(n).Name = "new name" ; n can be the number of the sheet or the name in quotes like "Old Name"

 

 

Hey water,

i use this to save a new copy after modify  

$savewb = $sNewPickFolderPath & "\"&$checknum&"CasaPickList.xlsx"
_Excel_BookSaveAs($datawb,$savewb, $xlWorkbookDefault,True)

 

Share this post


Link to post
Share on other sites
water

What I want to say is that:

  • You created a new workbook
  • You save this workbook
  • After the save the workbook is still open (even if it now has a different name)
  • If you then open this workbook again you will get an error from Excel - hence the object error with your script as the return value of _Excel_BookOpen is 0

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
FrancescoDiMuro

Good morning @water:)
Were you talking about the post #8 or you were replying to @antonioj84?
Thanks :) 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

I was referring to post #8 :)


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
FrancescoDiMuro

Thanks @water:)
But I still don't follow you...
In the post #10, you wrote that I re-open the workbook again, but, in fact, I don't do this, just because I don't want to close it if the user want to add another sheet... 

I open it when I can the function ONLY IF the variable $iNuovaMacroArea has the value == 0 ( so, when the user has called the function for the first time ), else, if the variable $iNuovaMacroArea has the value > 0, it means that the user has already created the workbook, saved, and opened, ready to add a new sheet.
And I don't know why, when I want to set the name of the NEW worksheet, I get that lines...
Thanks again :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

Based on your code in post #7 I have added comments to make clear why your code crashes:

Local $oExcel_PRV_Manodopera = _Excel_Open(False)
        If @error Then
            MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & "Esteso: " & @extended)
        Else
            Local $oWorkbook_PRV_Manodopera_New = _Excel_BookNew($oExcel_PRV_Manodopera, 1)
            If @error Then
                MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione del file '" & $sFilePreventivoManodopera & "'" & @CRLF & "Errore: " & @error)
            Else
                _Excel_BookSaveAs($oWorkbook_PRV_Manodopera_New, $sFilePreventivoManodopera, $xlOpenXMLWorkbook, True) ; <== Here you save the workbook
                If @error Then
                    MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro: " & @CRLF & $sFilePreventivoManodopera & @CRLF & "Errore: " & @error)
                Else
                    $oWorkbook_PRV_Manodopera = _Excel_BookOpen($oExcel_PRV_Manodopera, $sFilePreventivoManodopera) ; <== If save was successful you reopen the book. This will fail and set $oWorkbook_PRV_Manodopera to 0
                    $oWorkbook_PRV_Manodopera.Sheets(1).Name = $sNomeMacroArea ; <== This fails because $oWorkbook_PRV_Manodopera is no longer an object
                    $oWorkbook_PRV_Manodopera.Sheets($sNomeMacroArea).Activate

 


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
water

Is it intended that you save the object of the new Workbook in variable $oWorkbook_PRV_Manodopera_New and the object of the reopened workbook in a different variable $oWorkbook_PRV_Manodopera?


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
FrancescoDiMuro

I did this because I want to save the workbook I've created, and I need an istance of the worksheet to use _Excel_BookSaveAs()...
So I used the variable $oWorkbook_PRV_Manodopera_New to create the file ".xlsx", save it, and open it, using $oWorkbook_PRV_Manodopera as the "main" instance...

By the way, I found the issue, and I solved declaring $oWorkbook_PRV_Manodopera_New as a Global variable instead of Local...
The error were caused because:
- First time the user want to create the file, everything's fine...
- Then, when the user want to add another sheet, the function is called again, but, because of the if I put before the opening of the Excel object and Workbook object, I skip all that stuffs, and I pretended to use an object that, in fact, there were no more...

Thanks for your help @water :)
You're a good person and I'm very happy when I talk with you about these things...
If you want to suggest me something about to improve my code, I'll always listen to you ( and everyone else who want to help ) :) 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

I still would drop the reopening of the workbook. Because this creates a second reference to the same workbook.
I suggest to :

  • Rename variable $oWorkbook_PRV_Manodopera_New to $oWorkbook_PRV_Manodopera in your script. So you have just a single variable referencing your workbook
  • Drop line
    $oWorkbook_PRV_Manodopera = _Excel_BookOpen($oExcel_PRV_Manodopera, $sFilePreventivoManodopera)

This reduces unneeded complexity and makes it easier to maintain in the future.


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
FrancescoDiMuro

So, when I save a Workbook, I open it too? In this case, yes, I can use just a variable to make what I have to do with that variable :) 
Thanks @water :) 
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

No.

  • You use _Excel_BookNew to create/open a new workbook. The function returns a reference to this workbook which you save in a variable.
  • _Excel_BookSave  does what you expect it to do: It saves an already opened workbook (in your case the workbook created by _Excel_BookNew) - nothing else. Return value = 1 on success.
  • Do all the manipulation to the workbook by using the variable as returned by _Excel_BookNew.
     

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
FrancescoDiMuro

Sorry @water! I wanted to type _Excel_BookOpen instead of _Excel_BookSave...
All clear! 
Thank you! :)
Have a good day.
Francesco


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

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