Haugaard Posted December 17, 2007 Share Posted December 17, 2007 I need to searh a doc fiel for seven digit numbers and copy all of them to a excel sheet. I have no real ide on how to do this task. The lines look like this: 374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- And only 7696917 is needed Link to comment Share on other sites More sharing options...
PsaltyDS Posted December 17, 2007 Share Posted December 17, 2007 I need to searh a doc fiel for seven digit numbers and copy all of them to a excel sheet. I have no real ide on how to do this task. The lines look like this: 374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- And only 7696917 is needed Like this with StringRegExp: #include <array.au3> $sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _ "374 3130 1234567 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _ "374 3130 0987654 B+C DS RYGNINGAFSLUTNING START 1,000- " $avFinds = StringRegExp($sString, "\d{7}", 3) If @error Then MsgBox(16, "Error", "@error = " & @error) Else _ArrayDisplay($avFinds, "Results") EndIf I modifies your input string to show getting multiple results. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Haugaard Posted December 17, 2007 Author Share Posted December 17, 2007 (edited) Like this with StringRegExp: #include <array.au3> $sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _ "374 3130 1234567 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _ "374 3130 0987654 B+C DS RYGNINGAFSLUTNING START 1,000- " $avFinds = StringRegExp($sString, "\d{7}", 3) If @error Then MsgBox(16, "Error", "@error = " & @error) Else _ArrayDisplay($avFinds, "Results") EndIf I modifies your input string to show getting multiple results. That was i wanted but cant i use a file instead of writeing it into the script? couse its 135 pages in word i have to edit. And when its C/P to a excel sheet it allso give me the [0]7696917 So it would be better if it could just write it directly to a excel sheet Edited December 17, 2007 by Haugaard Link to comment Share on other sites More sharing options...
PsaltyDS Posted December 17, 2007 Share Posted December 17, 2007 That was i wanted but cant i use a file instead of writeing it into the script? couse its 135 pages in word i have to edit. And when its C/P to a excel sheet it allso give me the [0]7696917 So it would be better if it could just write it directly to a excel sheet $sString is only there for demo purposes. Of course you could just FileRead() the data. You don't even have to save it to a variable first, since AutoIt supports nested functions: #include <ExcelCOM_UDF.au3> $sTxtFile = "C:\Temp\Test.txt" $sExcelFile = "C:\Temp\Test.xls" ; Create txt file for demo $sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _ "374 3130 1234567 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _ "374 3130 0987654 B+C DS RYGNINGAFSLUTNING START 1,000- " $hFile = FileOpen($sTxtFile, 2) FileWrite($hFile, $sString) FileClose($hFile) ; Get all 7-digit numbers from file $avFinds = StringRegExp(FileRead($sTxtFile), "\d{7}", 3) ; Create Excel spreadsheet and save data $oXL = _ExcelBookNew() _ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0)oÝ÷ ØêÞ½éàyé"z¶®¶sc²7&VFRW6VÂ7&VG6VWBæBF7ÆFF¢b33c¶õÂÒôW6VÄ&öö´æWr¥ôW6VÅw&FT'&b33c¶õÂÂÂÂ7G&æu&VtWfÆU&VBb33c·5GDfÆRÂgV÷C²b3#¶G³wÒgV÷C²Â2 What you give up in doing that is the opportunity for error checking before the values are passed to the next function up in the nested functions. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted December 17, 2007 Moderators Share Posted December 17, 2007 @PsaltyDS - Using FileRead() on a MS Word Document isn't going to give the desired results. Try this... #include <Word.au3> #include <Array.au3> #include <ExcelCOM_UDF.au3> $sWordFile = "C:\Temp\Test.doc" $sExcelFile = "C:\Temp\Test.xls" ; Open MS Word Document $oWordApp = _WordCreate($sWordFile) ; Get reference to the document object $oDoc = _WordDocGetCollection($oWordApp, 0) ; Store document text to a variable $sText = $oDoc.Range.Text ; Close MS Word _WordQuit($oWordApp) ; Get all 7-digit numbers from text $avFinds = StringRegExp($sText, "\d{7}", 3) ; Create Excel spreadsheet and save data $oXL = _ExcelBookNew() _ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0) Link to comment Share on other sites More sharing options...
PsaltyDS Posted December 17, 2007 Share Posted December 17, 2007 @PsaltyDS - Using FileRead() on a MS Word Document isn't going to give the desired results.Good catch, I missed that the source was a Word doc. I tested the demo with the plain text file shown.To quote the noted philosopher, Homer: "Doh!" Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Haugaard Posted December 18, 2007 Author Share Posted December 18, 2007 @PsaltyDS - Using FileRead() on a MS Word Document isn't going to give the desired results. Try this... #include <Word.au3> #include <Array.au3> #include <ExcelCOM_UDF.au3> $sWordFile = "C:\Temp\Test.doc" $sExcelFile = "C:\Temp\Test.xls" ; Open MS Word Document $oWordApp = _WordCreate($sWordFile) ; Get reference to the document object $oDoc = _WordDocGetCollection($oWordApp, 0) ; Store document text to a variable $sText = $oDoc.Range.Text ; Close MS Word _WordQuit($oWordApp) ; Get all 7-digit numbers from text $avFinds = StringRegExp($sText, "\d{7}", 3) ; Create Excel spreadsheet and save data $oXL = _ExcelBookNew() _ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0) This works great but it makes 8 digit numbers down to 7 and post them in the excel sheet. Link to comment Share on other sites More sharing options...
PsaltyDS Posted December 18, 2007 Share Posted December 18, 2007 This works great but it makes 8 digit numbers down to 7 and post them in the excel sheet. That's my mistake, not big_daddy's. The RegExp just needs "\b" before and after the pattern so it only matches between word boundries: #include <array.au3> $sTxtFile = "C:\Temp\Test.txt" $sExcelFile = "C:\Temp\Test.xls" ; Create txt file for demo $sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING 01234567 START 1,000- " & @CRLF & _ "374 3130 1234567 B+C DS RYGNINGAFSLUTNING 012345678 START 1,000- " & @CRLF & _ "374 3130 0987654 B+C DS RYGNINGAFSLUTNING 0123456789 START 1,000- " $hFile = FileOpen($sTxtFile, 2) FileWrite($hFile, $sString) FileClose($hFile) ; Get all 7-digit numbers from file $avFinds = StringRegExp(FileRead($sTxtFile), "\b\d{7}\b", 3) _ArrayDisplay($avFinds, "Debug: $avFinds") Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Haugaard Posted December 18, 2007 Author Share Posted December 18, 2007 (edited) Yearh that fixed it Well heres all the code for the scrip incase someon else needs it or just for learning. This made my 10 days workload in 3 hours, so im realy happy Excel-2-AS400_711A.au2 #include <ExcelCOM_UDF.au3>; Include the function collection $FopenMessage = "Browse for the input Excel file." $Fopen = FileOpenDialog($FopenMessage, @WorkingDir, "Excel file (*.xls)", 1) $oExcel = _ExcelBookOpen($Fopen); Open file ;$iColumn = InputBox("Data", "What column should I use data from?", "1","") $icolumn = 1 For $Cell = 1 To 999991 WinActivate("Microsoft Excel - x805.xls");Activate Excel $exdata = _ExcelReadCell($oExcel, $Cell, $icolumn);Read data If $ExData < 1 Then ExitLoop;Stop script if $data1 is = 9999999 Sleep(1000) WinActivate("Session B - [24 x 80]");Activate AS400 Send($ExData);Write the data from excel Sleep(500) Send("{ENTER}") Sleep(500) MouseClick("Left", 310, 61, 1);Mouseclick AS400 macro Sleep(500) Send("{ENTER}");Confirm use of macro Sleep(3500);Wait for the macro to run, big sleep Next Exit AS400_pctxt-2-excel.au2 #include <Word.au3> #include <Array.au3> #include <ExcelCOM_UDF.au3> $FopenMessage1 = "Browse for the Word example file." $FopenMessage2 = "Browse for the Excel example file." $sWordFile = FileOpenDialog($FopenMessage1, @WorkingDir, "Word example file (*.doc)", 1) $sExcelFile = FileOpenDialog($FopenMessage2, @WorkingDir, "Excel example file (*.xls)", 1) ; Open MS Word Document $oWordApp = _WordCreate($sWordFile) ; Get reference to the document object $oDoc = _WordDocGetCollection($oWordApp, 0) ; Store document text to a variable $sText = $oDoc.Range.Text ; Close MS Word _WordQuit($oWordApp) ; Get all 7-digit numbers from text $avFinds = StringRegExp($sText, "\b\d{7}\b", 3) ; Create Excel spreadsheet and save data $oXL = _ExcelBookNew() _ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0) And thanks to all you guy´s helping me out! Edited December 18, 2007 by Haugaard 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