[Solved] Get the Filenames in Excel cell and find these files in a path then FileMove them

[KickStarter15]

Hi Experts,

Good Day!

I have this question if possible, can autoit get the filename save in excel cell shown below sample screenshot and find it in a specific path then Filemove these files to another path?

I need to get these files then transfer these to another path. Let's say copy from this path "C:\Programs\Folder1\" then FileMove to this path "D:\Programs\Folder2\".

Note: There are other files in that path that should not be included in FileMove(), means only these files declared in my Excel should be FileMove. Is this possible?

 

Thanks in advance, Experts!

KS15

Edited August 25, 2018 by KickStarter15

[FrancescoDiMuro]

@KickStarter15
I tell you that it is possible
Just read all the file names in an array, and loop through, using FileMove() to move them from Source Path to Destination Path  

Edited August 21, 2018 by FrancescoDiMuro

[KickStarter15]

Thanks @FrancescoDiMuro, glad to know it's possible.

I'll check what can I do with array, loop and then filemove, is it okay if I ask help if incase I could not compose a good one? or maybe you can give me a head up and I'm sure this involved Excel UDF and I'm not that good enough to handle this wonderful UDF by water.

 

[FrancescoDiMuro]

@water Made an amazing job with that UDF.
Easy and practical!
By the way, here you are a little hint

Spoiler

expandcollapse popup

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

Global $objExcel, _
       $objWorkbook, _
       $strConfigurationFile = @ScriptDir & "\FilesConfiguration.xlsx", _
       $arrResult, _
       $strSourceFolder = @ScriptDir & "\Source", _
       $strDestinationFolder = @ScriptDir & "\Destination"


$objExcel = _Excel_Open(False)
If @error Then
    ConsoleWrite("Error while getting Excel object. Error: " & @error & @CRLF)
