Jump to content
Skeletor

FileReadLine to Array to Excel

Recommended Posts

Skeletor

Hi All,

I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?

One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.

So its imperative that the $value1, $value2, etc variables be used. 

Code below:

$FileRead1 = FileReadLine("C:\temp\sample.txt",1)


For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1")

Next

 

Edited by Skeletor

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
What about _FileReadToArray() and _Excel_RangeWrite()? :)

#include <Array.au3>
#include <Excel.au3>
#include <ExcelConstants.au3>
#include <File.au3>

Global $strFilePath = @ScriptDir & "\SampleFile.txt", _
       $arrFileContent, _
       $objExcel, _
       $objWorkbook, _
       $strExcelFile = @ScriptDir & "\SampleWorkbook.xls"


_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
If @error Then
    ConsoleWrite("Error with _FileReadToArray(). Error: " & @error & @CRLF)
Else
    ; _ArrayDisplay($arrFileContent)
    $objExcel = _Excel_Open(False)
    If @error Then
        ConsoleWrite("Error while creating Excel object. Error: " & @error & @CRLF)
    Else
        $objWorkbook = _Excel_BookNew($objExcel)
        If @error Then
            ConsoleWrite("Error while creating the Workbook. Error: " & @error & @CRLF)
        Else
            _Excel_RangeWrite($objWorkbook, $objWorkbook.ActiveSheet, $arrFileContent)
            If @error Then
                ConsoleWrite("Error while writing the content of $arrFileContent in the Workbook. Error: " & @error & @CRLF)
            Else
                If FileExists($strExcelFile) Then FileDelete($strExcelFile)
                _Excel_BookSaveAs($objWorkbook, $strExcelFile, $xlExcel8)
                If @error Then
                    ConsoleWrite("Error while saving the Workbook. Error: " & @error & @CRLF)
                Else
                    ConsoleWrite("The content of the $arrFileContent array has been written in the Excel file." & @CRLF)
                    _Excel_BookClose($objWorkbook)
                    _Excel_Close($objExcel)
                EndIf
            EndIf
        EndIf
    EndIf
EndIf

Cheers :)

SampleFile.txt

Edited by FrancescoDiMuro
  • Like 1

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Skeletor

@FrancescoDiMuro 

Cool, but that's a straight dump from text file to Excel. 
I wanted to pick out only a few values from the text file and insert them into the Excel sheet... 

So, FileReadLine does this prefectly, reads one line at a time from the text file and inserts it into the Excel SpreadSheet.. but, it takes a long time. 
 

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
You can always modify your array as much as you want.
Keep in mind that with _FileReadToArray() you fill your array with the content of the file; and then, you can do whatever you want, since you are working with the array...
You can create another array which contains only what you want ( or use directly the rows/columns you want to store in the second file ).

So, it's your choice :)
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Skeletor

This is where I am confused.. I'm trying to figure out this whole array saga... 

I'm not much of a fan about these.. that's why I use FileReadLines and stuff like that... 
so if I use:
 

$Array = ($value1, $value2, $value3, $value4)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

Will that work?
Especially if it can trickle down the rows.. like A1, A2, A3, etc....?

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
It is what _Excel_RangeWrite() does...
But all depends on how your array is composed...
If you have a 1-Dimensional array, using _Excel_RangeWrite() you are going to set all the values in the Workbook starting from "A1" cell ( leaving the Range parameter default ); so, in this case, if you have a 1-Dimensional array and you would like to set values in B, C, D... columns, you have to format you array in order to have a 2-Dimensional array ( rows and columns ).
So, the line 

5 minutes ago, Skeletor said:

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

it's correct, but you need to know how your $Array is formatted :)
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Skeletor

Thanks FrancescoDiMuro, so I need to just work on my array... so do I get rid of the For..Loop then? 
Or will this loop through the file, read each line, then the StringSplit break it up like how I want and then spit it out like so....?

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
Next

$Array = ($value1, $value2, $value3, $value4)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

Is this correct? Or how would I format my Array... ?

Share this post


