Jump to content
broliukaz

_ExcelReadCell() - undefined function ?

Recommended Posts

broliukaz

Hi,

Before I created my script with Windows 7 pro 32bit computer with Office 2013 32bit, and it's working on all my computers with Win7, Win8, Win10 with office 32 and 64bit...
Now I want to edit it on Windows 10 pro 64bit Computer with Office 2016 64bit and I get error: _ExcelReadCell() - undefined function. Can this computer or office version cause the problem? Excel.au3 is included.

Here is the part of my script:

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $xlsRepairs&$sItem)
$CVDevice = _ExcelReadCell($oExcel, 1, 2) ; Device name
$CVeqrma = _ExcelReadCell($oExcel, 3, 3) ; EQRMA Number 
$CVlot = _ExcelReadCell($oExcel, 5, 3) ; Lot number
$CVpn = _ExcelReadCell($oExcel, 8, 3) ; PN
$CVsn = _ExcelReadCell($oExcel, 11, 3) ; SN
$CVreceived = _ExcelReadCell($oExcel, 17, 3) ; received

$CVDevIdent = _ExcelReadCell($oExcel, 1, 2) ; Device identification
$CVProjIdent = ($oExcel, 2, 2) ; Project identification
                _Excel_Close($oExcel)

 

Share this post


Link to post
Share on other sites
BrewManNH

The Excel UDF has been updated and the function names have changed or been replaced.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
broliukaz

Thanks BrewManNH, where I can find the information what is changed and replaced?

Share this post


Link to post
Share on other sites
water

