Masum

Excel write in cell not replace

3 posts in this topic

Hi all,

Is there a way to write a string to a cell in Excel without replacing what may be already in that cell? Can this be achieved with a single function? Or will I need to read any potential data first, then join it, and then write to that cell? As the write function replaces what is already in the cell.

Thanks

Share this post


Link to post
Share on other sites



Correct:

Quote

Or will I need to read any potential data first, then join it, and then write to that cell?

 

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 (2016-07-20 - Version 1.2.1.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

Thanks for confirming.

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

    • kctvt
      How to know max number in Excel
      By kctvt
      Hi there, i'm looking for a script to take max number of a column in Excel.
       
      Ex :  Column C , i have : 
      12
      13
      22
      123
      154
      ....
      .....
      .....
      134534
      (About 134600 rows)

      So, How to know which is the max number in Column C.
      I have this code, but it take me a lot of time >"< 
      So... please help me a faster code.
       
      $x = 3 $CloseCheck1 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+1) $CloseCheck2 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+2) $CloseCheck3 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+3) $CloseCheck4 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+4) $CloseCheck5 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+5) $CloseCheck6 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+6) $CloseCheck7 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+7) $CloseCheck8 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+8) $CloseCheck9 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+9) $CloseCheck10 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+10) Local $aArray = StringSplit($CloseCheck1&","&$CloseCheck2&","&$CloseCheck3&","&$CloseCheck4&","&$CloseCheck5&","&$CloseCheck6&","&$CloseCheck7&","&$CloseCheck8&","&$CloseCheck9&","&$CloseCheck10,",") $DMAX = _ArrayMax($aArray, 1, 1) $DMIN = _ArrayMin($aArray, 1, 1) $n = 11 While 1 $CloseCheckn = _Excel_RangeRead($oWorkbook, Default, "C"&$x+n) If $CloseCheckn > $DMAX Then Global $DMAX = $CloseCheckn EndIf If $CloseCheckn < $DMIN Then Global $MIN = $CloseCheckn EndIf If $CloseCheckn = "" Then ExitLoop EndIf $n = $n + 1 WEnd _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMAX, "P1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMIN, "P2")  
       
      Thanks  
       
       
       
       
    • snaileater
      _Excel_RangeInsert error ... ?
      By snaileater
      I'm trying the Excel.udf, my starting point are the examples found in the help.
      My problem is that i can't find any working example of _Excel_RangeInsert ... made many tries but i can't find the reason why ...
      New/existing Worbook nothing changes ... i tried _Excel_RangeWrite without any problem, but with _Excel_RangeInsert i always get an @error=3 and @extended=-2147352562 ...
      What could i be missing ?
      Here's the minimalistic snippet i'm playing with :
      Local $sWorkbook = @ScriptDir & "\pixel.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $dummy=_Excel_RangeInsert($oWorkbook.Activesheet, "1:3") If @error Then    $a=@extended    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error inserting" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Else    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Rows successfully inserted") EndIf ConsoleWrite ($dummy & chr(13) & $a & Chr(13)) I tried every possible (...) syntax for the range object, without success ... 
      I use the latest releases of AutoIt and Office 97 ...
      Thanks for your help ...
    • PINTO1927
      Excel write background
      By PINTO1927
      Hello guys,
      through this example that you find at the end I can not get to write a text in cell A1 example, without being open excel.
      #include <Excel.au3> $oExcel = _Excel_Open(False) $sWorkbook = @DesktopDir & "\test\test.xlsx" $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")  
      while if I make "_Excel_Open(True)" visible, the text is written.
    • SorryButImaNewbie
      AutoIt script turns off, because of array is out of bound
      By SorryButImaNewbie
      Hello all!
      I wrote a little script to automate some of my work (for which I'm greatfull).
      I have little problem now. My script get data of currency values from specified time period. The problem, if XY day is a holiday or something, then the bank doesn't have new values for that day, thus my script can ask for "till that date" if there is only data for "till that date-1".
      I should be done with this for tomorrow and I should learn for my test paper tomorrow, make a genetic algorhytm homework, and at least look at a ppt...
      So here is my code, pleas ask if you need more information!
      InternetRead2() ;Creating Date and Arfolyam arrays, using DateInterval read from above (_ArraysDisplayed and function is "tested") ;Calculate and write HUF*OSSZEG Do Local $SZAMLATELJOlvaso = _Excel_RangeRead($ExcelObject, Default, $SZAMLATELJoszlop & $CellaOlvasoSzamlalo) Local $Time = StringTrimRight($SZAMLATELJOlvaso, 6) $DateArrayTimeIndex = _ArraySearch($DateArray, $Time, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-1, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-2, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-3, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-4, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf $DateArrayTimeIndex = $DateArrayTimeIndex+1 ;Egyébként arraysearch -1et ad vissza, 0nál. Passz Sleep(200) If Not $SZAMLATELJOlvaso = "" Then _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$DateArrayTimeIndex]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) If @error Then _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$oXML_Node.Length]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) MsgBox(64, "Értesítés", "Valamilyen okból, nem találok az adott dátumhoz árfolyamot, a keresési idők közül a legutolsó árfolyammal töltöttem ki!" & @CRLF & Number($oXML_Node_Datum)) EndIf EndIf $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1 Until $SZAMLATELJOlvaso = "" also my internetread2 function :
      Func InternetRead2() ;ezt használja az excelmove ;----------------------------------- ;Original Source: https://www.autoitscript.com/forum/topic/184884-solved-basic-com-help-working-on-string-returned-from-api-mi-doing-this-okey/ ;by Genius257 ;TO DO: Some error handling maybe: what if there is no $MinTime/$MaxTime? ;Read the returns in to array for future use --> Right now, 2 arrays one with the dates and one with the exchangerates ;----------------------------------- ;Globals decleared to avoid warnings Global $MinTime ;20160601000000 Global $MaxTime ;20160610000000 Global $MinTimeFormated = StringTrimRight($MinTime, 6) Global $MaxTimeFormated = StringTrimRight($MaxTime, 6) $oHTTP = ObjCreate("WinHttp.WinHttpRequest.5.1") If $MaxTimeFormated = "" and $MinTimeFormated = "" Then MsgBox(64, "Értesítés", "Nincsenek beolvasott dátumok a memóriában, dummy dátumokat adok meg!" & @CRLF & "20160901, 20160910") $MinTimeFormated = "20160901" $MaxTimeFormated = "20160905" EndIf $oHTTP.Open("GET", "http://api.napiarfolyam.hu/?bank=mnb&valuta=eur&datum="&$MinTimeFormated&"&datumend="&$MaxTimeFormated&"", False) $oHTTP.Send() $sXML = $oHTTP.responseText Global $oXML = ObjCreate("Microsoft.XMLDOM") $oXML.loadXML( $sXML ) $oXML_Nodes = $oXML.SelectNodes("./arfolyamok/deviza/item") Global $ArfolyamArray[$oXML_Nodes.Length] ;MsgBox(64, "Title", "" & $oXML_Nodes.Length & "") ;_ArrayDisplay($ArfolyamArray) Global $DateArray[$oXML_Nodes.Length] For $i=0 To $oXML_Nodes.Length-1 Global $oXML_Node = $oXML_Nodes.Item($i) $oXML_Node_Bank = $oXML_Node.SelectNodes("./bank") $oXML_Node_Bank = $oXML_Node_Bank.Length>0?$oXML_Node_Bank.Item(0).text:"" Global $oXML_Node_Datum = $oXML_Node.SelectNodes("./datum") $oXML_Node_Datum = $oXML_Node_Datum.Length>0?$oXML_Node_Datum.Item(0).text:"" $oXML_Node_Penznem = $oXML_Node.SelectNodes("./penznem") $oXML_Node_Penznem = $oXML_Node_Penznem.Length>0?$oXML_Node_Penznem.Item(0).text:"" $oXML_Node_Kozeps = $oXML_Node.SelectNodes("./kozep") $oXML_Node_Kozep01 = $oXML_Node_Kozeps.Length>0?$oXML_Node_Kozeps.Item(0).text:"" $oXML_Node_Kozep02 = $oXML_Node_Kozeps.Length>1?$oXML_Node_Kozeps.Item(1).text:"" $ArfolyamArray[$i] = $oXML_Node_Kozep01 $DateArray[$i] = StringTrimRight(StringReplace($oXML_Node_Datum, "-", ""), 8) ConsoleWrite( "Match [" & StringFormat("%02i", $i+1) & "]:"&@CRLF& _ @TAB&"Bank: "&@TAB&$oXML_Node_Bank&@CRLF& _ @TAB&"Datum: "&@TAB&$oXML_Node_Datum&@CRLF& _ @TAB&"Penznem: "&@TAB&$oXML_Node_Penznem&@CRLF& _ @TAB&"Kozep01: "&@TAB&$oXML_Node_Kozep01&@CRLF& _ @TAB&"Kozep02: "&@TAB&$oXML_Node_Kozep02&@CRLF _ ) Next ;_ArrayDisplay($ArfolyamArray) ;_ArrayDisplay($DateArray) EndFunc My problem is that I have dates in my excel till 2016.10.30, but I only have dates till 28.
      (see the api, according to aoutit console, I have the same data: http://api.napiarfolyam.hu/?bank=mnb&valuta=eur&datum=20161001&datumend=20161030 )
      My idea/goal (with the If @error then... after @SZAMLATELJolvaso part) is that if this happens, I want it to automaticly jump to the last date of the array (I will be able to write something more elaborate, but for now thats my goal, to save the sinking ship )
      Thank yu for your help and insight!
    • l3ill
      _Excel_RangeFind - requested action with this object has failed
      By l3ill
      Hi,
      anybody know what this is about?
      I have tried on three different PC's all give me the same error whether my own code or the Help File Examples...
      All other excel func's work fine. ( haven't tested them all but most)
      >Running:(3.3.14.2):C:\Users\XXXXX\Desktop\autoit-v3\install\autoit3.exe "C:\Users\XXXXX\Desktop\autoit-v3\install\Examples\Helpfile\_Excel_RangeFind[2].au3" --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop "C:\Users\XXXXX\Desktop\autoit-v3\install\Include\Excel.au3" (656) : ==> The requested action with this object has failed.: $aResult[$iIndex][1] = $oMatch.Name.Name $aResult[$iIndex][1] = $oMatch^ ERROR No script error to speak of just this Include error.
      Any help is greatly appreciated.
      Bill