FrancescoDiMuro

Naming Excel Sheet

28 posts in this topic

#1 ·  Posted

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

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.

Share this post


Link to post
Share on other sites



#2 ·  Posted

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"

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

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

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.

Share this post


Link to post
Share on other sites

#4 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted

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


Click here to see my signature:

Spoiler

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

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

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.

Share this post


Link to post
Share on other sites

#8 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted

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

#10 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted

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


Click here to see my signature:

Spoiler

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.

Share this post


Link to post
Share on other sites

#12 ·  Posted

I was referring to post #8 :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#13 ·  Posted

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

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.

Share this post


Link to post
Share on other sites

#14 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted

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

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.

Share this post


Link to post
Share on other sites

#17 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted

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

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.

Share this post


Link to post
Share on other sites

#19 ·  Posted

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#20 ·  Posted

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

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.

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

    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • Mag91
      By Mag91
      Hey Community,
      cause im too new in the Auto it world i will try it with the your help. hopefully.
      I woud like to know how i can handle my Problem.
      ----
      I have a Excel Data with 362 random numbers.
      For Example:
      1166642335374 1172899897343
      .....
      this numbers are a part of the filepath ...example
      D:\Projekte\1166_64233_5374
      as u can see its the first number of the Excel data. After the first 4 numbers it shoud make a "_" than another 5 "_"
      This is my first question. How can i handle this to make it Shell execute.
       
      --------
      Second question:
      If i am in the path.
      For Example:
      D:\Projekte\1166_64233_5374
      the code shoud search for specific PDF Files.
      They are named like: 0050569E364B1ED79B900F73E62660EC.pdf
      the first 15 letters are always the same
      0050569E364B1ED
      when he found this data he has to copy it on a Folder on the Desktop.
      (There can also be 2 or 3 pdfs in one Folder with this letters)
      ----
      Please give me some help :-)