Some information about Excel UDF(s)(?)

[FrancescoDiMuro]

Good evening everybody How it's going? Hope it's everything fine  Dear reader, I'm just looking for a tons information about Excel... Why? I hope to be largely clear as I can! Always, sorry for my English! But now, I'm gonna explain to you what I'm trying to do... I have an Excel file with 4 worksheet in it... Every worksheet is called in a different way ( in order, they are: "WR-ANALOG", "WR-DIG", "RD-ANALOG", "RD-DIG" ), and I have to work in different ways with each worksheet as well. From this Worksheet, I have to retrieve some information, and, maybe, if possible, skip the rows where a cell of a specific column is blank. I'll give you an example in a moment. By the way, for now, I managed to create a GUI from which I choose the file, I open the object Excel, and I read from the worksheet I selected... But know, I have to store this "reading" somewhere... I used the _Excel_Range_Read() and it works fine, but it doesn't skip blank rows, and, in my project, I want to create a new Excel file, in which store the $sResult returned by the function. After copied, created a new Excel file and pasted the result I retrieved, I have to "Find and replace" some things... How can I do it? I'll give you all you need, but please, help me. In fact, things I have to do are:
- copy rows ( neither every column of the row, but just some specifics ) and possibly, skip rows where a cell of a specific column is blank;
- create a file and paste the result I got from some function;
- Find and Replace values I retrieved with parameters I give from GUI;
- save the file I've created.

Thanks everyone for the help. Have a good day. Francesco  

[water]

Shouldn't be too hard.
About how many rows do we talk?

[FrancescoDiMuro]

Just now, water said:

Shouldn't be too hard.
About how many rows do we talk?

Not over 1000 rows... I was looking at the VBA properties about Excel... So, can you help me out water? Thank you  If only AutoIt does classes, I would join instantly ( in Italy )

[water]

Let's check task by task.
Copy rows:
Use _Excel_RangeRead to read all cells of a worksheet into an array, then loop through the array and copy all needed cells except where a cell of a specific column is blank to another array. When done copy this new array to a new worksheet or workbook by using _Excel_RangeWrite.

[FrancescoDiMuro]

18 hours ago, water said:

Let's check task by task.
Copy rows:
Use _Excel_RangeRead to read all cells of a worksheet into an array, then loop through the array and copy all needed cells except where a cell of a specific column is blank to another array. When done copy this new array to a new worksheet or workbook by using _Excel_RangeWrite.

How can I access the element "column", and, in specific, the cell of that column? Take the example of $sResult = _ExcelRangeRead()... It returns an array... How can I set a condition to verify that a specific cell of a specific column is blank? How $sResult is composed? I.E. $sResult(0)(1)... Thank you for the reply water

[water]

Excel numbers the columns starting with 1 (column "A"), AutoIt starts with an index of 0.
So if you would need to check column 3 ("C" ) for a blank value you would need to check index 2 in AutoIt:

$aResult = _ExcelRangeRead()
For $i = 0 to UBound($aResult) - 1
    If $aResult[$i][2] = "" Then ... 
Next

 

[FrancescoDiMuro]

On 7/10/2016 at 5:18 PM, water said:

Excel numbers the columns starting with 1 (column "A"), AutoIt starts with an index of 0.
So if you would need to check column 3 ("C" ) for a blank value you would need to check index 2 in AutoIt:

$aResult = _ExcelRangeRead()
For $i = 0 to UBound($aResult) - 1
    If $aResult[$i][2] = "" Then ... 
Next

 

Thanks for the reply water... These days I'm alittle busy... But as soon as I have a bot time to test it, I'll try  So, once I did this, I'm able to read the content of the file, skipping blank rows... Reaòlly thank you for the help Have a good day! Francesco

[FrancescoDiMuro]

Hey guys, I don't know if someone will read this post today, but I still need help about my project... Can anyone help me please? I'm having issues with reading the content of a .xls file... Thanks for the help! Have a good day! 

 

[l3ill]

