Cyborg5000 Posted March 14, 2023 Posted March 14, 2023 (edited) Unsure what iam doing wrong here, but iam getting data appended to a cell each time a blank data is received in _stringbetween. Although i have tried if @error then and different methods to write to the colloumn as N/A. expandcollapse popup#include <Excel.au3> #include <WinAPIFiles.au3> #include <Array.au3> #include <File.au3> #include <FileConstants.au3> #include <MsgBoxConstants.au3> #include <String.au3> Global $sWorkbookPath = "C:\example.xlsx" ; Replace with your workbook path Global $sWorksheetName = "Sheet1" ; Replace with your worksheet name Global $xlUp = -4162 Global $sFolder = "D:\Test" Global $oExcel = _Excel_Open() ; Open an instance of Excel Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbookPath) ; Open the workbook Global $GNCNAME = "GNC-NAME:" Global $SGE = "SGE:" Global $UGE = "UGE:" Global $ElapsedTime = "Elapsed Time:" Global $OutageDuration = "Outage Duration:" Global $ImpactDuration = "Impact Duration:" Global $ImpactSummary = "Impact Summary:" Global $AffectedCI = "Affected CI:" Global $UpdateCST = "Update(CST):" Global $ReasonforImpact = "Reason for Impact:" Global $RecoveryAction = "Recovery Action:" Global $StartTimeCST = "Start Time (CST):" Global $EndTimeCST = "End Time (CST):" Global $VendorTicket = "Vendor Ticket:" Global $Notification = "Notification:" Global $Resources = "Resources:" Global $GNCCONTACT = "GNC-CONTACT:" Local $hSearch = FileFindFirstFile($sFolder & "\" & "*.txt") ; Check if the search was successful, if not display a message and return False. If $hSearch = -1 Then MsgBox($MB_SYSTEMMODAL, "", "Error: No files/directories matched the search pattern.") ;Return False EndIf ; Assign a Local variable the empty string which will contain the files names found. Local $sFileName = "", $iResult = 0 While 1 $sFileName = FileFindNextFile($hSearch) ; If there is no more file matching the search. If @error Then ExitLoop ; Display the file name. ;$iResult = MsgBox(($MB_OKCANCEL + $MB_SYSTEMMODAL), "", "File: " & $sFileName) ;If $iResult <> $IDOK Then ExitLoop ; If the user clicks on the cancel/close button. ;MsgBox(0, "test", $sFolder & "\" & $sFileName) $tempfolder = "D:\temp" FileCopy($sFolder & "\" & $sFileName, $tempfolder & "\test.txt") Global $tempfolder = "D:\Temp" Local $aFiles = _FileListToArray($tempfolder, "*.txt", $FLTA_FILES) For $i = 0 To UBound($aFiles) - 1 Local $sFilePath = $tempfolder & "\" & $aFiles[$i] Global $sFileContents = FileRead($sFilePath) FileDelete("D:\Temp\String.txt") Call("GNC_NAME_SGE", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) Call("GNC_NAME_UGE", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) Call("SGE_OutageDuration", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) Call("Resources_GNCCONTACT", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) Sleep(1900) Next FileDelete($tempfolder & "\test.txt") FileDelete("D:\Temp\String.txt") WEnd _Excel_BookSave($oWorkbook) ; Save the workbook _Excel_Close($oExcel) ; Close the Excel instance Func GNC_NAME_SGE($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) ;================================================================ GNC NAME - SGE ;If StringInStr($sFileContents, $GNCNAME) And StringInStr($sFileContents, $SGE) Then Local $aMatches = _StringBetween($sFileContents, $GNCNAME, $SGE) If IsArray($aMatches) Then ; String was found between the start and end strings Local $sMatch = $aMatches[0] Sleep(100) $filestring = ("D:\Temp\String.txt") FileWrite($filestring, $sMatch) Local $sFileContents1 = FileRead($filestring) Do Local $sNewContents = StringStripWS($sFileContents1, $STR_STRIPLEADING + $STR_STRIPTRAILING + $STR_STRIPSPACES) Until @extended = 0 FileDelete($filestring) FileWrite($filestring, $sNewContents) $line1 = FileReadLine($filestring, 1) $line2 = FileReadLine($filestring, 2) $line3 = FileReadLine($filestring, 3) ;MsgBox(0, "zvzxc", $line1) Local $sColumnName = "B" ; Replace with your column name Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, "A").Value = "SGE" ;$oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch $oWorksheet.Cells($iLastRow + 1, "R").Value = $sFilePath $oWorksheet.Cells($iLastRow + 1, "B").Value = $line1 $oWorksheet.Cells($iLastRow + 1, "C").Value = $line2 $oWorksheet.Cells($iLastRow + 1, "D").Value = $line3 Sleep(100) FileDelete("D:\Temp\String.txt") EndIf EndFunc ;==>GNC_NAME_SGE Func GNC_NAME_UGE($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) ;================================================================ GNC NAME - UGE Local $aMatches = _StringBetween($sFileContents, $GNCNAME, $UGE) If IsArray($aMatches) Then ; String was found between the start and end strings ; String was found between the start and end strings Local $sMatch = $aMatches[0] Sleep(100) $filestring = ("D:\Temp\String.txt") FileWrite($filestring, $sMatch) Local $sFileContents1 = FileRead($filestring) Do Local $sNewContents = StringStripWS($sFileContents1, $STR_STRIPLEADING + $STR_STRIPTRAILING + $STR_STRIPSPACES) Until @extended = 0 FileDelete($filestring) FileWrite($filestring, $sNewContents) $line1 = FileReadLine($filestring, 1) $line2 = FileReadLine($filestring, 2) $line3 = FileReadLine($filestring, 3) ;MsgBox(0, "zvzxc", $line1) Local $sColumnName = "B" ; Replace with your column name Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, "A").Value = "UGE" ;$oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch ; Add the new data to the next row in the column $oWorksheet.Cells($iLastRow + 1, "R").Value = $sFilePath $oWorksheet.Cells($iLastRow + 1, "B").Value = $line1 $oWorksheet.Cells($iLastRow + 1, "C").Value = $line2 $oWorksheet.Cells($iLastRow + 1, "D").Value = $line3 Sleep(100) FileDelete("D:\Temp\String.txt") EndIf EndFunc ;==>GNC_NAME_UGE Func SGE_OutageDuration($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) ;================================================================ SGE - OutageDuration Local $aMatches = _StringBetween($sFileContents, $SGE, $OutageDuration) If IsArray($aMatches) Then ; String was found between the start and end strings Local $sMatch = $aMatches[0] Local $sColumnName = "E" ; Replace with your column name Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch ; Add the new data to the next row in the column $oWorksheet.Cells($iLastRow + 1, "F").Value = "Outage Duration:" ;Sleep(5000) EndIf EndFunc ;==>SGE_OutageDuration Func Resources_GNCCONTACT($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) ;================================================================ Resources - GNCCONTACT Local $aMatches = _StringBetween($sFileContents, $Resources, $GNCCONTACT) If IsArray($aMatches) Then ; String was found between the start and end strings Local $sMatch = $aMatches[0] Local $sColumnName = "Q" ; ;Replace with your column name Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch ; Add the new data to the next row in the column ;Sleep(5000) EndIf EndFunc ;==>Resources_GNCCONTACT In below function or text "$Resources and $GNCCONTACT" there is sometimes a blank text and iam trying to write NA to "$oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch" Func Resources_GNCCONTACT($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) ;================================================================ Resources - GNCCONTACT Local $aMatches = _StringBetween($sFileContents, $Resources, $GNCCONTACT) If IsArray($aMatches) Then ; String was found between the start and end strings Local $sMatch = $aMatches[0] Local $sColumnName = "Q" Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch ; Add the new data to the next row in the column ;Sleep(5000) EndIf tried as below Func Resources_GNCCONTACT($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook) ;================================================================ Resources - GNCCONTACT Local $aMatches = _StringBetween($sFileContents, $Resources, $GNCCONTACT) If @Error Then Local $sColumnName = "Q" Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = "NA"; Add the new data to the next row in the column else Local $sColumnName = "Q" Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $aMatches ; Add the new data to the next row in the column ;Sleep(5000) EndIf EndFunc ;==>Resources_GNCCONTACT Edited March 14, 2023 by Cyborg5000
ioa747 Posted March 16, 2023 Posted March 16, 2023 (edited) put on top of your script #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 ;-w 5 -w 6 -w 7 at least until comment ; he will point out mistakes assuming you have it installed SciTE4AutoIt3.exe Edited March 16, 2023 by ioa747 corrections SOLVE-SMART 1 I know that I know nothing
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