syntax error excel_read

[drkcld]

Hello guys! I actually write a program to read-out excel´s... but i get a syntax error for ?no? reason...
this is my code

Spoiler

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <GUIConstants.au3>
#include <EditConstants.au3>

Local $oExcel = _Excel_Open()
Local $xlspart = @ScriptDir & "\data.xls"
Local $xlspart = _Excel_BookOpen($oExcel, $xlspart, Default, Default, True)

$sp1 = "0"
$sp2 = "0"
$sp3 = "0"
$sp4 = "0"
$sp5 = "0"
$sp6 = "0"

$Gui1 = GUICreate("Exelread v1.0", 400, 400)
GUICtrlCreateTab(0, 0, 400, 400)
GUICtrlCreateTabItem("Spalte 1")
GUICtrlCreateLabel($sp1, 100, 100)
$r1 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 2")
GUICtrlCreateLabel($sp2, 100, 100)
$r2 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 3")
GUICtrlCreateLabel($sp3, 100, 100)
$r3 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 4")
GUICtrlCreateLabel($sp4, 100, 100)
$r4 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 5")
GUICtrlCreateLabel($sp5, 100, 100)
$r5 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 6")
GUICtrlCreateLabel($sp6, 100, 100)
$r6 = GUICtrlCreateButton("Read", 300, 300)
GUISetState(@SW_SHOW)

While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            Exit
        Case $msg = $r1
            Local $sp1 = _Excel_RangeRead($xlspart, 2, ("A2:A6"), 2))
        Case $msg = $r2
            Local $sp2 = _Excel_RangeRead($xlspart, 2, "B2:B6", 2))
        Case $msg = $r3
            Local $sp3 = _Excel_RangeRead($xlspart, 2, ("C2:C6"), 2))
        Case $msg = $r4
            Local $sp4 = _Excel_RangeRead($xlspart, 2, ("D2:D6"), 2))
        Case $msg = $r5
            Local $sp5 = _Excel_RangeRead($xlspart, 2, ("E2:E6"), 2))
        Case $msg = $r6
            Local $sp6 = _Excel_RangeRead($xlspart, 2, ("F2:F6"), 2))
    EndSelect
WEnd

 

Spoiler

>Running AU3Check (3.3.14.2)  from: \AutoIt3  input: excelread.au3
"C:\Users\"(45,60) : error: syntax error
            Local $sp1 = _Excel_RangeRead($xlspart, 2, ("A2:A6"), 2))
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\"(47,58) : error: syntax error
            Local $sp2 = _Excel_RangeRead($xlspart, 2, "B2:B6", 2))
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\"(49,60) : error: syntax error
            Local $sp3 = _Excel_RangeRead($xlspart, 2, ("C2:C6"), 2))
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\"(51,60) : error: syntax error
            Local $sp4 = _Excel_RangeRead($xlspart, 2, ("D2:D6"), 2))
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\"(53,60) : error: syntax error
            Local $sp5 = _Excel_RangeRead($xlspart, 2, ("E2:E6"), 2))
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\"(55,60) : error: syntax error
            Local $sp6 = _Excel_RangeRead($xlspart, 2, ("F2:F6"), 2))
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\.au3 - 6 error(s), 0 warning(s)
!>13:46:53 AU3Check ended. Press F4 to jump to next error.rc:2
+>13:46:53 AutoIt3Wrapper Finished.
>Exit code: 2    Time: 0.6696

 

Another question...

I wanna read out the first "filled" field in row "A" -> save in ini -> delete in xls

saving is not the problem. could you help me to get the first "filled" and delete it?

[BrewManNH]

The error message tells you where you went wrong, you have too many closing parentheses.

[drkcld]

ok i removed the syntax error. ")" at the end was the problem...

if i press the "read" button on tab1, the var $sp1 should change from predefined "0" to the value in the "A"column... but nothing happens... :/

[water]

Does it work when you try:

_Excel_RangeRead($xlspart, 2, "A2:A6", 2)

[BrewManNH]

You need to change the lines that are like this.

Local $sp2 = _Excel_RangeRead($xlspart, 2, "B2:B6", 2)

To something like this.

GUICtrlSetData($idLabel1, _Excel_RangeRead($xlspart, Default, ("a14"), 2))

Where $idLabel is the the variable you need to assign the control ID of the label on tab one to. The first line is only assigning the ARRAY returned from _Excel_RangeRead to the variable $sp2. You're going to need to reformat the GUICtrlSetData line I posted because you can't assign the array to the label directly. I only used it as an example. When using a range in the RangeRead function, it returns an array and not a simple variable.

[drkcld]

Local $sp1 = _Excel_RangeRead($xlspart, 2, "C6", 2) ; C6 contains numbers
Local $sp1 = _Excel_RangeRead($xlspart, 2, "A6", 2) ; A6 contains letters

Both of them no output...

 

[BrewManNH]

What is the contents of the variable $sp1 after running it? Where are you outputting the contents of $sp1 to determine whether or not it's getting anything returned? As I stated above, merely assigning a value to the variable doesn't change the contents of the label, the GUICtrlSetData does that.

[water]

As BrewManNH stated above: You need to use GUICtrlSetData to present the data in your GUI.
When you read a single cell you will get a string but if you read a range you will get an array.

[drkcld]

Sorry, its hard for me to understand all words in their meaning...

i tried to build in a msgbox for the output still no response here...

 

Spoiler

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <GUIConstants.au3>
#include <EditConstants.au3>
#include <MsgBoxConstants.au3>

Local $xlspart = @ScriptDir & "\data.xls"
Local $oExcel = _Excel_Open()
Local $test = _Excel_BookOpen($oExcel, $xlspart, Default, Default, True)