Molto Vago    ( No I don't speak Italian (Google Translater)  )

Post your code, explain whats not working.

Did you get Copy Rows working with waters instructions?

[FrancescoDiMuro]

2 hours ago, l3ill said:

Molto Vago    ( No I don't speak Italian (Google Translater)  )

Post your code, explain whats not working.

Did you get Copy Rows working with waters instructions?

expandcollapse popup

#include <ExcelConstants.au3>
#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=c:\users\portatile-60\documents\documenti lavoro\autoit\database_creator_csv\form_db_creator.kxf
$Form_DB_Creator = GUICreate("DB Creator (c)", 358, 140, 192, 124)
$label_ListaIO = GUICtrlCreateLabel("Lista I/O:", 14, 26, 65, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$input_ListaIO = GUICtrlCreateInput("", 94, 26, 217, 21, BitOR($GUI_SS_DEFAULT_INPUT,$ES_READONLY))
$button_ScegliFile = GUICtrlCreateButton("...", 318, 26, 25, 22)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$label_Device = GUICtrlCreateLabel("Device:", 14, 68, 57, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$input_Device = GUICtrlCreateInput("", 94, 68, 41, 21)
$label_Nodo = GUICtrlCreateLabel("Nodo", 161, 68, 42, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$input_Nodo = GUICtrlCreateInput("", 230, 68, 50, 21)
$button_Crea = GUICtrlCreateButton("Ok", 310, 58, 33, 65)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$combobox_DB = GUICtrlCreateCombo("", 94, 104, 51, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL,$CBS_UPPERCASE,$WS_BORDER))
GUICtrlSetData($combobox_DB, "DB10|DB11|DB12|DB13")
$label_DB = GUICtrlCreateLabel("DB:", 14, 108, 29, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $button_ScegliFile
            Local $sListaIO
            $sListaIO = FileOpenDialog("Scegli Lista I/O...", @ScriptDir, "Excel Files (*.xls)")
            GUICtrlSetData($input_ListaIO, $sListaIO)
        Case $button_Crea
            Local $sScelta
            $sScelta = GUICtrlRead($combobox_DB)
            Switch $sScelta
                Case "DB10"
                    WorkWithDB10()
                Case "DB11"
                    MsgBox(1, "", "DB11")
                Case "DB12"
                    MsgBox(1, "", "DB12")
                Case "DB13"
                    MsgBox(1, "", "DB13")
            EndSwitch
    EndSwitch
WEnd

Func WorkWithDB10()
    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open(False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore = " & @error & ", Informazioni = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sListaIO, True, False)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Errore!", "Errore durante l'apertura della Cartella di Lavoro '" & @ScriptDir & $sListaIO & "'." & @CRLF & "Errore = " & @error & ", Informazioni = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf
    $oWorkbook.Sheets("WR-ANALOG").Activate
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default)
    _ArrayDisplay($aResult)
    _Excel_Close($oExcel)
EndFunc

Ahahahah! For a moment I have been like "  , an Italian on this forum?", and then "Nah, fu xD".
Thanks for the reply by the way. 
Now I'm working with the Excel file I'd have used since I started this project, and because of this, I had to change the arrangement of my program, so... Now, this is the source code I'm working on, and, this is the Excel file I'm working with. Thanks anyone for the help! 

 

The problem I'm having is that _ArrayDisplay doesn't return anything! I can explain what I need, maybe you can help more specifically.

 

PS: Please, use the file I've uploaded just for your own. Don't publish it on any website or blog or anything else. That file contains important work references. Thank you.

 

Edited November 10, 2016 by FrancescoDiMuro

[l3ill]

It seems there are other problems.

I cant get your excel file to show any data. whether opening via script or just opening outright it is an empty excel object without any sheets.

I tested this with one of my own excel docs and I get an array.

Spoiler

expandcollapse popup

#include <ExcelConstants.au3>
#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

#cs ; ======================================Testing

#Region ### START Koda GUI section ### Form=c:\users\portatile-60\documents\documenti lavoro\autoit\database_creator_csv\form_db_creator.kxf
$Form_DB_Creator = GUICreate("DB Creator (c)", 358, 140, 192, 124)
$label_ListaIO = GUICtrlCreateLabel("Lista I/O:", 14, 26, 65, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$input_ListaIO = GUICtrlCreateInput("", 94, 26, 217, 21, BitOR($GUI_SS_DEFAULT_INPUT,$ES_READONLY))
$button_ScegliFile = GUICtrlCreateButton("...", 318, 26, 25, 22)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$label_Device = GUICtrlCreateLabel("Device:", 14, 68, 57, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$input_Device = GUICtrlCreateInput("", 94, 68, 41, 21)
$label_Nodo = GUICtrlCreateLabel("Nodo", 161, 68, 42, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$input_Nodo = GUICtrlCreateInput("", 230, 68, 50, 21)
$button_Crea = GUICtrlCreateButton("Ok", 310, 58, 33, 65)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$combobox_DB = GUICtrlCreateCombo("", 94, 104, 51, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL,$CBS_UPPERCASE,$WS_BORDER))
GUICtrlSetData($combobox_DB, "DB10|DB11|DB12|DB13")
$label_DB = GUICtrlCreateLabel("DB:", 14, 108, 29, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $button_ScegliFile
            Local $sListaIO
            $sListaIO = FileOpenDialog("Scegli Lista I/O...", @ScriptDir, "Excel Files (*.xls)")
            GUICtrlSetData($input_ListaIO, $sListaIO)
        Case $button_Crea
            Local $sScelta
            $sScelta = GUICtrlRead($combobox_DB)
            Switch $sScelta
                Case "DB10"
                    WorkWithDB10()
                Case "DB11"
                    MsgBox(1, "", "DB11")
                Case "DB12"
                    MsgBox(1, "", "DB12")
                Case "DB13"
                    MsgBox(1, "", "DB13")
            EndSwitch
    EndSwitch
WEnd

#ce ; ======================================Testing

            Local $sListaIO
            $sListaIO = FileOpenDialog("Scegli Lista I/O...", @ScriptDir, "Excel Files (*.xls; xlsx)")

            WorkWithDB10()
;================================================Testing
Func WorkWithDB10()
    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open(False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore = " & @error & ", Informazioni = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sListaIO, True, False)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Errore!", "Errore durante l'apertura della Cartella di Lavoro '" & @ScriptDir & $sListaIO & "'." & @CRLF & "Errore = " & @error & ", Informazioni = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf
;~     $oWorkbook.Sheets("WR-ANALOG").Activate
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default)
    _ArrayDisplay($aResult)
    _Excel_Close($oExcel)
EndFunc

 

 

[FrancescoDiMuro]

Mmmm... There is a pivot table in there,  maybe it's it? I tried with another Excel file ( always .xls ), and it works great... What can I do? 

[l3ill]

If you know now that it is in fact the excel file itself that is the problem then you have to fix that.

 

[water]

Quote

PS: Please, use the file I've uploaded just for your own. Don't publish it on any website or blog or anything else. That file contains important work references. Thank you.

Be careful with such files. The content of this forum gets indexed by Google etc. 

I would delete the file as soon as possible. 

[FrancescoDiMuro]

 Thank you water... I still can't read this file... I don't know why...

[water]

On the web you will find a few links describing problems with pivot tables e.g. https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2013/03/22/problem-reopening-excel-2013-pivottable-workbooks/