Jump to content

[SOLVED] Excel_RangeWrite, no error and MsgBox returns string, yet nothing happens


Recommended Posts

Hello dear forum user!

I try to make an algorithm that reads values from 2 predefined columns until it founds 2 empty cells in that column, if that happens, It should write a SUM function in to the cell after the last read value. It should do this until it founds 3 empty cells. (I order my excel before this part, so these criteriums should work, and they do while I'm testing).

Now, I just found a little logical bump on the $SUMsor variable, but I corrected it. Since MsgBox shows me that the readings are good, and the string it should write are also good, and no error flags are detected, I'm not sure what to do next.

I'm pretty sure it is attached to the right excel, since every other things works, and it also reads in from the excel It should.

Here is my code snippet:

;CONSTRUCTION-----------------------------------
;TO DO:
;Range insert to SULY and HUF, put it in Bold

$CellaOlvasoSzamlalo = $CellNumb + 1
Do
    Local $SUMSor = $CellNumb + 1 ;will be used to determine the "top of the area which it should SUM, CellNumb is the row where the headers/titels were found
    Local $SULYolvaso1 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo) ;Read
    MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso1:" & $SULYolvaso1 & "") ;Check what is read
    Local $SULYolvaso2 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo + 1)
    MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso2:" & $SULYolvaso2 & "")
    Local $SULYolvaso3 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo + 2)
    MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso3:" & $SULYolvaso3 & "")
        Select
            Case $SULYolvaso2 = "" and $SULYolvaso3 = ""
                _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")")
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                MsgBox($MB_SYSTEMMODAL, "Értesítés", "Suly oszlop: " & "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")")
                _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $sHUFBeszurOszlop & $SUMSor & ":" & $sHUFBeszurOszlop & $CellaOlvasoSzamlalo & ")")
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                MsgBox($MB_SYSTEMMODAL, "Értesítés", "Suly oszlop: " & "=SUM(" & $sHUFBeszurOszlop & $SUMSor & ":" & $sHUFBeszurOszlop & $CellaOlvasoSzamlalo & ")")
                $SUMSor = $CellaOlvasoSzamlalo + 3
        EndSelect
    $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1
Until $SULYolvaso1 = "" and $SULYolvaso2 = "" and $SULYolvaso3 = ""
;$ExcelObject.Activesheet.Range(String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo).Font.Bold = True
;-----------------------------------------------

Also I would like to write it in bold, I have en example commented out at the end, but is there a way with range insert to insert in Bold?

Thank you for your help and insight! and have a nice day :)

Edited by SorryButImaNewbie
Link to comment
Share on other sites

You need to format a cell in bold in two steps: Write the value into the cell and then format the cell as you do in your commented line.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

11 hours ago, water said:

You need to format a cell in bold in two steps: Write the value into the cell and then format the cell as you do in your commented line.

Ahhh, thank you master water, I will format the cells separetly. (I tired to format it while it write the string in to the cell.)

Any idea about why Range Write doesn't work? Is there a COM like way to write in to cells just as I can format it to Bold? (actually this just popped to my mind, I will try to find a way to do it)

 

Edit: _ExcelWriteCell is just the old _Excel_RangeWrite right? (looks like it)

Edited by SorryButImaNewbie
Edit: additional question, not to spam the forum
Link to comment
Share on other sites

You do not specify parameter 4 so everthing gets written to the default cell - which is A1.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Sorry for posting here again, but I seems to have a little bit of problem with adding strings.

_Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")", "" & String($SULYoszlop) & String($SULYolvaso2) & "")
                Local $TestCell =  ("" & String($SULYoszlop) & String($SULYolvaso2) & "")
                If IsString($TestCell) Then
                    MsgBox($MB_SYSTEMMODAL, "", "The variable is a string")
                Else
                    MsgBox($MB_SYSTEMMODAL, "", "The variable is not a string")
                EndIf
                MsgBox(64, "Teszt", $TestCell)
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

I try desperatly to merge these two but I only get the first variable in to the $TestCell, I started from $SULYoszlop & SULYolvaso2, and "evolved" it to what you can see above, all the time the IsString, tells me its a string, but its value is only J , the column value, in other example in my code, it works just fine, like here:

_Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "HUF", String($sHUFBeszurOszlop) & $CellNumb)

what do I miss?

Edited by SorryButImaNewbie
Link to comment
Share on other sites

You use brackets where I wouldn't.

Example: I would use

Local $TestCell = String($SULYoszlop) & String($SULYolvaso2)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

10 minutes ago, water said:

You use brackets where I wouldn't.

Example: I would use

Local $TestCell = String($SULYoszlop) & String($SULYolvaso2)

 

I tried this as well, same result, only returns: J as $TestCell string

Edit: so far I tried every permutation from $SULYoszlop & $SULYolvaso2 to the one over complicated one seen above

Edit2: String(String($SULYoszlop) & "" & String($SULYolvaso2)) is returns J as well... gonna run out of ideas :D

Edit3: OMG I think I found it!

Edit4: for edit3 clarification

Okey, so for start I made a logical mistake, 

Case $SULYolvaso2 = ""

so, using the $SULYolvaso to decide the number parameter for the cell is kinda stupid from the start. Now, I used 

String($CellaOlvasoSzamlalo + 1) seems to work...

(Computer works according to your instructions, not according to your wishes :) )

 

Edited by SorryButImaNewbie
Link to comment
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
 Share

×
×
  • Create New...