Searh doc for 7 digit numbers?

[Haugaard]

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

[PsaltyDS]

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.

[Haugaard]

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

[PsaltyDS]

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.

[big_daddy]

@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)

[PsaltyDS]

@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!"

[Haugaard]

@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.

[PsaltyDS]

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")

[Haugaard]

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