Jump to content

First Program Completed - Where should I clean up?


Recommended Posts

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)
Link to comment
Share on other sites

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.

Link to comment
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.

Link to comment
Share on other sites

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
Link to comment
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".

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...