Jump to content
Sign in to follow this  
FrancescoDiMuro

Naming Excel Sheet

Recommended Posts

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

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"

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

I was referring to post #8 :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
    • By Rajat231
      I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
      SavingWorksheets.au3
×
×
  • Create New...