Else
    $objWorkbook = _Excel_BookOpen($objExcel, $strConfigurationFile, False, False)
    If @error Then
        ConsoleWrite("Error while opening the Workbook '" & $strConfigurationFile & "'. Error: " & @error & @CRLF)
    Else
        $arrResult = _Excel_RangeRead($objWorkbook, Default, $objWorkbook.ActiveSheet.UsedRange.Columns("A:A"))
        If @error Then
            ConsoleWrite("Error while reading the specified range in the Workbook '" & $strConfigurationFile & "'. Error: " & @error & @CRLF)
        Else
            ; _ArrayDisplay($arrResult, "Result:")
            If UBound($arrResult) > 1 Then
                For $i = 0 To UBound($arrResult) - 1
                    If FileExists($strSourceFolder & "\" & $arrResult[$i]) Then
                        If FileMove($strSourceFolder & "\" & $arrResult[$i] ,$strDestinationFolder & "\" & $arrResult[$i], $FC_CREATEPATH + $FC_OVERWRITE) Then
                            ConsoleWrite("File " & $arrResult[$i] & " moved successfully from '" & $strSourceFolder & "' to '" & $strDestinationFolder & "'." & @CRLF)
                        Else
                            ConsoleWrite("Error while moving the file  " & $arrResult[$i] & "." & @CRLF)
                        EndIf
                    Else
                        ConsoleWrite("File not found: " & $strSourceFolder & "\" & $arrResult[$i] & "!" & @CRLF)
                    EndIf
                Next

                _Excel_BookClose($objWorkbook)
                If @error Then
                    ConsoleWrite("Error while closing the Workbook " & $strConfigurationFile & ". Error: " & @error & @CRLF)
                Else
                    _Excel_Close($objExcel)
                    If @error Then
                        ConsoleWrite("Error while closing the Excel object. Error: " & @error & @CRLF)
                    Else
                        Exit
                    EndIf
                EndIf
            Else
                ConsoleWrite("There are no files to move." & @CRLF)
            EndIf
        EndIf
    EndIf
EndIf

FileMoveFromExcelConfiguration.zip

Make sure that the configuration file is in the @ScriptDir

Cheers

Edited August 21, 2018 by FrancescoDiMuro

[water]

Please have look at _Excel_Open, _Excel_BookOpen and _Excel_RangeRead. 

If you have any questions - just ask  

[KickStarter15]

Thanks @water, got it. Trying to compose now and hoping to have success.

@FrancescoDiMuro, thanks men.. tried it and having the below error.

 

BTW, to make it more  clearer guys, it's a folder that should be FileMove to another path. Sorry for the confusion my mistake.

[FrancescoDiMuro]

@KickStarter15
So, "ABC12345" is a folder and not a file?

[water]

In this case replace FileMove with DirMove  

[KickStarter15]

15 hours ago, FrancescoDiMuro said:

So, "ABC12345" is a folder and not a file?

Yes, sorry for my first post.

Can't imaging @FrancescoDiMuro, you hit it in one shot. Wow, thank you very much for the help and also to @water thank you as well and yup changing the FileMove() to DirMove() make things easy and working as expected.

 

Thank you so much guys! you save my day.....

[FrancescoDiMuro]

Happy to have helped!

Cheers

[KickStarter15]

@FrancescoDiMuro,

Sorry for asking again...

I just want to ask how to separate each line using _GUICtrlRichEdit_WriteLine(). I used RichEdit function to add color on my edit box when there's no file found from the source path. The code is like the below.

This is the previous line code to show each file being transferred to the destination path.

GUICtrlSetData($Edit1, "File " & $arrResult[$i] & " moved successfully " & "to Destination: '" & $strDestinationFolder & "'." & @CRLF, "1") ; value "1" is to separate each validated output from $arrResult[$i]

 

This is the code that show's the files not being found from the path. I want this line to be in red so that it can be determine easily. The code is working however, it will show in one long line in my editbox.

GUICtrlSetData($Edit1, "File not found: " & $strSourceFolder & "\" & $arrResult[$i] & "!" & @CRLF, "1") ; this line is to show those are not found from the path.

; I change it this way. coloring it red to emphasized that file was not found.
_GUICtrlRichEdit_WriteLine($Edit1, "File not found: " & $strSourceFolder & "\" & $arrResult[$i] & "!" & @CRLF, Default, Default, 0xFF0000)

 

Thanks in advance!

KS15

[FrancescoDiMuro]

@KickStarter15
... Or, you could use built-in _GUICtrlRichEdit_* functions to color your text.
Just change your conditions to make it true, and that's done
 

expandcollapse popup

#include <Color.au3>
#include <GUIConstantsEx.au3>
#include <GuiRichEdit.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1)

Global $arrRGB_Red[3] = [0, 0, 255] ; Seems buggy, since R = 255, G = 0, B = 0 should be red color, indeed is blue

#Region ### START Koda GUI section ### Form=
Global $frmMainForm = GUICreate("A Form", 405, 293, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "ExitApplication")
Global $reRichEdit = _GUICtrlRichEdit_Create($frmMainForm, "", 14, 12, 377, 265)
GUICtrlSetData(-1, "")
PopulateRichEdit()
GUISetState(@SW_SHOW, $frmMainForm)
#EndRegion ### END Koda GUI section ###


While 1
    Sleep(100)
WEnd


Func ExitApplication()
    Exit
EndFunc

Func PopulateRichEdit()

    For $intCounter = 0 To 9 Step 1
        If Mod($intCounter, 2) = 0 Then
            _GUICtrlRichEdit_SetCharColor($reRichEdit, _ColorSetRGB($arrRGB_Red))
            _GUICtrlRichEdit_InsertText($reRichEdit, "File" & $intCounter & " not found." & @CRLF)
        Else
            _GUICtrlRichEdit_InsertText($reRichEdit, "File" & $intCounter & " found." & @CRLF)
        EndIf
    Next

EndFunc

 

[KickStarter15]

@FrancescoDiMuro,

Thanks, but when I applied your code to the below script. Still the same output and this time all were colored with red and was presented in one line in my editbox.

Here's what you given to me in your previous post above. Some code was added and some were replaced.

expandcollapse popup

Func ProcessMe()
   $ExcelBase = GUICtrlRead($Fex) ; this is just reading the input box from GUI where they add the filename of the excel file.
   Global $objExcel, _
          $objWorkbook, _
          $strConfigurationFile = @ScriptDir & "\" & $ExcelBase, _
          $arrResult, _
          $strSourceFolder = $sFileSelectFolder & "\", _
          $strDestinationFolder = $dFileSelectFolder & "\"


   $objExcel = _Excel_Open(False)
   If @error Then
      GUICtrlSetData($Edit1, "Error while getting Excel object. Error: " & @error & @CRLF, "1")
   Else
    $objWorkbook = _Excel_BookOpen($objExcel, $strConfigurationFile, False, False)
    If @error Then
        GUICtrlSetData($Edit1, "Error while opening the Workbook '" & $strConfigurationFile & "'. Error: " & @error & @CRLF, "1")
    Else
        $arrResult = _Excel_RangeRead($objWorkbook, Default, $objWorkbook.ActiveSheet.UsedRange.Columns("A:A"))
        If @error Then
            GUICtrlSetData($Edit1, "Error while reading the specified range in the Workbook '" & $strConfigurationFile & "'. Error: " & @error & @CRLF, "1")
        Else
            ; _ArrayDisplay($arrResult, "Result:")
            If UBound($arrResult) > 1 Then
                For $i = 0 To UBound($arrResult) - 1
                    If FileExists($strSourceFolder & "\" & $arrResult[$i]) Then
                        If DirMove($strSourceFolder & "\" & $arrResult[$i] ,$strDestinationFolder & "\" & $arrResult[$i]) Then
;~                         GUICtrlSetData($Edit1, "File " & $arrResult[$i] & " moved successfully " & "to Destination: '" & $strDestinationFolder & "'." & @CRLF, "1") ; This is my old code...
                           _GUICtrlRichEdit_InsertText($Edit1, "File " & $arrResult[$i] & " moved successfully " & "to Destination: '" & $strDestinationFolder & "'." & @CRLF) ; This is the replacement.

                            $sFilePath = @ScriptDir & "\Log.ini"
                            Local $hFileOpen = FileOpen($sFilePath, 1)
                            If $hFileOpen = -1 Then
                              MsgBox(16, "Error", "An error occurred whilst writing the temporary file.")
                              Return False
                            EndIf

                            FileWrite($hFileOpen, "File " & $arrResult[$i] & " was moved. Processed on: " & @MON &"/"& @MDAY &"/"& @YEAR &" by "& @UserName & @CRLF)
                            FileClose($hFileOpen)
                        Else
                            GUICtrlSetData($Edit1, "Error while moving the file  " & $arrResult[$i] & ". Check storage or file is corrupted!" & @CRLF, "1")
                        EndIf
                    Else
;~                         GUICtrlSetData($Edit1, "File not found: " & $strSourceFolder & "\" & $arrResult[$i] & "!" & @CRLF, "1") ; this is my old code
                        _GUICtrlRichEdit_SetCharColor($Edit1, _ColorSetRGB($arrRGB_Red)); this is the replacement
                        _GUICtrlRichEdit_InsertText($Edit1, "File not found: " & $strSourceFolder & "\" & $arrResult[$i] & "!" & @CRLF); this is the replacement
                    EndIf
                Next

                _Excel_BookClose($objWorkbook)
                MsgBox(64,"Completed","Done processing file/s.")
                If @error Then
                    GUICtrlSetData($Edit1, "Error while closing the Workbook " & $strConfigurationFile & ". Error: " & @error & @CRLF, "1")
                Else
                    _Excel_Close($objExcel)
                    If @error Then
                        GUICtrlSetData($Edit1, "Error while closing the Excel object. Error: " & @error & @CRLF, "1")
                    Else
                       Call("From1")
                    EndIf
                EndIf
            Else
                GUICtrlSetData($Edit1, "There are no files to move." & @CRLF, "1")
            EndIf
        EndIf
    EndIf
 EndIf
EndFunc

 

[FrancescoDiMuro]

@KickStarter15

How are you creating the Edit control?

 

[KickStarter15]

#include <GuiRichEdit.au3>
Global $arrRGB_Red[3] = [0, 0, 255]
$Form1 = GUICreate("Form", 542, 379, 198, 124)
Global $Edit1 = _GUICtrlRichEdit_Create($Form1, "", 24, 112, 433, 249, BitOr($GUI_SS_DEFAULT_EDIT, $ES_READONLY))
; continue code here...

This is the edit control that I have. Followed what you suggested.

[FrancescoDiMuro]

@KickStarter15
Here is the issue:

Global $Edit1 = _GUICtrlRichEdit_Create($Form1, "", 24, 112, 433, 249, BitOr($GUI_SS_DEFAULT_EDIT, $ES_READONLY))

Try to change those lines of code with:

Global $Edit1 = _GUICtrlRichEdit_Create($Form1, "", 24, 112, 433, 249)
_GUICtrlRichEdit_SetReadOnly($Edit1, True)

Cheers

[KickStarter15]

@FrancescoDiMuro, Thanks, you got it. everything is now okay and working. WOWW!!! Thank you so much Francesco, I just added a little "BitOR($ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL)" to enable the scroll bar in my _GUICtrlRichEdit_Create().

Thank you!!!

[FrancescoDiMuro]

@KickStarter15

Happy to have helped buddy

Cheers