Link to post
Share on other sites
FrancescoDiMuro
24 minutes ago, Skeletor said:

$Array = ($value1, $value2, $value3, $value4)

This is not correct.
To set correctly your values in your array, you should do something like this:

#include <Array.au3>

Global $Array[1][4]

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)    
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
    
    _ArrayAdd($Array, $value1 & "|" & $value2 & "|" & $value3 & "|" & $value4 & @CRLF)
Next

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

But I am going to tell you again that you are doing unecessary steps, which will take additional time to do what they do :)

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Subz

As per FrancescoDiMuro _FileReadToArray example, just add the following afterwards:

This just deletes all the columns except for Column 0 to 3, then just use _Excel_RangeWrite to write it directly into Excel.

_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")

For $i = UBound($arrFileContent) - 1 To 4 Step - 1
    _ArrayColDelete($arrFileContent, $i)
Next

 

  • Like 1

Share this post


Link to post
Share on other sites
Skeletor

Thanks Subz, however I'm selecting only a few values form the files.. 

Example: 
 

aaa,sss,ddd,fff,ggg,hhh

When I stringSplit I take get this:
 

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
Next

I then want to take $value1 and $value3 out from the file and insert it into Excel... 
 

Share this post


Link to post
Share on other sites
Skeletor

Using the FileArray will only dump the entire file as is into Excel... Yes splits it into columns, but thats not what I want.. i want to use select my data...

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor

6 minutes ago, Skeletor said:

I then want to take $value1 and $value3 out from the file and insert it into Excel... 

#include <Array.au3>

Global $Array[1][2]

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)    
    $value1 = $input[1]
    $value3 = $input[3]
    
    _ArrayAdd($Array, $value1 & "|" & $value3 & @CRLF)
Next

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

If you don't want to use all the samples we already gave to you.

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Subz

If you only wanted Input1 and Input3 then just delete the second column as well, or maybe I'm missing something?

_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
For $i = UBound($arrFileContent) - 1 To 3 Step - 1
    _ArrayColDelete($arrFileContent, $i)
Next
_ArrayColDelete($arrFileContent, 1)
_ArrayDisplay($arrFileContent)

 

Share this post


Link to post
Share on other sites
Skeletor

Thanks Subz, but my examples are only a tiny bit of the large code I have.. 

So, basically, the $value1 and $value3 will not go in order... 

Let's say I have this:

$FileRead1 = FileReadLine("C:\temp\sample.txt",1)
Local $countlines = _FileCountLines($FileRead1)
Global $Array[$countlines]
For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
    $value5 = $input[5]
    $value6 = $input[6]
    $value7 = $input[7]
    $value8 = $input[8]
    $value9 = $input[9]
    _ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3, 1, "|")
Next

_ArrayDisplay($Array)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A2")

Now, I turned the Array into a 1D.. I get results but each one of the $values are in rows, and not like how I specified them... 
If in 2D I get no results.

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
This

_ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3, 1, "|")

Should be

_ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3 & @CRLF, 1, "|")

 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
caramen

Teacher @FrancescoDiMuro deserve the mvp statut xD


My video tutorials : HERE ( In construction ) 

How to Ask Help ||  UIAutomation From Junkew || WebDriver From Danp2 || And Water's UDFs in the Quote

Spoiler

 Water's UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Subz

Two other methods which would be much faster than FileReadLine

#include <Array.au3>
#include <Excel.au3>
#include <File.au3>

Global $g_sFilePath = @ScriptDir & "\SampleFile.txt"
Global $g_aFileData
_FileReadToArray($g_sFilePath, $g_aFileData, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
    If @error Then Exit

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 8, 8), "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 1, 1), "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 4, 4), "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 6, 6), "E1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 4, 4), "F1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 2, 2), "G1")

Second example:

#include <Array.au3>
#include <ArrayWorkshop.au3> ;~ https://www.autoitscript.com/forum/topic/180467-arrayworkshop/
#include <Excel.au3>
#include <File.au3>

