Jump to content
Sign in to follow this  
snaileater

_Excel_RangeInsert error ... ?

Recommended Posts

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

Share this post


Link to post
Share on other sites

Works fine here with AutoIt 3.3.12.0 and Excel 2010.

the value of @extended stands for: 0x8002000E - DISP_E_BADPARAMCOUNT - invalid number of parameters.

As the Excel UDF has been tested with Excel 2003 and later I'm sure Excel 97 does not support a parameter passed by the UDF.
I fear you need to switch to a newer version of Excel or use the old Excel UDF that comes with AutoIt 3.3.8.1


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - 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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
       
       
       
      ExcelPivot.au3
    • By siawpo
      Hi,
      I'd like to change different colors for different portion of text in same cell of Excel application.
      Neither character length nor cell might not fixed.
      Here's the code I've tried to put together but not manage to pull it off.
      I'm appreciate it for any suggestion, thank you.
      $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")  
    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
×
×
  • Create New...