Move excel files with no data in second row or cell A2 to another folder using VB Script

Hello, I am trying to use VBScript to check multiple excel files in a folder and move them to another folder if cell "A2" is blank. The Header row will always be populated with the fields, but the actual data is not present, meaning the file is blank. Below is the code I have that moves the excel files if they are 5kb or less:

' VBScript.

Option Explicit
Sub ProcessFiles()

'Declare the variables:
Dim FSO, oError, oFile
Dim dtmValue, strDate, Mth, The_Date, TheDate

'Create the file system object for creating folders:
Set FSO = CreateObject("Scripting.FileSystemObject")

'Get current date into variable:
dtmValue = Now()
Mth = Month(dtmValue)
The_Date = Mth&"."&Day(dtmValue)&"."&Year(dtmValue)
TheDate = Right(String(2,"0")&Month(date),2)&"."&Right(String(2,"0")&Day(date),2)&"."&Year(date)

'Use date part functions to create the folder names as required
strDate = "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective"

Wscript.Echo strDate
Wscript.Echo The_Date
Wscript.Echo TheDate

'Set variable for that folder name:
Set oError = FSO.GetFolder("O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective")

For Each oFile In oError.Files
If oFile.Size <= 5120 Then
   'FSO.CopyFile oFile.Path, "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective\Empty Files Archive\" &The_Date& "\", True
    FSO.MoveFile oFile.Path, "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective\Empty Files Archive\" &TheDate& "\"
End If
Next
End Sub
   
Call ProcessFiles


Any help with this would be greatly appreciated.

Thanks!
Branden SmithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Option Explicit
Sub ProcessFiles()

'Declare the variables:
Dim FSO, oError, oFile 
Dim dtmValue, strDate, Mth, The_Date, TheDate
Dim xlApp,xlWB,xlWS

'Create the file system object for creating folders:
Set FSO = CreateObject("Scripting.FileSystemObject")

'Get current date into variable:
dtmValue = Now()
Mth = Month(dtmValue)
The_Date = Mth&"."&Day(dtmValue)&"."&Year(dtmValue)
TheDate = Right(String(2,"0")&Month(date),2)&"."&Right(String(2,"0")&Day(date),2)&"."&Year(date)

'Use date part functions to create the folder names as required
strDate = "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective"

Wscript.Echo strDate
Wscript.Echo The_Date
Wscript.Echo TheDate

'Set variable for that folder name:
Set oError = FSO.GetFolder("O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective")
Set xlApp=CreateObject("Excel.Application")

For Each oFile In oError.Files
	If Left(FSO.GetExtensionName(oFile),3)="xls" Then
		Set xlWB=xlApp.Workbooks.Open(oFile,True,True)
		Set xlWS=xlWB.Sheets(1)
		If xlWS.range("A2").value="" Then
			xlWB.Close False
			FSO.MoveFile oFile.Path, "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective\Empty Files Archive\" &TheDate& "\"			
		End If
	End if
Next
End Sub

Call ProcessFiles

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Branden SmithAuthor Commented:
Thank you Subodh. I have to leave my office for an appointment, but I will be back on this evening to test this, but this seems like what I was looking for. I am a novice to VB Script. I will be back in touch.

Thanks!
0
Branden SmithAuthor Commented:
Hi Subodh, this works to remove the true blank files, but when I click on the blank file in the new folder, it opens along with all of the other files that were in the original 'Prospective' folder that did have data. I have to close all of them. But the blank files are in the new folder. Not quite sure what to do. I also changed this section of the code:
If Left(FSO.GetExtensionName(oFile),4)="xlsx" Then

I changed the 3 to 4 and xls to xlsx and it still seemed to work in the same manner.

Thanks,
Branden
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's something strange. Does it only happen when you run the script?
If so,
1) Close all the excel files before running the script.
2) Once script is run, check the Task Manager to see if any instance of Excel is running.
3) After script it run, reboot the system and then open the file from the new folder and see if it still opens all the files?
0
Branden SmithAuthor Commented:
When I run the script, everything executes fine. It is only when I go to the destination folder that the blank files were moved to and double click to open one, all the files that are not blank and remained in the original folder open as well.

But after I close the blank file and the other non blank files from the original location, I can open the other blank files without triggering other files to open.

All Excel files were closed
The task manager did not show any instance of Excel running
I am going to reboot the system and hen try to open the blank files from the new destination folder.

The person that is running the script will probably not want to reboot in order to open the blank files in the new folder. These files are being opened only to verify that they are indeed blank. We probably will not continue to check after a few successful test runs.

I am rebooting now. I'll be back on in a few minutes.

Thanks Subodh!

Branden
0
Branden SmithAuthor Commented:
When I reboot the machine, only the blank file selected opens. Not sure why I have to reboot for this. Also, these files are always going to be .xlsx. I saw where the code is checking for the file extension.

Thanks,
Branden
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That seems to be a windows explorer issue.
I don't think that the code has something which is causing this strange behavior.
0
Branden SmithAuthor Commented:
Subodh was able to take my code that looked at file size to make a determination to move an excel file from one folder to another in VB Script and change it to check the Cell A2 -which would be the first cell populated with data outside of the header row- to see if it were blank and move it to the new folder location. This was a tremendous time saver for us to be able to move blank files systematically. Thanks again Subodh!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Branden,

Can you please give this a try and see if the issue you have gets resolved?

Option Explicit
Sub ProcessFiles()

'Declare the variables:
Dim FSO, oError, oFile 
Dim dtmValue, strDate, Mth, The_Date, TheDate
Dim xlApp,xlWB,xlWS

'Create the file system object for creating folders:
Set FSO = CreateObject("Scripting.FileSystemObject")

'Get current date into variable:
dtmValue = Now()
Mth = Month(dtmValue)
The_Date = Mth&"."&Day(dtmValue)&"."&Year(dtmValue)
TheDate = Right(String(2,"0")&Month(date),2)&"."&Right(String(2,"0")&Day(date),2)&"."&Year(date)

'Use date part functions to create the folder names as required
strDate = "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective"

Wscript.Echo strDate
Wscript.Echo The_Date
Wscript.Echo TheDate

'Set variable for that folder name:
Set oError = FSO.GetFolder("O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective")
Set xlApp=CreateObject("Excel.Application")

For Each oFile In oError.Files
	If Left(FSO.GetExtensionName(oFile),3)="xls" Then
		Set xlWB=xlApp.Workbooks.Open(oFile,True,True)
		Set xlWS=xlWB.Sheets(1)
		If xlWS.range("A2").value="" Then
			xlWB.Close False
			FSO.MoveFile oFile.Path, "O:\Everyone\Claims\Recovery\Payment Integrity DataMining\Data Mining Files " & Year(dtmValue)& "\" & MonthName(Mth) & "\Prospective\Empty Files Archive\" &TheDate& "\"			
		End If
	End if
Next
Set FSO=Nothing
Set xlWB=Nothing
Set xlApp=Nothing
End Sub

Call ProcessFiles

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.