Sign in to follow this  
Followers 0
RogueX20

First Program Completed - Where should I clean up?

9 posts in this topic

This stuff is fun. I wish I started to learn years ago.

Finished up a program with the help of members on this site. I thank you all.

I read a lot to get concepts and my head wrapped around all of this and finally decided to put it to use to some stuff that was dumped on a few of us recently at work.

Below is the code that I would like to know how more of you experienced programmers would clean it up. I tested and it does the job, so I am content with that. However if there is cleaning up or ways to make it more efficient I would like to see. That would give me more ideas on when I go to write.

#include <Excel.au3>
#include <Array.au3>

$sFilePath1 = @ScriptDir & "\MainFile.xlsx"
$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist")
    Exit
EndIf


$search = FileFindFirstFile("y*.txt")
$i = 0


If $search = -1 Then
MsgBox(0, "Error", "No files/directories matched the search pattern")
Exit
EndIf

While 1


$file = FileFindNextFile($search)
$fileRead = FileOpen($file, 0)
$line = FileReadLine($fileRead,4)
;testing to see what shows up in the line
;MsgBox(0, "Line read: ", $line)
;ie - Department XYZ has the following charge of 112.00 for Widget purchased on Oct 22, 2010 released to John Doe
$aArray = StringRegExp($line, "Department (.+) has the following charge of (.+) for (.+) purchased on (.+) released to (.+)", 3)
;_ArrayDisplay($aArray)
If @error Then ExitLoop

$i = ($i + 1)
;looking to copy the name of the file and the info in the array into the excel file


_ExcelWriteCell($oExcel, $aArray[1], $i, 1)
_ExcelWriteCell($oExcel, $aArray[2], $i, 2)
_ExcelWriteCell($oExcel, $aArray[3], $i, 3)
_ExcelWriteCell($oExcel, $aArray[4], $i, 4)
_ExcelWriteCell($oExcel, $aArray[0], $i, 5)
_ExcelWriteCell($oExcel, $file, $i, 6)

WEnd

FileClose($search)
FileClose($fileRead)

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

You are using fileopen within your loop, and only closing it when loop is ended

I dont know if any, the concequences of this, butYou may as well take it out all together as the filereadline will open and close the file when a path is given.

EDIT

#include <Excel.au3>
#include <Array.au3>

$sFilePath1 = @ScriptDir & "\MainFile.xlsx"
$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist")
    Exit
EndIf


$search = FileFindFirstFile("y*.txt")
$i = 0


If $search = -1 Then
    MsgBox(0, "Error", "No files/directories matched the search pattern")
    Exit
EndIf

While 1


    $file = FileFindNextFile($search)
    ;$fileRead = FileOpen($file, 0)
    $line = FileReadLine($file, 4)
    ;testing to see what shows up in the line
    ;MsgBox(0, "Line read: ", $line)
    ;ie - Department XYZ has the following charge of 112.00 for Widget purchased on Oct 22, 2010 released to John Doe
    $aArray = StringRegExp($line, "Department (.+) has the following charge of (.+) for (.+) purchased on (.+) released to (.+)", 3)
    ;_ArrayDisplay($aArray)
    If @error Then ExitLoop

    $i = ($i + 1)
    ;looking to copy the name of the file and the info in the array into the excel file


    _ExcelWriteCell($oExcel, $aArray[1], $i, 1)
    _ExcelWriteCell($oExcel, $aArray[2], $i, 2)
    _ExcelWriteCell($oExcel, $aArray[3], $i, 3)
    _ExcelWriteCell($oExcel, $aArray[4], $i, 4)
    _ExcelWriteCell($oExcel, $aArray[0], $i, 5)
    _ExcelWriteCell($oExcel, $file, $i, 6)

WEnd

FileClose($search)
;FileClose($fileRead)

EDIT 2

also, you should have "If @error Then ExitLoop" directly after the line "$file = FileFindNextFile($search)"

so it exits the loop if no more files are found

Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

Thanks John.

Share this post


Link to post
Share on other sites

If you intend to expand upon this project it might be wise to make it more modular and create a function, something like (untested)

#include <Excel.au3>
#include <Array.au3>

$sFilePath1 = @ScriptDir & "\MainFile.xlsx"
$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist")
    Exit
EndIf


$search = FileFindFirstFile("y*.txt")
$i = 0


If $search = -1 Then
    MsgBox(0, "Error", "No files/directories matched the search pattern")
    Exit
EndIf

While 1

    _MyFunc()
    If @error Then ExitLoop

WEnd

FileClose($search)

Func _MyFunc()
    $file = FileFindNextFile($search)

    If @error Then Return SetError(1)

    $line = FileReadLine($file, 4)

    $aArray = StringRegExp($line, "Department (.+) has the following charge of (.+) for (.+) purchased on (.+) released to (.+)", 3)

    $i += 1

    _ExcelWriteCell($oExcel, $aArray[1], $i, 1)
    _ExcelWriteCell($oExcel, $aArray[2], $i, 2)
    _ExcelWriteCell($oExcel, $aArray[3], $i, 3)
    _ExcelWriteCell($oExcel, $aArray[4], $i, 4)
    _ExcelWriteCell($oExcel, $aArray[0], $i, 5)
    _ExcelWriteCell($oExcel, $file, $i, 6)
EndFunc   ;==>_MyFunc

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Maybe you can use _ExcelWriteArray(), something like this (not tested):

_ExcelWriteCell($oExcel, $aArray[1], $i, 1)
    _ExcelWriteCell($oExcel, $aArray[2], $i, 2)
    _ExcelWriteCell($oExcel, $aArray[3], $i, 3)
    _ExcelWriteCell($oExcel, $aArray[4], $i, 4)
    _ExcelWriteCell($oExcel, $aArray[0], $i, 5)
    _ExcelWriteCell($oExcel, $file, $i, 6)

_ExcelWriteArray($oExcel, $i, 1, $aArray, 0, 1)
    _ExcelWriteCell($oExcel, $file, $i, 6)
Edited by Zedna

Share this post


Link to post
Share on other sites

I thought there might be some optimization that could help it. Zedna I also thought there must be a shorter way to get what I want to write to the excel file to happen. Thanks! Will test in a few.

Share this post


Link to post
Share on other sites

Seems to be a limit on how many files it will loop through. Stopped at the same count the 3 times I ran it. 893. Let me check a few things.

Share this post


Link to post
Share on other sites

Problem found. Some of the files do not have the same info in a particular manner. Going to have to put some error checking in the code to skip when it doesn't find that particular string on a particular line.

You've all been a great help. I am getting addicted to this stuff. Better pace myself as there is so much to learn just to be considered "decent".

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0