Help file - History - Script Breaking Changes


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - 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

  • Similar Content

    • MrCheese
      By MrCheese
      Hi guys,
      without including everything (unless you want it)
      I am copying data from a table in chrome and wanting to paste it into excel.
      Copying in Chrome works.
      I can paste it into the field i want by emulating goto -> ctrl V:
      WinActivate($dataload) WinWaitActive($dataload) Sleep(500) $oWorkbook1.Sheets("ItemReturn").Activate Sleep(500) $msg = "Measuring Sheet" conwrite() ttips2() Local Const $xlUp = -4162 With $oWorkbook1.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, "B")).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number EndWith $NewStartCell = $iLastCell + 2 $msg = "moving to location" conwrite() ttips2() Sleep(250) Send("^g") WinWait("Go To") Sleep(100) Send("B" & $NewStartCell) Sleep(100) Send("{ENTER}") Sleep(500) Send("^v")  
      But, I want to use _excel_rangecopypaste, pasting from the clipboard
      _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, default, "B" & $NewStartCell,default,$xlPasteValuesAndNumberFormats) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error pasting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) however, this gives me error 4 , extended@:  -2147352567
      How can i fix this or find out how to debug this error?
       
      Thanks
    • Simpel
      By Simpel
      Hi.
      I try to figure out who is using a excel workbook which I can only open "read only". I use this code:
      #include <Array.au3> #include <Excel.au3> Local $sFile = ; excel file with path on a network drive Local $oExcel = _Excel_Open(True, True) Local $oTabelle = _Excel_BookOpen($oExcel, $sFile) Local $aUsers If IsObj($oTabelle) Then $aUsers = $oTabelle.UserStatus _ArrayDisplay($aUsers) EndIf If I am the one allowed to write to the excel file (I'm the first one who opened it) then I will get an array with myself:

      If my collegue opened the excel file first and I run the code I get the following error message:
      "H:\_Conrad lokal\Downloads\AutoIt3\_COX\Tests\test.au3" (9) : ==> The requested action with this object has failed.: $aUsers = $oTabelle.UserStatus $aUsers = $oTabelle^ ERROR The excel file is on a network drive. Is that's the problem?
      Regards, Conrad
    • robertocm
      By robertocm
      change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip:
      #include <File.au3> ;Change this Local $sFind = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png" Local $sReplace = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png" Local Const $sMessage = "Directory to change excel image paths" Local $sFileSelectFolder = FileSelectFolder($sMessage, "") Local $sTempDir = @ScriptDir & "\testdir" ;Required 7-zip Local $PathZipProgram = @ProgramFilesDir & "\7-Zip\" If Not(FileExists($PathZipProgram & "\7z.exe")) Then MsgBox(16, "", "7z.exe not found in path " & $PathZipProgram) Exit EndIf ;look for excel files in selected directory and all subdirectories Local $SFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls.;*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then For $i = 1 To $SFileList[0] DirRemove($sTempDir, 1) ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file RunWait('"' & $PathZipProgram & '7z.exe" x -aoa -r "' & $SFileList[$i] & '" -o"' & $sTempDir & '" -y', $PathZipProgram, @SW_HIDE) __ReplaceImagePaths($sTempDir, $sFind, $sReplace) RunWait('"' & $PathZipProgram & '7z.exe" a -r "' & $SFileList[$i] & '" "' & $sTempDir & '\*" -tzip -y', $PathZipProgram, @SW_HIDE) Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf DirRemove($sTempDir, 1) Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace) ;List all files with .xml.rels extension in the directory \xl\drawings\_rels Local $aFileList = _FileListToArray($sTempDir & "\xl\drawings\_rels", "*.xml.rels", 1, True) If @error = 1 Then ;MsgBox (0, "", "Path was invalid") SplashTextOn("Title", "Path was invalid", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf If @error = 4 Then ;MsgBox (0, "No files", "No files were found") SplashTextOn("Title", "No files were found", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf Local $iRetval ;Loop through the array For $i = 1 To $aFileList[0] $iRetval = _ReplaceStringInFile($aFileList[$i], $sFind, $sReplace) Next EndFunc  
      Some references:
      https://stackoverflow.com/questions/37145369/change-path-to-picture-links-in-excel http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp EDITED:
      Note: it seems that if User Account Control (UAC) is enabled then 7zip is unable to overwrite the destination file (using the same name).
      In this case, a possible solution would be to rename the original excel file before (see _PathSplit in help file).
      In my case i prefer just to disable UAC
    • SOF-TECH
      By SOF-TECH
      Dear all,
      Can someone show  me how to en hance the below function to write in CSV  into column  and rows the input values ? 
      I am getting this result: 

      I would like the result to be as this 

      From A1:C1 is for headers
      From A2:C2 is for input Data
      Global Const $GUI_EVENT_CLOSE = -3 $sDataFilePath = @ScriptDir & "\Records.csv" #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Demo1: New Record", 580, 115) $Input1 = GUICtrlCreateInput("", 10, 30, 270, 21) $Input2 = GUICtrlCreateInput("", 300, 30, 270, 21) $Input3 = GUICtrlCreateInput("", 10, 80, 270, 21) $Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17) $Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17) $Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17) $Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _ExportData() MsgBox(64, @ScriptName, "Record Saved.") EndSwitch WEnd Func _ExportData() If Not FileExists($sDataFilePath) Then FileWriteLine($sDataFilePath, "Name;ID;Phone No.;") EndIf For $i = $Input1 To $Input3 FileWrite($sDataFilePath, GUICtrlRead($i) & ";") Next FileWriteLine($sDataFilePath, "") EndFunc ;==>_ExportData May be Excel UDF has be to be added but I can manage that my self  
      Thank you in advance
    • MrCheese
      By MrCheese
      HI there
      this is driving me nuts - i get the row count, but not the column count - what am I missing? Thanks for your help!
      #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ; XlDirection enumeration: https://msdn.microsoft.com/en-us/library/office/ff820880.aspxGlobal $oExcel = _Excel_Open() Global $xlup = -4162 Global $xlByRows, $xlPrevious, $xlByColumns Global $oExcel = _Excel_Open() $bookname = "temp.xlsx" $sWorkbook = @ScriptDir & "\" & $bookname Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True) With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iColCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Column.Count MsgBox(0, "", "row:" & $iRowCount & "Col:" & $iColCount) EndWith  
      for context - i want to :
      * count columns used in excel
      * create ini file from the rows in each column - finishing at the last column used - i.e. one column for one ini file; containing 15 rows or so.
      is it better to read the entire sheet to an array via the sheettoarray function? then read it from that?
       
       
      temp.xlsx
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.