Naming Excel Sheet

[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

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

 

[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  

[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

[FrancescoDiMuro]

Again thank you @water
Have a good day!
Francesco

[water]

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

expandcollapse popup

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  

[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

 

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

 

[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

[FrancescoDiMuro]

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

[water]

I was referring to post #8

[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

[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

 

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

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

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

[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  
 

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

[FrancescoDiMuro]

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