Jump to content
Sign in to follow this  
Dimmae

Excel - select columns and put those together as new array

Recommended Posts

Dimmae

Hello,

at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.

Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.

I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.

The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.

 

Hope you can help me, and sry again for this 'unlucky illustration'.

 

btw: how can i add code shown as code here, instead of posting it as a attached file?.

 

 

 

autoit-select-column.au3

defects.xlsx

Edited by Dimmae
Failed with text input, just trying to get things in order

Share this post


Link to post
Share on other sites
JLogan3o13

@Dimmae welcome to the forum. Rather than adding the columns in one at a time, you could always just used the "UsedRange" feature in Excel and grab the whole thing:

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

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\defects.xlsx")
Local $aRange = _Excel_RangeRead($oWorkbook, Default, $oExcel.ActiveSheet.UsedRange)
    _ArrayDisplay($aRange)

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Dimmae

ah thanks @JLogan3o13, it worked wonderfully, and that's a lot easier than i thought. I might have been too deep in my problem to see this simple solution, thanks! but i had to change the 'DesktopDir' to 'ScriptDir' or i would run  into an error.

Edited by Dimmae

Share this post


Link to post
Share on other sites
Juvigy
6 minutes ago, Dimmae said:

ah thanks @JLogan3o13, it worked wonderfully, and that's a lot easier than i thought. I might have been too deep in my problem to see this simple solution, thanks! but i had to change the 'DesktopDir' to 'ScriptDir' or i would run  into an error.

This is because your excel file is not in the desktop dir , but somewhere else.

Share this post


Link to post
Share on other sites
Dimmae
Just now, Juvigy said:

This is because your excel file is not in the desktop dir , but somewhere else.

yes that makes sense :D thanks 

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

    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework:
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> HotKeySet("!v", "Pastedata") func Makearray() local $bArray() 'User has cells already copied 'Convert clipboard into an array 'I don't know how excel stores data to clipboard so don't know how to bring it into array _Arraydisplay($bArray) return $bArray end func func Pastedata() Local aArray 'make array based on assumption user has already copied a range to clipboard aArray = Makearray() 'paste code 'don't worry about this I got the rest end func  
    • nooneclose
      By nooneclose
      Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 
      Here is my code: 
      Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2 For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice      For $iN2 = 0 To $IndexRows Step 1          if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then              Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)              ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)              Local $eI  = $timeSheetName + 1              ;ConsoleWrite($timeSheetName & @CRLF)              ;ConsoleWrite($eI & @CRLF)              ;ConsoleWrite(@CRLF)              _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)                                                                          ;Inserts a empty cell in columns E and F.              _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)                                                                         ;Fills the empty cell in columns E with the doubled name              $aArray_Index = 2                                           ;Array element counter              For $Index = 2 To $IndexRows Step 1                        ;Loops through every row in the Excel file unto no rows are found or a null row is found                  $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)                  $e_names[$aArray_Index] = $Array_Value_E                ;While the code loops every value in column E is stored in the E array (updating the array)                  $aArray_Index += 1              Next              ExitLoop          EndIf      Next      $nI  += 1      $nII += 1 Next Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.
      I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 
      Any ideas on how to improve the "update" for my array?
      (once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)
      Any and all tips would be greatly appreciated. 
       
      NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 
    • smud
      By smud
      Currently, I'm working on a program that will display Dialog boxes with either Yes or No.
      For each dialog, I reward the user with X amount of Credits.
      I'm hoping to output the amount of credits to a cell in a column (there will be 20 different columns).
      It will only post to a row that is equal to today's date (first column). If no row exists yet with the current date, it will start a new row.
      Any suggestions?
      Thank you
    • nooneclose
      By nooneclose
      How do I properly convert this to Autoit? This is a VBA macro that I recorded in Excel.
       ActiveSheet.Outline.ShowLevels RowLevels:=2 I need this to close my subtotal once it is finished. 
      any help will be greatly appreciated. 
×