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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...