Solved: ignore files which are blank?

[obriensj]

Hi,

Having some trouble with my range in the below macro.
The problem being that when my macro goes to the folder ("J:\STOCK RECS\Physical Rec\Physical Rec Reports\Physical Rec 2010\110\Leona\Proces)
And opens up all the files , there are some files which are blank. It is these files which is causing a my macro to stop when it reaches
the Range("A7").Select… section
Is there anyway either with the application.filesearch or and if statement with the range section that the macro knows to ignore those empty files?
I thought I could use the file size so if greater than 1 will process but I cant seem to add in the macro.
Any help appreciated as driving me mad!

Thanks




[VBA]Sub process_data()
'
' ProcessData Macro
' Macro recorded 24/03/2010 by obriensj



Application.DisplayAlerts = False

ChDir "H:\Project Documentation\Macros"
Workbooks.Open Filename:="J:\STOCK RECS\Physical Rec - Contact Details & Procedures\Passwords.xls"

With Application.FileSearch
.LookIn = "J:\STOCK RECS\Physical Rec\Physical Rec Reports\Physical Rec 2010\110\Leona\Process"
.Filename = "*det*.csv"

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)

Range("A7").Select
Selection.TextToColumns Destination:=Range("A7"), DataType:=xlFixedWidth, _
OtherChar:="|", FieldInfo:=Array(Array(0, 2), Array(5, 1), Array(8, 1), Array(19, _
1), Array(28, 1), Array(42, 1), Array(75, 1), Array(104, 1), Array(124, 1)), _
TrailingMinusNumbers:=True
Range("C7").Select



Range("H8").Select
ActiveCell.FormulaR1C1 = "WEBSITE"
Range("I8").Select
ActiveCell.FormulaR1C1 = "LOGON"
Range("J8").Select
ActiveCell.FormulaR1C1 = "PASSWORD"
Range("H9").Select

'vlookup bringing in website address

Range("H9").Select
Do Until IsEmpty(ActiveCell.Offset(0, -7))
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-7],[Passwords.xls]Sheet1!C2:C7,3,FALSE)),0,(VLOOKUP(RC[-7],[Passwords.xls]Sheet1!C2:C7,3,FALSE)))"
ActiveCell.Offset(1, 0).Select
Loop

'vlookup bringing in Username

Range("I9").Select
Do Until IsEmpty(ActiveCell.Offset(0, -8))
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-8],[Passwords.xls]Sheet1!C2:C7,5,FALSE)),0,(VLOOKUP(RC[-8],[Passwords.xls]Sheet1!C2:C7,5,FALSE)))"
ActiveCell.Offset(1, 0).Select
Loop

'vlookup bringing in password

Range("J9").Select
Do Until IsEmpty(ActiveCell.Offset(0, -8))
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-9],[Passwords.xls]Sheet1!C2:C7,6,FALSE)),0,(VLOOKUP(RC[-9],[Passwords.xls]Sheet1!C2:C7,6,FALSE)))"
ActiveCell.Offset(1, 0).Select
Loop
Range("A1").Select




ActiveWorkbook.RefreshAll


asatdate = Range("C7")
asatdate2 = Format(Now(), "dd_mmm_yyyy hh_mm")
Filename = asatdate & " " & asatdate2 & ".xls"
ActiveWorkbook.SaveAs Filename:= _
"J:\STOCK RECS\Physical Rec\Physical Rec Reports\Physical Rec 2010\110\Leona\Results\" & Filename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close

Next i
Else
MsgBox "There were no files found."
End If
End With

On Error Resume Next
Kill "J:\STOCK RECS\Physical Rec\Physical Rec Reports\Physical Rec 2010\110\Leona\Process\*.*" ' delete all files in the folder
'RmDir "C:\Users\Ron\Test\" ' delete folder
On Error GoTo 0

ActiveWorkbook.Close

End Sub[/VBA]

[SamT]

Try to avoid using the Select and Activate methods. They cause problems and slow down the code.

[VBA]
If Not Range("A7").Value = "" Then
Range("A7").TextToColumns Destination:=Range("A7"), DataType:=xlFixedWidth, _
OtherChar:="|", FieldInfo:=Array(Array(0, 2), Array(5, 1), Array(8, 1), Array(19, 1), _
Array(28, 1), Array(42, 1), Array(75, 1), Array(104, 1), Array(124, 1)), _
TrailingMinusNumbers:=True
End If
Range("C7").Select
[/VBA]

[obriensj]

Thanks SamT, appreciated. I knew it was something simple, been a long day!