jerem488 Posted December 29, 2018 Share Posted December 29, 2018 Hi all, I read an excel file, it works fine. I create a combo list with values of the excel file. But I don't understand why my StringStripWS() function doesn't work. For example in the Excel file attached to this topic, the "Open Banco Sabadell" cell, there is a blanck at the end of the string and the StringStripWS() function doesn't delete the blanck at the end. You're going to tell me to remove it manually in the Excel File, but the Excel file attached to this topic is part of many other Excel files. expandcollapse popup#include <GUIConstantsEx.au3> #include <Excel.au3> #include <String.au3> Global $s_File = @DesktopDir & "\s_2017.xls" Global $o_Excel = _Excel_Open() Global $o_Workbook = _Excel_BookOpen($o_Excel, $s_File) Local $h_GUI = GUICreate("Selection", 490, 200) Local $id_Tournois = GUICtrlCreateCombo("", 20, 35, 450) GUICtrlSetData($id_Tournois, _ArrayToString(Tournaments("B"), "|", 1)) GUISetState(@SW_SHOW, $h_GUI) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE ExitLoop EndSwitch WEnd GUIDelete($h_GUI) _Excel_BookClose($o_Workbook) _Excel_Close($o_Excel) Func Tournaments($s_Letter) Local $a_Tournois[0] Local $a_Results = _Excel_RangeRead($o_Workbook, Default, $o_Workbook.Sheets("Tournament_List").Usedrange.Columns($s_Letter & ":" & $s_Letter)) $s_Results_StripWS = StringStripWS(_ArrayToString($a_Results), 7) For $i = 1 To StringLen($s_Results_StripWS) ConsoleWrite(":" & StringMid($s_Results_StripWS, $i, 1) & ":" & Asc(StringMid($s_Results_StripWS, $i, 1)) & @LF) Next $a_Results_TitleCase = _StringTitleCase($s_Results_StripWS) _ArrayAdd($a_Tournois, $a_Results_TitleCase) $a_Unique = _ArrayUnique($a_Tournois) Local $i_Tournament = _ArraySearch($a_Unique, "Tournament") _ArrayDelete($a_Unique, $i_Tournament) _ArraySort($a_Unique) ConsoleWrite("Number of rows:" & UBound($a_Unique, $UBOUND_ROWS) & @LF) Return $a_Unique EndFunc s_2017.xls Qui ose gagneWho Dares Win[left]CyberExploit[/left] Link to comment Share on other sites More sharing options...
Nine Posted December 29, 2018 Share Posted December 29, 2018 $s_Results_StripWS = StringStripWS(_ArrayToString($a_Results), 7) _ArrayToString = Places the elements of a 1D or 2D array into a single string, separated by the specified delimiters Single string is made with _ArrayToString (single is the keyword here) StringStripWS (with flag 7) = leading, trailing, and double (for a single string) It is normal that you don't eliminate a simple ws inside a single string. Once you have sorted out all the elements of the array, do the StringStripWs on each individual cell “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
Danp2 Posted December 29, 2018 Share Posted December 29, 2018 You can't use StringStripWS like this. The following works fine for me -- Func Tournaments($s_Letter) Local $a_Tournois[0] Local $a_Results = _Excel_RangeRead($o_Workbook, Default, $o_Workbook.Sheets("Tournament_List").Usedrange.Columns($s_Letter & ":" & $s_Letter)) For $i = 0 To UBound($a_Results) - 1 $a_Results[$i] = _StringTitleCase(StringStripWS($a_Results[$i], 7)) Next $a_Unique = _ArrayUnique($a_Results) Local $i_Tournament = _ArraySearch($a_Unique, "Tournament") _ArrayDelete($a_Unique, $i_Tournament) _ArraySort($a_Unique) ConsoleWrite("Number of rows:" & UBound($a_Unique, $UBOUND_ROWS) & @LF) Return $a_Unique EndFunc Latest Webdriver UDF Release Webdriver Wiki FAQs Link to comment Share on other sites More sharing options...
Subz Posted December 29, 2018 Share Posted December 29, 2018 Alternative method which will trim spaces and set proper casing in the document. Func Tournaments($s_Letter) Local $o_Range = $o_Workbook.Sheets("Tournament_List").Usedrange.Columns($s_Letter & ":" & $s_Letter) $o_Range.Value = $o_Excel.Trim($o_Range) $o_Range.Value = $o_Excel.Proper($o_Range) Local $a_Results = _Excel_RangeRead($o_Workbook, Default, $o_Range) _ArrayDelete($a_Results, 0) ;~ Delete the first row item "Tournament" _ArraySort($a_Results) Local $a_Tournois = _ArrayUnique($a_Results) Return $a_Tournois EndFunc Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now