obriensj
03-31-2010, 07:51 AM
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
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
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
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