Jump to content
Sign in to follow this  

User selects which Excel column to _ExcelReadArray not cooperating

Recommended Posts


I want to have the user enter the number of the excel worksheet column containing the data to be read into an array.

If I write the column number I want in the code, it works fine.

This is the code:

#include <ExcelCOM_UDF.au3>
#include <Array.au3>
;Allow user to select an excel file and recommend a specific file.
$var = FileOpenDialog($message, @MyDocumentsDir & "\", " (*.xls;*.xlsx)", 0, "Chronic Illnesses")
If @error Then ;Check if they did not select a file
    MsgBox(4096,"","No File(s) chosen", 3)
$oExcel = _ExcelBookOpen($var) ;Open the selected excel file
$aSheet = _ExcelSheetList($oExcel) ; how many sheets?
$ClmnNr = InputBox("Visit Column", "Enter the search column Number") ;User choses column to use for array
$vSheet = $aSheet[$m]
$arrayRange = _ExcelSheetUsedRangeGet($oExcel, $vSheet)
$XLArray = _ExcelReadArray($oExcel, 2, $ClmnNr, $arrayRange[3], 1,0); Get the data from entered column number.
_ArrayDisplay($XLArray,"Stuff") ;display the column values.

If I answer the input prompt with a column letter, there is no error but no result either.

If I answer the input prompt with a number (like it should be, after all, the input prompt fills the variable $ClmnNr with the number corresponding to the column),

I get this error:

C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (618) : ==> The requested action with this object has failed.:
$aArray[$xx] = $oExcel.Activesheet.Cells(($xx - $iIndexBase) + $iStartRow, $iStartColumn).Value
$aArray[$xx] = $oExcel.Activesheet.Cells(($xx - $iIndexBase) + $iStartRow, $iStartColumn)^ ERROR
->15:08:48 AutoIT3.exe ended.rc:1

I have a feeling it is a very simple thing I'm doing wrong but I just can't put my finger on it. Any help will be appreciated.

Share this post

Link to post
Share on other sites

I got it!

The input box returns a "string" not a number.

To convert it to a number, add the following line after the input box.

$ClmnNr = Int($ClmnNr)

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  


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.