SorryButImaNewbie

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

9 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
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 (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 (edited)

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

Share this post


Link to post
Share on other sites

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

1 person likes this

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 (edited)

6 minutes ago, water said:

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

I'm just gonna sit in the corner for a few minutes, thanks

Edit: also SOLVED

Edited by SorryButImaNewbie

Share this post


Link to post
Share on other sites

:)


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 (edited)

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

Share this post


Link to post
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 (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

#9 ·  Posted (edited)

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

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

    • SorryButImaNewbie
      [SOLVED]_Excel_RangeWrite How to Bold?
      By SorryButImaNewbie
      Hello
      First, I thank you for all the help I received here with Excel UDFs and COM objects in the last few days/week.
      I would like to "pimp" my output excel a bit, and for that I would like to write some results Bold.
      How do I do that?
      I tried a few things but most return errors, or doesn't do a thing seemingly.
      My code right now, this does run, but doesn't make the inserted results bold. (by inserted I mean what I write with _Excel_RangeWrite)
      _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$DateArrayTimeIndex]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) $ExcelObject.Activesheet.Range(String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo).Bold = True Thank you for the help!
      Edit: I leave this here, added the bold tag, maybe someone find it usefull