Leaderboard

Ok guys... back to English please in open forum. Just keep practicing it and you will soon master it. Jos

Oops, sorry I didn't have enough time to check carefully before leaving. The negation was malplaced: Func _StringTitleCase3($s) Return(Execute("'" & StringRegExpReplace(StringLower($s), "(*UCP)(?<!\pL)(\p{Ll})", "' & StringUpper('$1') & '") & "'")) EndFunc Titlecase differs from uppercasing the first "letter" in several instances, like when applied to some digrams. As an example, at least those character fall into his bucket: Codepoint Character Upper Lower Fold u00001C5 Dž DŽ dž dž u00001C8 Lj LJ lj lj u00001CB Nj NJ nj nj u00001F2 Dz DZ dz dz Unicode (here understand that by "human scripts") makes subtle differences between lowercase and foldcase, titlecase and propercase and titlecase. For most human scripts that doesn't make a difference but for some it matters. EDIT: unfortunately, I don't recall Windows natively offering a primitive for Title or such, but maybe things have changed since I last looked.

he's just digesting it for him now

@tezhihi The regex doesn't work because of the alternation in (|\\(.*)) : "match nothing or \\(.*)" . For the first string, (D.*?) returns everything after D If you remove the alternation it will work for the first but not for the second. And if you make the second part of the expression optional then it doesn't work for the first string again So assuming that the part you want always begins with a D, a correct way could be this $b = StringRegExpReplace($a, '.*\\(D[^\\]+).*', '$1') this expression matches "D and one or more non-backslash chars" (heeding the backslash just before the D) Edit Trong, sometimes you have to guess a little what is the real question and so what should be a correct answer i.e. the non-regex way Local $b, $split = StringSplit($a, "\") For $i = 2 to $split[0] If StringLeft($split[$i], 1) == "D" Then $b = $split[$i] Next msgbox(0,"", $b)

Thanks. I have deadlines to meet, but I'll take a good look at it later in the week.

@TigBits Welcome to the AutoIt forum Your nickname is a bit tricky, but, when you change the position the first T with the B, you got an awesome nickaname. By the way, to help you, we need more information. Remember that, when you ask for help, is better to attach the code you worked/are working with, in order to have something in our hand to work with

@Jibberish one of our Regex experts will doubtless wander along and provide something more elegant, but for a simple method this works well for me. For this example my file is sitting on the desktop, change your file path accordingly. Let me know if you have any questions: #include <File.au3> Local $aFile, $sText, $sFile = @DesktopDir & "\test.xml" _FileReadToArray($sFile, $aFile) For $a = 1 To $aFile[0] If StringInStr($aFile[$a], '"LoopCheckbox" value="false"') Then $sText = StringReplace(FileReadLine($sFile, $a), '="false"', '="true"') _FileWriteToLine($sFile, $a, $sText, 1) EndIf Next

Create a hotkey, set it to copy text, then have it append to your text file.

@jchd I just took a look at this. You have created a kind of 'camel' title case: I don't think that was quite your intention. The first word is not being capitalized. I struggled to find a good regular expression myself. I was expecting the output to be like this (patch seems to be working). MsgBox(0, "", _StringTitleCase3("đây là dòng chữ tiếng_việt chuẩn")) Func _StringTitleCase3($s) Return StringTrimLeft(Execute("'" & StringRegExpReplace(" " & StringLower($s), "(*UCP)(?<=\PL)(\p{Ll})", "' & StringUpper('$1') & '") & "'"), 1) EndFunc

You may have noticed that WM_COMMAND messages are still handled correctly when you right-click in the small empty area below the TreeView. Only when you right-click in the TreeView area the messages are not handled correctly. When you right-click in a window WM_COMMAND messages are always send to the same window. If you right-click in the GUI WM_COMMAND messages are send to the GUI. If you right-click in the TreeView WM_COMMAND messages are to send to the TreeView. But GUIRegisterMsg is only able to catch messages which are send to the GUI. GUIRegisterMsg cannot catch messages which are send to the TreeView. The solution is to subclass the TreeView. Details in the codebox. I've added 10 code lines. #include <GUIConstantsEx.au3> #include <GuiTreeView.au3> #include <WindowsConstants.au3> #include <GuiMenu.au3> #include <WinAPIShellEx.au3> Global $g_hTreeView Global Enum $e_idOpen = 1000, $e_idSave, $e_idInfo Example() Func Example() Local $hGUI, $hItem Local $iStyle = BitOR($TVS_EDITLABELS, $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_SHOWSELALWAYS, $TVS_CHECKBOXES) $hGUI = GUICreate("(UDF Created) TreeView Create", 400, 300) $g_hTreeView = _GUICtrlTreeView_Create($hGUI, 2, 2, 396, 268, $iStyle, $WS_EX_CLIENTEDGE) GUISetState(@SW_SHOW) GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") GUIRegisterMsg($WM_CONTEXTMENU, "WM_CONTEXTMENU") GUIRegisterMsg($WM_COMMAND, "WM_COMMAND") Local $pTreeViewMsgHandler = DllCallbackGetPtr( DllCallbackRegister( "TreeViewMsgHandler", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) ) _WinAPI_SetWindowSubclass( $g_hTreeView, $pTreeViewMsgHandler, 0, 0 ) ; $iSubclassId = 0, $pData = 0 _GUICtrlTreeView_BeginUpdate($g_hTreeView) For $x = 1 To Random(2, 10, 1) $hItem = _GUICtrlTreeView_Add($g_hTreeView, 0, StringFormat("[%02d] New Item", $x)) For $y = 1 To Random(2, 10, 1) _GUICtrlTreeView_AddChild($g_hTreeView, $hItem, StringFormat("[%02d] New Child", $y)) Next Next _GUICtrlTreeView_EndUpdate($g_hTreeView) ; Loop until the user exits. Do Until GUIGetMsg() = $GUI_EVENT_CLOSE _WinAPI_RemoveWindowSubclass( $g_hTreeView, $pTreeViewMsgHandler, 0 ) GUIDelete() EndFunc ;==>Example Func TreeViewMsgHandler( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData ) Switch $iMsg Case $WM_COMMAND Switch $wParam Case $e_idOpen _DebugPrint("WM_COMMAND " & $wParam & " Open") Case $e_idSave _DebugPrint("WM_COMMAND " & $wParam & " Save") Case $e_idInfo _DebugPrint("WM_COMMAND " & $wParam & " Info") EndSwitch EndSwitch ; Call next function in subclass chain (this forwards Windows messages to main GUI) Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0] EndFunc Func WM_CONTEXTMENU($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $lParam ;ConsoleWrite($GUI_RUNDEFMSG & @CRLF) ;Switch $wParam ; Case $hTreeView Local $hMenu $hMenu = _GUICtrlMenu_CreatePopup() _GUICtrlMenu_InsertMenuItem($hMenu, 0, "Open", $e_idOpen) _GUICtrlMenu_InsertMenuItem($hMenu, 1, "Save", $e_idSave) _GUICtrlMenu_InsertMenuItem($hMenu, 3, "", 0) _GUICtrlMenu_InsertMenuItem($hMenu, 3, "Info", $e_idInfo) _GUICtrlMenu_TrackPopupMenu($hMenu, $wParam) _GUICtrlMenu_DestroyMenu($hMenu) Return True ;EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>WM_CONTEXTMENU Func WM_COMMAND($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $lParam Switch $wParam Case $e_idOpen _DebugPrint("WM_COMMAND " & $wParam & " Open") Case $e_idSave _DebugPrint("WM_COMMAND " & $wParam & " Save") Case $e_idInfo _DebugPrint("WM_COMMAND " & $wParam & " Info") EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>WM_COMMAND Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $wParam Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR, $hWndTreeview $hWndTreeview = $g_hTreeView If Not IsHWnd($g_hTreeView) Then $hWndTreeview = GUICtrlGetHandle($g_hTreeView) $tNMHDR = DllStructCreate($tagNMHDR, $lParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iIDFrom = DllStructGetData($tNMHDR, "IDFrom") $iCode = DllStructGetData($tNMHDR, "Code") Switch $hWndFrom Case $hWndTreeview Switch $iCode Case $NM_CLICK ; The user has clicked the left mouse button within the control _DebugPrint("$NM_CLICK" & @CRLF & "--> hWndFrom:" & @TAB & $hWndFrom & @CRLF & _ "-->IDFrom:" & @TAB & $iIDFrom & @CRLF & _ "-->Code:" & @TAB & $iCode) ; Return 1 ; nonzero to not allow the default processing Return 0 ; zero to allow the default processing Case $NM_DBLCLK ; The user has double-clicked the left mouse button within the control _DebugPrint("$NM_DBLCLK" & @CRLF & "--> hWndFrom:" & @TAB & $hWndFrom & @CRLF & _ "-->IDFrom:" & @TAB & $iIDFrom & @CRLF & _ "-->Code:" & @TAB & $iCode) ; Return 1 ; nonzero to not allow the default processing Return 0 ; zero to allow the default processing EndSwitch EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Func _DebugPrint($s_Text, $sLine = @ScriptLineNumber) ConsoleWrite( _ "!===========================================================" & @CRLF & _ "+======================================================" & @CRLF & _ "-->Line(" & StringFormat("%04d", $sLine) & "):" & @TAB & $s_Text & @CRLF & _ "+======================================================" & @CRLF) EndFunc ;==>_DebugPrint

I know there are some Excel .xml UDF's out there already but since I'm using this for frickin huge reports I didn't want to have to call a function for each cell I needed to populate so I wrote a function that would take a 2D array which contains another 2D array in each row for each worksheet. Currently I'm using this to pull defect reports from HP ALM for 102 Projects. The below example has been modified to look more like a Server Patching report since I know a lot of you do that sort of stuff. My Largest report right now has 103 worksheets, with between 100 and 10,000 rows in each worksheet and is about 4MB in size after being saved as .xlsx Excel would randomly crash when building these reports through direct interaction especially when performing the formatting but using the .xml code below it builds the files extremely quick and most importantly, with no crashing. The sample below creates a single excel workbook with 59 worksheets. An overview worksheet that has hyperlinks to the other 58 worksheets. This is what I now use as my standard report template. #include <Excel.au3> ;This is only used when we want to convert the .xml to .xlsx $aAllWorksheets = _Build_Sample_Array() ;This builds an array populated with data so we can write it out to the .xml file ;Save the spreadsheet as .xml only ;_GenerateXML($aAllWorksheets, 6, "C:\TEMP\SpreadsheetXMLformat.xml", False) ;_GenerateXML("Array containing everything", "max number of columns needed in the entire worksheet", "The target .xml file") ;Save the array data to .xlsx and remove the temp .xml file _GenerateXML($aAllWorksheets, 6, "C:\TEMP\SpreadsheetFromXML.xlsx") ;_GenerateXML("Array containing everything", "max number of columns needed in the entire worksheet", "The target .xlsx file") ;----------------------------------------------------------------------------------------------------------------------------------------------------- ;Creates a .xml file using FileWrite and does not require Excel at all if leaving as .xml. ;$aWorksheets is a 2D array where the last array item stores another 2D array for the individual worksheets. ;Required 2D Array setup for $aWorksheets ; [0][#] will store the column width for the Overview worksheet columns ; [1][#] will store the Headers for the Overview worksheet Columns ; [2+][#] will store the individual worksheets ; [2+][last array item] This will be used to store another array which contains all data for the worksheet ; ; Worksheet 2D Array $aWorksheetData ; [0][#] will store the column width for the worksheet ; [1][#] will store the column headers A1 will be used as a hyperlink back to the overview worksheet ; [2+][#] Remaining items will store the data used to populate the worksheet ; ; $iMaxColumns, This needs to be the maximum Column used in any of the worksheets so you will need to count as you're building the 2D arrays ; ; $sOutputFile: If Convert to XLSX = True, then this OutputFile must end with .xlsx. ; If the Convert to XLSX = False, then this output file must end with .xml ; ; $ConvertToXLSX: If True, then there will be a temp .xml file created with the array data and will then be opened with Excel and saved as .xlsx ; If False, then it will create just the .xml file, warning these can be huge. ;----------------------------------------------------------------------------------------------------------------------------------------------------- Func _GenerateXML($aWorksheets, $iMaxColumns, $sOutputFile, $ConvertToXLSX = True) ConsoleWrite("Creating Report" & @CRLF) ;Create the main Workbook, this will need to be customized if you want to use other font, or Styles ; Easiest way to get new styles is to create in Excel, save as XML Spreadsheet 2003 (*.xml) then open it up in Scite to pull out the new style, "look for Style child items too" ;Below are the Current styles that can be used ; ;Default: If no style specified then this will be used ; Align Right, Bottom ; No cell borders ; Times New Romain, size 12 ; Black font on white background ; ;s16: Used for the Hyperlinks ; Font Times New Romain, size 12 ; Blue text that is Underlined ; ;s17: Format the cell as a Number ; ;s18: Used for Header text ; Font Times New Romain, Size 12 ; Black Font, Bold ; Set background color to blue ; Set cells as Text formatting ; ;s19: This is a child item of s16 "not sure why it's always stuck down at bottom" ; Used to apply Hyperlink to items in the Header row with background color ; ;s20: This is another child item of s16, used to show hyperlinks on normal cells ;Create the $sWorksheets string that will contain the entire .xml that will be written to file. This top section identifies it as Excel XML, and sets up the Styles that can be used. $sWorksheets = '<?xml version="1.0"?>' & @CRLF & _ '<?mso-application progid="Excel.Sheet"?>' & @CRLF & _ '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" ' & @CRLF & _ 'xmlns:o="urn:schemas-microsoft-com:office:office" ' & @CRLF & _ 'xmlns:x="urn:schemas-microsoft-com:office:excel" ' & @CRLF & _ 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ' & @CRLF & _ 'xmlns:html="http://www.w3.org/TR/REC-html40"> ' & @CRLF & _ '<Styles> ' & @CRLF & _ '<Style ss:ID="Default" ss:Name="Normal"> ' & @CRLF & _ '<Alignment ss:Vertical="Bottom"/> ' & @CRLF & _ '<Borders/> ' & @CRLF & _ '<Font ss:FontName="Times New Roman" x:Family="Swiss" ss:Size="12" ' & @CRLF & _ 'ss:Color="#000000"/> ' & @CRLF & _ '<Interior/> ' & @CRLF & _ '<NumberFormat/> ' & @CRLF & _ '<Protection/> ' & @CRLF & _ '</Style> ' & @CRLF & _ '<Style ss:ID="s16" ss:Name="Hyperlink"> ' & @CRLF & _ '<Font ss:FontName="Times New Roman" x:Family="Swiss" ss:Size="12" ' & @CRLF & _ 'ss:Color="#0563C1" ss:Underline="Single"/> ' & @CRLF & _ '</Style> ' & @CRLF & _ '<Style ss:ID="s17"> ' & @CRLF & _ '<NumberFormat ss:Format="@"/> ' & @CRLF & _ '</Style> ' & @CRLF & _ '<Style ss:ID="s18"> ' & @CRLF & _ '<Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="12" ' & @CRLF & _ 'ss:Color="#000000" ss:Bold="1"/> ' & @CRLF & _ '<Interior ss:Color="#BDD7EE" ss:Pattern="Solid"/> ' & @CRLF & _ '<NumberFormat ss:Format="@"/> ' & @CRLF & _ '</Style> ' & @CRLF & _ '<Style ss:ID="s19" ss:Parent="s16"> ' & @CRLF & _ '<Interior ss:Color="#BDD7EE" ss:Pattern="Solid"/> ' & @CRLF & _ '<NumberFormat ss:Format="@"/> ' & @CRLF & _ '</Style> ' & @CRLF & _ '<Style ss:ID="s20" ss:Parent="s16"> ' & @CRLF & _ '<NumberFormat ss:Format="@"/> ' & @CRLF & _ '</Style> ' & @CRLF & _ '</Styles> ' & @CRLF ;Create the Overview Worksheet which will have hyperlinks to all of the other ones ; ss:Name= the name of the worksheet tab ; _FilterDatabase: Add the Dropdown filters to the used columns ; ss:ExpandedColumnCount: Seems to need the max column count from All worksheets in the workbook which is passed to the function in $iMaxColumns ; ExpandedRowCount = the total rows being used for this worksheet $sWorksheets &= '<Worksheet ss:Name="Overview">' & @CRLF & _ '<Names>' & @CRLF & _ '<NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=''Overview''!R1C1:R2C' & $iMaxColumns & '" ss:Hidden="1"/>' & @CRLF & _ '</Names>' & @CRLF & _ '<Table ss:ExpandedColumnCount="' & $iMaxColumns & '" ss:ExpandedRowCount="' & UBound($aWorksheets) & '" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="15.75">' & @CRLF ;Need to format the width of the columns depending on the [0][#] data For $c = 0 to UBound($aWorksheets, 2) - 2 ;The last item in the array is the Worksheets array so we don't want to use that as a column in the overview worksheet $sWorksheets &= '<Column ss:AutoFitWidth="0" ss:Width="' & $aWorksheets[0][$c] & '"/>' & @CRLF Next ;We now need to add each of the Servers as a row in the Overview worksheet For $i = 1 to UBound($aWorksheets) - 1 If $aWorksheets[$i][0] = "" Then ContinueLoop ;If the Domain is not populated, then we shouldn't add the rest since this is most likely an empty row at the end $iLastArray = UBound($aWorksheets, 2) - 1 ;Identify the last array item for each row which should contain individual worksheet array data If $i = 1 Then ;This is the header row $sWorksheets &= '<Row ss:StyleID="s18">' & @CRLF ;make the Header row as blue background and bold using Style 18 For $c = 0 to UBound($aWorksheets, 2) - 1 If $aWorksheets[$i][$c] <> "" Then $sWorksheets &= '<Cell><Data ss:Type="String">' & $aWorksheets[$i][$c] & '</Data></Cell>' & @CRLF Next $sWorksheets &= '</Row>' & @CRLF Else $sWorksheets &= '<Row>' & @CRLF ;Write the value in Column A If $aWorksheets[$i][0] <> "" Then $sWorksheets &= '<Cell><Data ss:Type="String">' & $aWorksheets[$i][0] & '</Data></Cell>' & @CRLF ;If this row has Array Data then add a hyperlink in Column B to the Worksheet which will be created If IsArray($aWorksheets[$i][$iLastArray]) Then If $aWorksheets[$i][1] <> "" Then $sWorksheets &= '<Cell ss:StyleID="s20" ss:HRef="#''' & $aWorksheets[$i][1] & '''!A1" x:HRefScreenTip="' & $aWorksheets[$i][1] & '"><Data ss:Type="String">' & $aWorksheets[$i][1] & '</Data></Cell>' & @CRLF Else If $aWorksheets[$i][1] <> "" Then $sWorksheets &= '<Cell><Data ss:Type="String">' & $aWorksheets[$i][1] & '</Data></Cell>' & @CRLF EndIf ;Only write out Column C if there is enough items in the main array to contain it If ($iLastArray > 2) and ($aWorksheets[$i][2] >= 0) Then If StringIsInt($aWorksheets[$i][2]) Then ;Figure out what type of formatting this cell requires $sType = "Number" Else $sType = "String" EndIf $sWorksheets &= '<Cell><Data ss:Type="' & $sType & '">' & $aWorksheets[$i][2] & '</Data></Cell>' & @CRLF EndIf ;Only write out Column D if there is enough items in the main array to contain it If ($iLastArray > 3) and ($aWorksheets[$i][3] >= 0) Then If StringIsInt($aWorksheets[$i][3]) Then $sType = "Number" Else $sType = "String" EndIf $sWorksheets &= '<Cell><Data ss:Type="' & $sType & '">' & $aWorksheets[$i][3] & '</Data></Cell>' & @CRLF EndIf ;Only write out Column E if there is enough items in the main array to contain it If ($iLastArray > 4) and ($aWorksheets[$i][4] >= 0) Then If StringIsInt($aWorksheets[$i][4]) Then $sType = "Number" Else $sType = "String" EndIf $sWorksheets &= '<Cell><Data ss:Type="' & $sType & '">' & $aWorksheets[$i][4] & '</Data></Cell>' & @CRLF EndIf ;Only write out Column F if there is enough items in the main array to contain it If ($iLastArray > 5) and ($aWorksheets[$i][5] >= 0) Then If StringIsInt($aWorksheets[$i][5]) Then $sType = "Number" Else $sType = "String" EndIf $sWorksheets &= '<Cell><Data ss:Type="' & $sType & '">' & $aWorksheets[$i][5] & '</Data></Cell>' & @CRLF EndIf $sWorksheets &= '</Row>' & @CRLF EndIf Next ;Write out the footer info for the first Overview worksheet $sWorksheets &= '</Table>' & @CRLF & _ '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">' & @CRLF & _ '<PageSetup>' & @CRLF & _ '<Header x:Margin="0.3"/>' & @CRLF & _ '<Footer x:Margin="0.3"/>' & @CRLF & _ '<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>' & @CRLF & _ '</PageSetup>' & @CRLF & _ '<Selected/>' & @CRLF & _ '<FreezePanes/>' & @CRLF & _ '<FrozenNoSplit/>' & @CRLF & _ '<SplitHorizontal>1</SplitHorizontal>' & @CRLF & _ ;Split the top row so it's stationary when scrolling down '<TopRowBottomPane>1</TopRowBottomPane>' & @CRLF & _ '<ActivePane>2</ActivePane>' & @CRLF & _ '<Panes>' & @CRLF & _ '<Pane>' & @CRLF & _ '<Number>3</Number>' & @CRLF & _ '</Pane>' & @CRLF & _ '<Pane>' & @CRLF & _ '<Number>2</Number>' & @CRLF & _ '<ActiveRow>0</ActiveRow>' & @CRLF & _ '</Pane>' & @CRLF & _ '</Panes>' & @CRLF & _ '<ProtectObjects>False</ProtectObjects>' & @CRLF & _ '<ProtectScenarios>False</ProtectScenarios>' & @CRLF & _ '</WorksheetOptions>' & @CRLF & _ '</Worksheet>' & @CRLF ;Create all of the remaining worksheets For $i = 1 to UBound($aWorksheets) - 1 ;Skip 0 since that will be the Overview worksheet If IsArray($aWorksheets[$i][$iLastArray]) Then ;Only continue building this worksheet if the last item in main Array has data $aWorksheetData = $aWorksheets[$i][$iLastArray] ;Pull out the worksheets array data from the main Array ;Name the worksheet using the value from Column B in the Overview worksheet ;Set the dropdown filter on the used columns of this worksheet ;Again, set the Expanded Column Count to the max used on all worksheets ;Set the row count to the total that will be used in this worksheet $sWorksheets &= '<Worksheet ss:Name="' & $aWorksheets[$i][1] & '">' & @CRLF & _ '<Names>' & @CRLF & _ '<NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=''' & $aWorksheets[$i][1] & '''!R1C1:R2C' & $iMaxColumns & '" ss:Hidden="1"/>' & @CRLF & _ '</Names>' & @CRLF & _ '<Table ss:ExpandedColumnCount="' & $iMaxColumns & '" ss:ExpandedRowCount="' & UBound($aWorksheetData) & '" x:FullColumns="1" x:FullRows="1" ss:StyleID="s17" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="15.75">' & @CRLF ;Format the width of the columns depending on the [0][#] data For $c = 0 to UBound($aWorksheetData, 2) - 1 $sWorksheets &= '<Column ss:AutoFitWidth="0" ss:Width="' & $aWorksheetData[0][$c] & '"/>' & @CRLF Next For $r = 1 to UBound($aWorksheetData) - 1 If $r = 1 Then ;If this is the first row, then it's Header data so use differently $sWorksheets &= '<Row ss:StyleID="s18">' & @CRLF For $c = 0 to UBound($aWorksheetData, 2) - 1 If $r = 1 and $c = 0 Then ;If this is the first Cell in the First Column "A1" then give it a hyperlink back to the overview worksheet if $aWorksheetData[$r][$c] <> "" Then $sWorksheets &= '<Cell ss:StyleID="s19" ss:HRef="#''Overview''!B' & $i & '" x:HRefScreenTip="Return to Overview"><Data ss:Type="String">' & $aWorksheetData[$r][$c] & '</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' & @CRLF Else if $aWorksheetData[$r][$c] <> "" Then $sWorksheets &= '<Cell><Data ss:Type="String">' & $aWorksheetData[$r][$c] & '</Data></Cell>' & @CRLF EndIf Next $sWorksheets &= '</Row>' & @CRLF Else $sWorksheets &= '<Row>' & @CRLF ;Create a new row For $c = 0 to UBound($aWorksheetData, 2) - 1 ;Populate each column in the new row with data $sWorksheets &= '<Cell><Data ss:Type="String">' & $aWorksheetData[$r][$c] & '</Data></Cell>' & @CRLF Next $sWorksheets &= '</Row>' & @CRLF EndIf Next ;All data should be in the worksheet now so add the footer data to it $sWorksheets &= '</Table>' & @CRLF & _ '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">' & @CRLF & _ '<PageSetup>' & @CRLF & _ '<Header x:Margin="0.3"/>' & @CRLF & _ '<Footer x:Margin="0.3"/>' & @CRLF & _ '<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>' & @CRLF & _ '</PageSetup>' & @CRLF & _ '<Print>' & @CRLF & _ '<ValidPrinterInfo/>' & @CRLF & _ '<HorizontalResolution>600</HorizontalResolution>' & @CRLF & _ '<VerticalResolution>600</VerticalResolution>' & @CRLF & _ '</Print>' & @CRLF & _ '<Zoom>90</Zoom>' & @CRLF & _ '<FreezePanes/>' & @CRLF & _ '<SplitHorizontal>1</SplitHorizontal>' & @CRLF & _ '<TopRowBottomPane>1</TopRowBottomPane>' & @CRLF & _ '<ActivePane>2</ActivePane>' & @CRLF & _ '<Panes>' & @CRLF & _ '<Pane>' & @CRLF & _ '<Number>3</Number>' & @CRLF & _ '</Pane>' & @CRLF & _ '<Pane>' & @CRLF & _ '<Number>2</Number>' & @CRLF & _ '<ActiveRow>0</ActiveRow>' & @CRLF & _ '</Pane>' & @CRLF & _ '</Panes>' & @CRLF & _ '<ProtectObjects>False</ProtectObjects>' & @CRLF & _ '<ProtectScenarios>False</ProtectScenarios>' & @CRLF & _ '</WorksheetOptions>' & @CRLF & _ '<AutoFilter x:Range="R1C1:R2C' & UBound($aWorksheetData, 2) & '" xmlns="urn:schemas-microsoft-com:office:excel">' & @CRLF & _ '</AutoFilter>' & @CRLF & _ '</Worksheet>' & @CRLF EndIf Next $sWorksheets &= @CRLF & '</Workbook>' ;Add the closing for the entire xml workbook ;Create the temp .xml file that will get opened, then converted to the final .xlsx If $ConvertToXLSX = True Then ;See if we should convert this .xml into .xlsx $sTmpFile = "C:\TEMP\" & @UserName & "_" & @YDAY & @HOUR & @MIN & @SEC & @MSEC & ".xml" If FileWrite($sTmpFile, $sWorksheets) Then ;Write the full string to the .xml temp file $oXL = _Excel_Open(False, False, False, True, True) ;Create the Excel Com object $oWorkbook = _Excel_BookOpen($oXL, $sTmpFile, True) ;Open the huge .xml file If _Excel_BookSaveAs($oWorkbook, $sOutputFile, $xlOpenXMLWorkbook, True) Then ;Save the .xml file as .xlsx so it will be compressed into the new format FileDelete($sTmpFile) ;If the Save As was successful, Delete the Temp .mlfile Else MsgBox(0, "Error", "Unable to convert temp file to .xlsx, temp file remains " & $sTmpFile) EndIf _Excel_BookClose($oWorkbook, False) ;Close the newly created .xlsx file _Excel_Close($oXL, False) ;Disconnect from the Excel COM object MsgBox(0, "Finished", "Output report saved as: " & $sOutputFile) Else MsgBox(0, "Error", "Unable to save the temp .xml file as " & $sTmpFile) EndIf Else ;Will just leave as .xml so save as the specified file name since it should be .xml If FileWrite($sOutputFile, $sWorksheets) Then ;Write the full string to the .xml file MsgBox(0, "Finished", "Output report saved as: " & $sOutputFile) Else MsgBox(0, "Error", "Unable to save the XML spreadsheet as: " & $sOutputFile) EndIf EndIf EndFunc Func _Build_Sample_Array() ;Build an array we will randomly pull values from to build sample server names Local $aValues[62] $w = 0 For $x = 48 to 57 $aValues[$w] = Chr($x) $w += 1 Next For $y = 65 to 90 $aValues[$w] = Chr($y) $w += 1 Next For $y = 97 to 122 $aValues[$w] = Chr($y) $w += 1 Next ;Build the array that will store everything for this report, 0-4 is the Overview worksheet. 5 will store the arrays for the other 58 worksheets Local $aAllWorksheets[60][6] $aAllWorksheets[0][0] = 110 ;Set the Column sizes for the Overview worksheet $aAllWorksheets[0][1] = 120 $aAllWorksheets[0][2] = 180 $aAllWorksheets[0][3] = 110 $aAllWorksheets[0][4] = 90 $aAllWorksheets[1][0] = "Domain" ;Set the header rows for the Overview worksheet $aAllWorksheets[1][1] = "Server" $aAllWorksheets[1][2] = "Total Applications to patch" $aAllWorksheets[1][3] = "Successfully Patched" $aAllWorksheets[1][4] = "Patched Failed" ;Loop used to build the arrays which will contain the data for the individual worksheets For $i = 2 to UBound($aAllWorksheets) - 1 $iFailed = 0 $iPassed = 0 Local $aDBvalues[200][5] ;Make the 2D array that will hold everything $aDBvalues[0][0] = 60 ;[0][#] is used to store the column widths $aDBvalues[0][1] = 120 $aDBvalues[0][2] = 200 $aDBvalues[0][3] = 220 $aDBvalues[0][4] = 160 $aDBvalues[1][0] = "Domain" ;[1][#] is used to store the Header values $aDBvalues[1][1] = "Server" $aDBvalues[1][2] = "Application Name" $aDBvalues[1][3] = "Patch Required" $aDBvalues[1][4] = "Patch Results" $sDomain = "Prod_ADS" $sServerName = "Prd-" & $aValues[Random(0, 61)] & $aValues[Random(0, 61)] & $aValues[Random(0, 61)] & $aValues[Random(0, 61)] & $aValues[Random(0, 61)] & $aValues[Random(0, 61)] & "-" & $i - 2 For $a = 2 to 199 $aDBvalues[$a][0] = $sDomain $aDBvalues[$a][1] = $sServerName $aDBvalues[$a][2] = "App_NameHere" & $a * $i $aDBvalues[$a][3] = "Yes" If IsInt($a / 2) Then $aDBvalues[$a][4] = "Error " & $a - 1 $iFailed += 1 Else $aDBvalues[$a][4] = "Passed" $iPassed += 1 EndIf Next $aAllWorksheets[$i][0] = $sDomain $aAllWorksheets[$i][1] = $sServerName $aAllWorksheets[$i][2] = UBound($aDBvalues) - 2 ;Populate the Total applications to patch $aAllWorksheets[$i][3] = $iPassed ;Populate the Overview worksheet Successfully Patched $aAllWorksheets[$i][4] = $iFailed ;Populate the Overview worksheet Failed to Patch $aAllWorksheets[$i][5] = $aDBvalues ;Save the array that contains the servers detailed worksheet data Next Return $aAllWorksheets EndFunc

For that you have to look in Win32 API SDK or SPY++ see for example https://www.codeproject.com/Tips/1029254/SendMessage-and-PostMessage https://www.codeproject.com/Articles/33459/Spying-Window-Messages-from-the-Inside

Why a so complicated pattern ? Use this : - (?m) multiline option to make ^ match each start of line, - \s* for possible whitespaces, - \R for the ending newline sequence $r = StringRegExpReplace(FileRead("Example.txt"), '(?m)^\s*\R', "") FileWrite("result.txt", $r)