FrancescoDiMuro

Some information about Excel UDF(s)(?)

16 posts in this topic

#1 ·  Posted

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  

Share this post


Link to post
Share on other sites



#2 ·  Posted

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

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 :D )

Share this post


Link to post
Share on other sites

#4 ·  Posted

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted

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

Share this post


Link to post
Share on other sites

#6 ·  Posted

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

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

Share this post


Link to post
Share on other sites

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! 

 

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

2 hours ago, l3ill said:

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

Post your code, explain whats not working.

Did you get Copy Rows working with waters instructions?

#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 " :o , 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 by FrancescoDiMuro

Share this post


Link to post
Share on other sites

#11 ·  Posted

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
#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

 

 

Share this post


Link to post
Share on other sites

#12 ·  Posted

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? 

Share this post


Link to post
Share on other sites

#14 ·  Posted

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. 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted

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

Share this post


Link to post
Share on other sites

#16 ·  Posted


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

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