$sp1 = ""
$sp2 = ""
$sp3 = ""
$sp4 = ""
$sp5 = ""
$sp6 = ""

$Gui1 = GUICreate("Exelread v1.0", 400, 400)
GUICtrlCreateTab(0, 0, 400, 400)
GUICtrlCreateTabItem("Spalte 1")
GUICtrlCreateLabel($sp1, 100, 100)
$r1 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 2")
GUICtrlCreateLabel($sp2, 100, 100)
$r2 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 3")
GUICtrlCreateLabel($sp3, 100, 100)
$r3 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 4")
GUICtrlCreateLabel($sp4, 100, 100)
$r4 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 5")
GUICtrlCreateLabel($sp5, 100, 100)
$r5 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("Spalte 6")
GUICtrlCreateLabel($sp6, 100, 100)
$r6 = GUICtrlCreateButton("Read", 300, 300)
GUISetState(@SW_SHOW)


While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            Exit
        Case $msg = $r1
            GUICtrlSetData($sp1, _Excel_RangeRead($test, Default, "C1:C10", 1))
        Case $msg = $r2
            Local $aResult2 = _Excel_RangeRead($test, 2, "B2:B6", 1)
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel read", "Error reading from excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                MsgBox($MB_SYSTEMMODAL, "Excel read", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells B2:B6 of sheet 2.")
                _ArrayDisplay($aResult2, "Excel UDF: _Excel_RangeRead Example 2 - Cells B2:B6 of sheet 1")
        Case $msg = $r3
            Local $aResult3 = _Excel_RangeRead($xlspart, 2, "A2:A6", 1)
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel read", "Error reading from excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                MsgBox($MB_SYSTEMMODAL, "Excel read", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells B2:B6 of sheet 2.")
                _ArrayDisplay($aResult3, "Excel UDF: _Excel_RangeRead Example 2 - Cells B2:B6 of sheet 1")
        Case $msg = $r4
            Local $sp4 = _Excel_RangeRead($xlspart, 2, ("D2:D6"), 2)
        Case $msg = $r5
            Local $sp5 = _Excel_RangeRead($xlspart, 2, ("E2:E6"), 2)
        Case $msg = $r6
            Local $sp6 = _Excel_RangeRead($xlspart, 2, ("F2:F6"), 2)
    EndSelect
WEnd

 

as you can see, the only difference about $r2 and $r3 is, that i was not sure which to take here:

Local $xlspart = @ScriptDir & "\data.xls" ;;; $xlspart =dir
Local $oExcel = _Excel_Open() ;;;;;;; $oExcel -> Opens excel with no sheet?
Local $test = _Excel_BookOpen($oExcel, $xlspart, Default, Default, True) ;;;;;;;;;;;;$test = opened and active sheet?

did i understand it right?

 

[water]

This should give you an idea:

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <GUIConstants.au3>
#include <EditConstants.au3>
#include <MsgBoxConstants.au3>

Global $sCol1 = "", $sCol2 = "", $sCol3 = "", $sCol4 = "", $sCol5 = "", $sCol6 = "", $sTemp = "", $aTemp

Global $sWorkbook = @ScriptDir & "\test.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error opening Excel! @error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error opening Workbook! @error = " & @error & ", @extended = " & @extended)

GUICreate("Exelread v1.0", 400, 400)
GUICtrlCreateTab(0, 0, 400, 400)
GUICtrlCreateTabItem("Spalte 1")
$idLabel1 = GUICtrlCreateLabel($sCol1, 10, 30, 380, 20)
$idButton1 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTab(0, 0, 400, 400)
GUICtrlCreateTabItem("Spalte 2")
$idLabel2 = GUICtrlCreateLabel($sCol2, 10, 30, 380, 20)
$idButton2 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTab(0, 0, 400, 400)
GUICtrlCreateTabItem("Spalte 3")
$idLabel3 = GUICtrlCreateLabel($sCol3, 10, 30, 380, 20)
$idButton3 = GUICtrlCreateButton("Read", 300, 300)
GUICtrlCreateTabItem("")
GUISetState(@SW_SHOW)

While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            _Excel_BookClose($oWorkbook)
            _Excel_Close($oExcel)
            Exit
        Case $msg = $idButton1
            $sTemp = _Excel_RangeRead($oWorkbook, Default, "A1") ; Read a single cell
            If @error Then MsgBox($MB_ICONERROR, "Error", "Error reading data! @error = " & @error & ", @extended = " & @extended)
            GUICtrlSetData($idLabel1, $sTemp)
        Case $msg = $idButton2
            $aTemp = _Excel_RangeRead($oWorkbook, Default, "A1:C1") ; Read a row
            If @error Then MsgBox($MB_ICONERROR, "Error", "Error reading data! @error = " & @error & ", @extended = " & @extended)
            $sTemp = _ArrayToString($aTemp)
            GUICtrlSetData($idLabel2, $sTemp)
        Case $msg = $idButton3
            $aTemp = _Excel_RangeRead($oWorkbook, Default, "A1:A4") ; Read a column
            If @error Then MsgBox($MB_ICONERROR, "Error", "Error reading data! @error = " & @error & ", @extended = " & @extended)
            $sTemp = _ArrayToString($aTemp)
            GUICtrlSetData($idLabel3, $sTemp)
    EndSelect
WEnd

 

[water]

Wenn es Dir auf Deutsch leichter fällt, dann kannst Du ja im Deutschen Forum einen Thread öffnen.
Da schaue ich auch gelegentlich rein

[drkcld]

On 24.3.2016 at 4:43 PM, water said:

Wenn es Dir auf Deutsch leichter fällt, dann kannst Du ja im Deutschen Forum einen Thread öffnen.
Da schaue ich auch gelegentlich rein

Danke für die Hilfe, ich werde im deutschen Forum weiter machen