Global $g_sFilePath = @ScriptDir & "\SampleFile.txt"
Global $g_aFileData
_FileReadToArray($g_sFilePath, $g_aFileData, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")

;~ Create the Order of Columns to write into Excel
;~ Note Columns are 0 index based.
Global $g_aColumnOrder[] = [8, 1, "", 4, 6, 4, 3, ""]
Global $g_aColumnData = _FileExtract($g_aFileData, $g_aColumnOrder)
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $g_aColumnData)

Func _FileExtract($_aFileData, $_aColumnOrder)
    If $_aColumnOrder[0] = "" Then
        Local $aColumnData[UBound($_aFileData)][1]
    Else
        Local $aColumnData = _ArrayExtract($_aFileData, -1, -1, $_aColumnOrder[0], $_aColumnOrder[0])
    EndIf
    For $i = 1 To UBound($_aColumnOrder) - 1
        If $_aColumnOrder[$i] = "" Then
            _ArrayColInsert($aColumnData, $i)
        Else
            _ArrayAttach($aColumnData, _ArrayExtract($_aFileData, -1, -1, $_aColumnOrder[$i], $_aColumnOrder[$i]), 2)
        EndIf
    Next
    Return $aColumnData
EndFunc

 

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

    • SlackerAl
      By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
    • Morphice
      By Morphice
      Hello , 
      I am new to autoIT, I am wondering if someone could guide me in the correct path for this program. Attached are the steps for the program as well as what I currently have. Any help is greatly appreciated . Thank You. 
      * workbooks\sheets will be organized on per level basis , 1st sheet lvl 1 ,2nd sheet lvl 2 etc or workbook 1 = level 1, workbook 2 = level 2 etc. 
      ; = comments and reminders 
      #include <MsgBoxConstants.au3> #include <EditConstants.au3> #include<excel.au3> #include<Array.au3> Global Const $PatientLookupX = 320 Global Const $PatientLookupY = 64 ; down 1 and enter Global Const $PatientTextBoxX = 410 Global Const $PatientTextBoxY = 217 ; click , Ctrl + V , Enter Global Const $PHMhubX = 512 Global Const $PHMhubY = 613 ;click down 1 enter button Global Const $HealthRiskAssesmetX = 40 Global Const $HealthRiskAssesmetY = 162 Global Const $AddnewAssesmentX = 168 Global Const $AddnewAssesmentY = 98 Global Const $SelectAssesmentX = 342 Global Const $SelectAssesmentY = 98 ; Down 7 and enter Risk score new Global Const $EmptyAnswerBarX = 465 Global Const $EmptyAnswerBarY = 145 Global Const $LowriskpreventionX = 716 Global Const $LowriskpreventionY = 324 Global Const $MediumriskPreventionX = 716 Global Const $MediumriskPreventionY = 352 Global Const $HighriskPreventionX = 716 Global Const $HighriskPreventionY = 377 Global Const $CatatrosphicPreventionX = 714 Global Const $CatatrosphicPreventionY = 399 Global Const $ClosebuttonX = 1167 Global Const $ClosebuttonY = 666 Global Const $SaveRiskButtonX = 1161 Global Const $SaveRiskButtonY = 692 Global Const $ExitCPScreenX = 1339 Global Const $ExitCPScreenY = 8 Global Const $ExitpatientHubX = 1000 Global Const $ExitpatientHubY = 79 Global Const $sleepMod = 2 Global Const $sleepVal = 5000*$sleepMod Global Const $sleepLow = 200*$sleepMod Global Const $sleepMed = 1000*$sleepMod Global Const $sleepHigh = 3500*$sleepMod ;Function Open excel , read account number in column A ;------------------------------------------------------------------------------------------------------------------------------------------------------- HotKeySet("{ESC}","stopbaby") Func _WinWaitActivate($title,$text,$timeout=0) $hWnd = WinWait($title,$text,$timeout) If Not WinActive($title,$text) Then WinActivate($title,$text) WinWaitActive($title,$text,$timeout) EndFunc $i=0 While $i <=2 $i = $i+1 Local $Open_excel = _Excel_Open() Local $File_path = "D:\AutoIT\Risk_Test.xlsx" Local $Open_workbook = _Excel_BookOpen($Open_excel,$File_path) WinActivate($Open_workbook) Local $Read_account_number = _Excel_RangeRead($Open_workbook,default,"A" &$i) _Excel_Close($Open_excel,False) WEnd ;--------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoSearch() WinActivate(eClinicalWorks (Garcia,Erick) Sleep($sleepMed) MouseClick("",693,77) Send("!p") ; shortcut for patient menu Send("{DOWN}") ; down 1 send ("{Enter}") ; patient lookup Sleep($sleepMed) ;paste account number How would I do this??? Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub Sleep($sleepMed) Next NavtoPHMHub() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoPHMHub() MouseClick("",$PHMhubX,$PHMhubY) Sleep($sleepLow) Send("{DOWN}") Send("{ENTER}") ;takes you to Care Plan HUB Next NavtoRiskScore() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoRiskScore() MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment Sleep($sleepLow) MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment Sleep($sleepLow) MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab Sleep($sleepLow) Send("{DOWN 7}") Send("{ENTER}") Sleep($sleepLow) MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar Sleep($sleepLow) MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6 Next NavtoNextPatient() Func NavtoNextPatient MouseClick("",$ClosebuttonX,$ClosebuttonY) MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY) MouseClick("",$ExitCPScreenX,$ExitCPScreenY) MouseClick("",$ExitpatientHubX,$ExitpatientHubY) EndFunc ;Function should loop back to excel sheet, copy next account number, activate eclinicalworks, and repeat the steps ;--------------------------------------------------------------------------------------------------------------------------------------------------------- Func stopbaby() exit EndFunc Best Regards,
      Morphice
      steps for program.docx
    • SlackerAl
      By SlackerAl
      Running the first example of _Excel_RangeFind from the help file (note I have added the version MsgBox and changed the path to _Excel1.xls)
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> MsgBox(0, "Version", @AutoItVersion) ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Find all occurrences of value "37000" (partial match) ; ***************************************************************************** Local $aResult = _Excel_RangeFind($oWorkbook, "37000") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") I have also created a simple new Excel file "_Excel1.xls" in my script area and added "37000" to one cell.
      I generate the error:

      I arrived at this after generating the same error within my code. I'm using AutoIt version 3.3.14.2
      Any thoughts?
       
       
       
    • Ibet
      By Ibet
      Hey all, 
      Ending day 2 of learning AutoIt, and I'm stumped. I wrote an extremely rudimentary script simulating keystrokes for reading/copying values from one excel spreadsheet and pasting them into another spreadsheet, line by line. It works, but it doesn't use any of the Excel UDFs and was just sloppy. So, I'm trying to re-write it using some Excel UDFs to not only optimize the script, but to also learn how to use the Excel UDFs. If the answer is in a help file, please explain as I'm sometimes having problems understanding the examples in the help files.
      I'm getting the error: 
      "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Read spreadsheet 1 - write spreadsheet 2-version2.au3" (25) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: MsgBox(0,"Test","Test",$SourceEntry[1]) MsgBox(0,"Test","Test",^ ERROR >Exit code: 1 Time: 1.804 Here is the code:
      #include<Array.au3> #include<Excel.au3> ;-------------------Read from Source--------------------------- Local $oExcel_Source = _Excel_Open() Local $sWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_SOURCE.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel_Source,$sWorkbook) Local $SourceRow = 3 ;--eventually will be used to iterate through the rows, one at a time Local $SourceEntry[5] = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow) _ArrayDisplay($SourceEntry, "1D Display") ;--Displays array values correctly MsgBox(0,"Test","Test",$SourceEntry[1]) ;--Gives error, for any index in the array I want to make sure I can read the values of the array individually, before I try putting them into another document. This is because I've got to add some checks against the values already existing in the destination spreadsheet before any manipulation. I've spent the last hour or more googling that error and reading multiple posts where that error is meaning many different things, so unsure EXACTLY what the problem is. Would greatly appreciate a fix and/or explanation as well as patience with my noob-ness.
      Thanks in advance
×