Jump to content

Excel; How to check if cell is blank


Go to solution Solved by Nine,

Recommended Posts

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

 

image.thumb.png.d08b96aeba60580fab49dc910405a1c2.png

Edited by aa2zz6
Link to post
Share on other sites
  • aa2zz6 changed the title to Excel; How to check if cell is blank
  • Solution

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)

 

Link to post
Share on other sites
  • 2 weeks later...

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)

 

Link to post
Share on other sites
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...

Link to post
Share on other sites

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 by water
Fixed the bug as described by Nine in the following post.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2021-07-20 - Version 1.0.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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

Link to post
Share on other sites
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? 

 

 

 

image.thumb.png.d1282d086112779fbed3f8811567817e.png

 

image.png

Link to post
Share on other sites

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)

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...