Search the Community

Hello I am relatively new to the world of Microsoft Office and the Excel UDF. I am trying to loop through every row in a spreadsheet and get the text/values from each column in the given row... so far I have looked into the Help file for the Excel UDF and the wiki page for Excel UDF but I have no idea about how this is done ... This is all I have in my script: Global $oExcel = _Excel_Open(False, False, False, False, True) Global Const $sSpreadsheet = @ScriptDir & '\data.xlsx' Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False) ; ... I am placing my bet on the _Excel_Range functions... especially _Excel_RangeRead. I don't know how $vRange works so I would be glad if someone can point me in the right direction . What I would ideally like is to get all of the contents of the spreadsheet (it's just a normal text one) in a 2D array. Thanks in Advance!

So, I have stumbled upon a problem with countring rows in excel files. The script I am working on will count the total number of rows of every file in the folder it was placed into. It is to my understanding that UsedRange counts rows even if they are empty, provided that a user has previously edited them. In other words, even if the file has two rows, the script might return 1000, if I edit and then leave empty cell (A,1000). How can I avoid this happening? Thanks in advance! #include <Array.au3> #include <File.au3> #include <MsgBoxConstants.au3> #include <Excel.au3> Global $Files = _FileListToArray (@ScriptDir, "*") $oExcel = _Excel_Open() $TotalNumberOfRows = 0 for $i=2 to $Files[0] Sleep(1000) $sWorkbook = @ScriptDir & "\" & $Files[$i] Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) Sleep(2000) $NumberOfRows = $oExcel.ActiveSheet.UsedRange.Rows.Count $TotalNumberOfRows= $TotalNumberOfRows + $NumberOfRows MsgBox(1, "", "Number of invoices: " & $TotalNumberOfRows) Sleep(100) _Excel_BookClose ($oWorkbook, False) Next

I recently >posted a question in the general help and support about how the best way to find the last non-empty cell in a workbook. @Water was kind enough to help me find several solutions. During that thread we also posted a snippet that I find to be very useful. It was slightly off topic from the OP so I thought I would post it here so it has a home in case anyone needs it. That said, this is one way to delete all blank rows in a spreadsheet: $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete The $xlCellTypeBlanks is a constant in the Excel constants include so you don't need to pre-define it. "A:A" is the range. So this would look for any blank rows in Col A and delete those rows from the workbook. Hope somebody finds it useful.

Hi All, I recently noticed when recompiling an older script that the listview row coloration was no longer functioning as expected. I have narrowed it down to this line in my WM_NOTIFY routine: If Not _GUICtrlListView_GetViewDetails($hWndFrom) Then Return $GUI_RUNDEFMSG If I comment out the line, the the alternating row coloration works as expected. Is this line no longer needed, or has something changed / broken in the newer versions of Autoit? Tested with 3.3.12.0 and 3.3.13.19. Here's the complete WM_NOTIFY function: Func WM_NOTIFY($hWnd, $Msg, $wParam, $lParam) Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR Local Const $iListBkColor = 0xFFFFFF Local Const $iListAltBkColor = 0xFFF68F Local Const $iListSelColor = 0x00BFFF ;custom draw constants Local Const $CDDS_ITEM = 0x10000 Local Const $CDDS_MAPPART = 0x5 Local Const $CDDS_POSTERASE = 0x4 Local Const $CDDS_POSTPAINT = 0x2 Local Const $CDDS_PREERASE = 0x3 Local Const $CDDS_PREPAINT = 0x1 Local Const $CDDS_SUBITEM = 0x20000 Local Const $CDDS_ITEMPOSTERASE = BitOR($CDDS_ITEM, $CDDS_POSTERASE) Local Const $CDDS_ITEMPOSTPAINT = BitOR($CDDS_ITEM, $CDDS_POSTPAINT) Local Const $CDDS_ITEMPREERASE = BitOR($CDDS_ITEM, $CDDS_PREERASE) Local Const $CDDS_ITEMPREPAINT = BitOR($CDDS_ITEM, $CDDS_PREPAINT) Local Const $CDRF_DODEFAULT = 0x0 Local Const $CDRF_NEWFONT = 0x2 Local Const $CDRF_NOTIFYITEMDRAW = 0x20 Local Const $CDRF_NOTIFYPOSTERASE = 0x40 Local Const $CDRF_NOTIFYPOSTPAINT = 0x10 Local Const $CDRF_NOTIFYSUBITEMDRAW = 0x20 Local Const $CDRF_SKIPDEFAULT = 0x4 $tNMHDR = DllStructCreate($tagNMHDR, $lParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iIDFrom = DllStructGetData($tNMHDR, "IDFrom") $iCode = DllStructGetData($tNMHDR, "Code") Switch $hWndFrom Case $hListView Switch $iCode Case $LVN_BEGINDRAG ; Disallow drag operation Return Case $NM_CUSTOMDRAW If Not _GUICtrlListView_GetViewDetails($hWndFrom) Then Return $GUI_RUNDEFMSG Local $tCustDraw = DllStructCreate('hwnd hwndFrom;int idFrom;int code;' & _ 'dword DrawStage;hwnd hdc;long rect[4];dword ItemSpec;int ItemState;dword Itemlparam;' & _ 'dword clrText;dword clrTextBk;int SubItem;' & _ 'dword ItemType;dword clrFace;int IconEffect;int IconPhase;int PartID;int StateID;long rectText[4];int Align', _ ;winxp or later $lParam), $iDrawStage, $iItem, $iSubitem, $hDC, $iColor1, $iColor2, $iColor3 $iDrawStage = DllStructGetData($tCustDraw, 'DrawStage') If $iDrawStage = $CDDS_PREPAINT Then Return $CDRF_NOTIFYITEMDRAW ;request custom drawing of items If $iDrawStage = $CDDS_ITEMPREPAINT Then Return $CDRF_NOTIFYSUBITEMDRAW ;request drawing each cell separately If Not BitAND($iDrawStage, $CDDS_SUBITEM) Then Return $CDRF_DODEFAULT $iItem = DllStructGetData($tCustDraw, 'ItemSpec') $iSubitem = DllStructGetData($tCustDraw, 'SubItem') If Mod($iItem, 2) = 0 Then DllStructSetData($tCustDraw, 'clrTextBk', RGB2BGR($iListAltBkColor)) Else DllStructSetData($tCustDraw, 'clrTextBk', RGB2BGR($iListBkColor)) EndIf Return $CDRF_NEWFONT EndSwitch EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Thoughts? Thx, Dan