Excel; How to check if cell is blank

[aa2zz6]

How do I check whether a certain cell or multiple cells are blank? 

 The goal is to loop each excel sheet without having to open , check if a cell W57 is blank , and if it's blank do nothing, else move to another directory

#include <File.au3>


$FileList = _FileListToArray("Z:\Shared\Enterprise File Shares\NEO Assignments\Strasburg Assignments\New Assignments")
If @error = 1 Then
    MsgBox(0, "", "No Files\Folders Found.")
    Exit
EndIf

For $i = 1 To $FileList[0]
    MsgBox(0, $i, $FileList[$i])
Next

 

Edited June 29, 2021 by aa2zz6

[Nine]

Try this :

#include <Excel.au3>
#include <File.au3>

Local $aFile = _FileListToArrayRec(@ScriptDir, "*.xls;*.xlsx", $FLTA_FILES, Default, Default, $FLTAR_FULLPATH)
If @error Then Exit
;_ArrayDisplay($aFile)
Local $oExcel = _Excel_Open(False)
Local $oWB, $sW57
For $i = 1 To $aFile[0]
  $oWB = _Excel_BookOpen($oExcel, $aFile[$i])
  $sW57 = _Excel_RangeRead($oWB, Default, "W57")
  If StringStripWS($sW57, $STR_STRIPLEADING+$STR_STRIPTRAILING) <> "" Then
    ConsoleWrite($aFile[$i] & @CRLF)
  EndIf
  _Excel_BookClose($oWB)
Next
_Excel_Close($oExcel)

 

[aa2zz6]

@Nine This is perfect! Thank you sir

[aa2zz6]

How do I get my script to loop through an array variable $OfficeSearch? 

#include <Excel.au3>
#include <File.au3>

Global $strasburg_Office = @ScriptDir & "\..\../2) Strasburg Office/0_Service_Request/"

Global $OfficeSearch[3] = ["Strasburg", "Lancaster", "Mentor"]


Global $aFile = _FileListToArrayRec(@ScriptDir, "*.xls;*.xlsx", $FLTA_FILES, Default, Default, $FLTAR_FULLPATH)
If @error Then Exit
;_ArrayDisplay($aFile)
Global $oExcel = _Excel_Open(False)
Global $oWB, $sW57
For $i = 1 To $aFile[0]
  $oWB = _Excel_BookOpen($oExcel, $aFile[$i])
  $sW57 = _Excel_RangeRead($oWB, Default, "AA3")

  For $i = 1 To $OfficeSearch[0]
  If StringStripWS($sW57, $STR_STRIPLEADING+$STR_STRIPTRAILING) == "'Mentor" Then
    ConsoleWrite($aFile[$i] & @CRLF)

    Global $sFilName = StringRegExpReplace($aFile[$i], "^.*\\|\..*$", "")
    ;MsgBox(0,"msgbox", $sFilName , 5000)
    FileMove($aFile[$i], $strasburg_Office & $sFilName & ".xls", $FC_NOOVERWRITE)
  EndIf
  Next

  _Excel_BookClose($oWB)
Next
_Excel_Close($oExcel)

 

[Nine]

3 minutes ago, aa2zz6 said:

How do I get my script to loop through an array variable $OfficeSearch? 

What do you mean by that ? I do not understand what you want to achieve with this...

[water]

Change

For $i = 1 To $OfficeSearch[0]

to

For $i = 0 To Ubound($OfficeSearch) - 1

This is needed because your array does not hold the number of array elements in element 0.

Edited July 13, 2021 by water
Fixed the bug as described by Nine in the following post.

[Nine]

@water probably meant :

For $i = 0 To Ubound($OfficeSearch) - 1

And there is a single quote before Mentor which shouldn't be there.  But I would still like to understand what is the end goal of your previous code...

[aa2zz6]

11 minutes ago, Nine said:

@water probably meant :

For $i = 0 To Ubound($OfficeSearch) - 1

And there is a single quote before Mentor which shouldn't be there.  But I would still like to understand what is the end goal of your previous code...

Hey Nine, 

I'm trying to setup a file Manager script so that when excel sheets (See images) get created by our customer service reps in the New Assignments folder  the File Manager script  reads cell AA3  determines which office (Strasburg, Lancaster, Mentor) and moves it to the office folder. What would be the best practice because I'm thinking duplication might be an issue possibly? 

 

 

 

 

[Nine]

Ok if I understand correctly. Based on AA3 you want to move the Excel file to one of the office locations.  Try this (untested): 

#include <Excel.au3>
#include <File.au3>

Global $OfficeDir[3] = ["Full\path\to\Strasburg\", "Full\path\to\Lancaster\", "Full\path\to\Mentor\"]

Global $OfficeSearch[3] = ["Strasburg", "Lancaster", "Mentor"]


Global $aFile = _FileListToArrayRec(@ScriptDir, "*.xls;*.xlsx", $FLTA_FILES, Default, Default, $FLTAR_FULLPATH)
If @error Then Exit
_ArrayDisplay($aFile)
Global $oExcel = _Excel_Open(False)
Global $oWB, $sLocation, $sDrive, $sDir, $sFileName, $sExtension
For $i = 1 To $aFile[0]
  $oWB = _Excel_BookOpen($oExcel, $aFile[$i])
  $sLocation = StringStripWS(_Excel_RangeRead($oWB, Default, "AA3"), $STR_STRIPLEADING+$STR_STRIPTRAILING)
  For $j = 0 To UBound($OfficeSearch) - 1
    If $sLocation = $OfficeSearch[$j] Then
      ConsoleWrite("Moving " & $aFile[$i] & " to " & $OfficeSearch[$j] & @CRLF)
      _PathSplit($aFile[$i], $sDrive, $sDir, $sFileName, $sExtension)
      FileMove($aFile[$i], $OfficeDir[$j] & $sFileName & $sExtension)
      ExitLoop
    EndIf
  Next
   If $j = UBound($OfficeSearch) Then ConsoleWrite("Error on " & $aFile[$i] & @CRLF)
  _Excel_BookClose($oWB)
Next
_Excel_Close($oExcel)