this is driving me nuts - i get the row count, but not the column count - what am I missing? Thanks for your help!
#include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ; XlDirection enumeration: https://msdn.microsoft.com/en-us/library/office/ff820880.aspxGlobal $oExcel = _Excel_Open() Global $xlup = -4162 Global $xlByRows, $xlPrevious, $xlByColumns Global $oExcel = _Excel_Open() $bookname = "temp.xlsx" $sWorkbook = @ScriptDir & "\" & $bookname Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True) With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iColCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Column.Count MsgBox(0, "", "row:" & $iRowCount & "Col:" & $iColCount) EndWith
for context - i want to :
* count columns used in excel
* create ini file from the rows in each column - finishing at the last column used - i.e. one column for one ini file; containing 15 rows or so.
is it better to read the entire sheet to an array via the sheettoarray function? then read it from that?
Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now.
I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3
I saw this code:
With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>"
Is there another include file I need?
I got this to work for highlighting cells, wondering if there is a option similar to this for all borders?
$oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks
I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.
This in itself is working, but the RangeWrite function always puts the Array count in the first cell.
How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.
This is what my script looks like:
Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
Local $cDate = StringReplace($Yesterday, "/", "-")
Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)
Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?
Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.
#include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable.
Any help is appreciated. Thanks in advance.
Here is a function that will return a 2D array of visible windows. You will notice that windows "Start" and "Program Manager" windows will always be in the array. The array returns the title of the window, the window handle, the PID of the process associated with the window, the process name associated with the window, the window's position, and the window's dimension, Please see comments in the code about the numbers returned about the window's position.
#include <Array.au3> #include <Process.au3> ;Get a list of visable windows with titles. $aWindows = _GetVisibleWindows() _ArrayDisplay($aWindows) Func _GetVisibleWindows() ;Retrieve a list of windows. Local $aWinList = WinList() If Not IsArray($aWinList) Then Return SetError(0, 0, 0) ;Loop through the array deleting no title or invisable windows. Local $sDeleteRows = "" For $i = 1 To $aWinList If $aWinList[$i] = "" Or Not BitAND(WinGetState($aWinList[$i]), $WIN_STATE_VISIBLE) Then $sDeleteRows &= $i & ";" EndIf Next $sDeleteRows = StringTrimRight($sDeleteRows, 1) ;Remove last ";". _ArrayDelete($aWinList, $sDeleteRows) $aWinList = UBound($aWinList) - 1 ;Get Window's Processor ID (PID), and add to the array. _ArrayColInsert($aWinList, UBound($aWinList, 2)) For $i = 1 To $aWinList $aWinList[$i] = WinGetProcess($aWinList[$i]) Next ;Get Window's Process Name from PID, and add to the array. _ArrayColInsert($aWinList, UBound($aWinList, 2)) For $i = 1 To $aWinList $aWinList[$i] = _ProcessGetName($aWinList[$i]) Next ;Get Windows's Position and Size, and add it to the array. ;For Position, -3200,-3200 is minimized window, -8,-8 is maximized window on 1st display, and ;x,-8 is maximized windown on the nth display were x is the nth display width plus -8 (W + -8). _ArrayColInsert($aWinList, UBound($aWinList, 2)) ;Position (X,Y). _ArrayColInsert($aWinList, UBound($aWinList, 2)) ;Dimension (WxH). Local $aWinPosSize For $i = 1 To $aWinList $aWinPosSize = WinGetPos($aWinList[$i]) $aWinList[$i] = $aWinPosSize & "," & $aWinPosSize $aWinList[$i] = $aWinPosSize & "x" & $aWinPosSize Next Return $aWinList EndFunc ;==>_GetVisibleWindows