Jump to content
Sign in to follow this  
uncommon

3D array from excel

Recommended Posts

uncommon

I have look at the examples in the forms and in the help file however I still dont think I understand what is wrong with my script. I keep getting a "Subscript used with non-Array variable" error. Maybe I am delcaring the array wrong but I am not sure.

I am trying to get the excel sheets to populate into a 3D array.

#include <GUIConstantsEx.au3>
#include <GuiButton.au3>
#include <GuiTreeView.au3>
#include <WindowsConstants.au3>
#include <GuiTab.au3>
#include <array.au3>
#include <Excel.au3>
Global $next = 0
Opt("MustDeclareVars", 0)

;...

$oExcel = _ExcelBookOpen(@WorkingDir & "\Databaseproject.xls", 0)
dim $Databaseproject[10]
dim $array[100][100]
For $sheet = 1 to 10
_ExcelSheetActivate($oExcel, $sheet)
$array = _ExcelReadSheetToArray($oExcel, 1, 1, 100, 100)
$Databaseproject[$sheet] = $array[100][100]
Next
_ArrayDisplay($Databaseproject)
_ExcelBookClose($oExcel)

No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites
BrewManNH

You're not using a 3D array for one thing, and for another, you never test to see if the _ExcelReadSheetToArray returns anything, or if it does return anything, whether there are 100 rows and columns. Also, does your spreadsheet really have 10 sheets in it?


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
uncommon

You're not using a 3D array for one thing, and for another, you never test to see if the _ExcelReadSheetToArray returns anything, or if it does return anything, whether there are 100 rows and columns. Also, does your spreadsheet really have 10 sheets in it?

If I am not using a 3D array then I must be declaring it wrong, could you give me an example of what it should look like?

I did test to see if I was getting an array from excel using _ArrayDisplay but I thought was was unnecessary for this post. As far as returning the 100 sheets, that's just over compensating if I need it later. I put ten sheets in there in case I would need it.


No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites
jdelaney

single d array: $array[1]

2d" $array[1][1]

3d: $array[1][1][1]

so you can setup like $array[sheet][x][y]

or, an array of arrays, using the excel read sheet to array function:

$array[sheet] = _ExcelReadSheetToArray() (so, this is a 1d array, comprised of 2d arrays)

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites
water

Easier to handle are 2D arrays. 3D arrays can become quite complex to work with.

Do you really need to process multiple sheets in one go? Or could you process sheet by sheet?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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
uncommon

Easier to handle are 2D arrays. 3D arrays can become quite complex to work with.

Do you really need to process multiple sheets in one go? Or could you process sheet by sheet?

Maybe you are right Water, I might be over doing this a bit. I must think on this, maybe there is a way I can flatten the data.

Universalist, an Array within array, I did not think that was possible but I will try that function out. Thanks for your comments.


No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites
kylomas

uncommon,

This is a rough example of what jdelaney has advised...

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

local $myExcelFile = @scriptdir & '\budget.xls'
Local $oExcel = _ExcelBookOpen($myExcelFile,1)

switch @error
    case 1
        ConsoleWrite('Unable to create object' & @LF)
        exit
    case 2
        ConsoleWrite('File does not exist' & @LF)
        Exit
endswitch

local $sheet_list = _excelsheetlist($oExcel)                ; get list of sheets to array, count is at offset 0
local $aSheets[$sheet_list[0]]                              ; define array from sheet list

for $1 = 0 to ubound($aSheets) - 1
    _ExcelSheetActivate($oExcel, $sheet_list[$1])           ; activate each sheet
    $aSheets[$1] = _ExcelReadSheetToArray($oExcel)          ; read each sheet (2D array) to an element of $aSheets (1D array)
next

for $1 = 0 to ubound($aSheets) - 1
    _arraydisplay($asheets[$1])                             ; $aSheets[$1] contains a 2D array
    ConsoleWrite('Number of dimensions for sheet # ' & $1 & ' = ' & ubound($aSheets[$1],0) & @LF)
next

_ExcelBookClose($oExcel, 1)

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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  

  • Similar Content

    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By 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  
×