Help with error 53

S

s_smith_iet

Hey,

I am using a macro to open up unknown files from one folder, put them
in another and change the name. I pull out all the necessary data and
then clear everything and close everything.

But what happens is that once there are no more files in the folders
it ends on error 53 reun time error.

What I would like to happen in this code is that if on the first loop
it does not find and files in the folders a message box pops up and
tells the user that there is no data to process.

If there are files once the macro is done it starts another macro.


Please help !!!!

===========================================================================


Do
ChDir "\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data log
trending Version 2.0"



Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by machine)
\P124\TXT")
Set tmp = Workbooks.Add
Set myfiles = f.Files
counter = 1
For Each fc In myfiles
tmp.Sheets(1).Cells(counter, 1).Value = fc.Name
tmp.Sheets(1).Cells(counter, 2).Value = fc.datelastmodified
counter = counter + 1
Next
tmp.Sheets(1).Columns("B:B").EntireColumn.AutoFit
tmp.Sheets(1).Range(Selection, Selection.End(xlToRight)).Select
tmp.Sheets(1).Range(Selection, Selection.End(xlDown)).Select
Set sortrange = Selection

For Count = 1 To 1

Set f2 = fso.GetFile("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by machine)
\P124\TXT\" & tmp.Sheets(1).Cells(Count, 1).Value)

f2.Move ("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by machine)
\P124\Known txt files\" & tmp.Sheets(1).Cells(Count, 1).Value)

Next Count

tmp.Close False

Set tmp = Nothing

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by machine)
\P124\DAT")
Set tmp = Workbooks.Add
Set myfiles = f.Files
counter = 1
For Each fc In myfiles
tmp.Sheets(1).Cells(counter, 1).Value = fc.Name
tmp.Sheets(1).Cells(counter, 2).Value = fc.datelastmodified
counter = counter + 1
Next
tmp.Sheets(1).Columns("B:B").EntireColumn.AutoFit
tmp.Sheets(1).Range(Selection, Selection.End(xlToRight)).Select
tmp.Sheets(1).Range(Selection, Selection.End(xlDown)).Select
Set sortrange = Selection

For Count = 1 To 1

Set f2 = fso.GetFile("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by machine)
\P124\DAT\" & tmp.Sheets(1).Cells(Count, 1).Value)

f2.Move ("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by machine)
\P124\Known dat files\" & tmp.Sheets(1).Cells(Count, 1).Value)

Next Count

tmp.Close False

Set tmp = Nothing

s1 = "\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data log
trending Version 2.0\Data log files (by machine)\P124\Known txt files
\"
sName = Dir(s1 & "*.*")
i = 0
Do While sName <> ""
i = i + 1
Name s1 & sName As s1 & i & ".txt"
sName = Dir()
Loop
s1 = "\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data log
trending Version 2.0\Data log files (by machine)\P124\Known dat files
\"
sName = Dir(s1 & "*.*")
i = 0
Do While sName <> ""
i = i + 1
Name s1 & sName As s1 & i & ".dat"
sName = Dir()
Loop



Workbooks.OpenText Filename:= _
"\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data
log trending Version 2.0\Data log files (by machine)\P124\Known txt
files\1.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1))
Cells.Select
Selection.Copy
Windows("code.xls").Activate
Sheets("sheet6").Select
Range("A1").Select
ActiveSheet.Paste
Workbooks("1.txt").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data
log trending Version 2.0\Data log files (by machine)\P124\Known dat
files\1.dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1)
Cells.Select
Selection.Copy
Windows("code.xls").Activate
Sheets("sheet4").Select
Range("A1").Select
ActiveSheet.Paste
Workbooks("1.dat").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.Close

Workbooks("code.xls").Activate
Sheets("Sheet6").Activate
Rows("2:3000").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
Range("B1").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="<", FieldInfo:=Array(1, 3)
Sheets("Sheet1").Activate

Set rng = Cells.Find("Run Number")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("A3").Select
ActiveCell.PasteSpecial



Sheets("Sheet1").Activate

Set rng = Cells.Find(" 038 Loading Recipe File:")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("C3").Select
ActiveCell.PasteSpecial
Sheets("sheet1").Activate

Sheets("sheet1").Activate
Range("E1").Select



Set rng = Cells.Find("034 Data Recording Finished")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, -1).Select
On Error Resume Next
ActiveCell.Offset(-100, 0).Resize(100, 40).Copy
Sheets("sheet3").Activate
Range("A1").Select
ActiveCell.PasteSpecial
On Error GoTo 0

Sheets("sheet3").Activate

Set rng = Cells.Find(" 047 Run Statistics:")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 0).Resize(1, 25).Copy
Sheets("sheet2").Activate
Range("D3").PasteSpecial

Sheets("sheet3").Activate

Set rng = Cells.Find("---A---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("AC3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True



Sheets("sheet3").Activate

Set rng = Cells.Find("---B---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("AG3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True



Sheets("sheet3").Activate

Set rng = Cells.Find("---C---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("Ak3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True




Sheets("sheet3").Activate

Set rng = Cells.Find("---D---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("AO3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True





Sheets("sheet3").Activate

Set rng = Cells.Find("---E---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("AS3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True




Sheets("sheet3").Activate

Set rng = Cells.Find("---F---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("AW3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True





Sheets("sheet3").Activate

Set rng = Cells.Find("---G---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("BA3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True




Sheets("sheet3").Activate

Set rng = Cells.Find("---H---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("Be3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True




Sheets("sheet3").Activate

Set rng = Cells.Find("---I---")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(1, 0).Resize(4, 1).Copy
Sheets("sheet2").Activate
Range("BI3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True


Sheets("Sheet3").Activate

Set rng = Cells.Find(" 063 Bias kWh:")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("BN3").Select
ActiveCell.PasteSpecial


Sheets("Sheet3").Activate

Set rng = Cells.Find(" 091 Bias Ah:")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("BO3").Select
ActiveCell.PasteSpecial


Sheets("Sheet3").Activate

Set rng = Cells.Find(" 064 Total Number of Arcs:")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("BP3").Select
ActiveCell.PasteSpecial



Sheets("Sheet1").Activate

Set rng = Cells.Find(" 044 Total Run Time (Minutes):")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("BQ3").Select
ActiveCell.PasteSpecial




Sheets("Sheet1").Activate

Set rng = Cells.Find(" 043 Door Open Time (Minutes):")
If Not rng Is Nothing Then
rng.Select
Else
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Sheets("sheet2").Activate
Range("BR3").Select
ActiveCell.PasteSpecial

Sheets("sheet2").Activate
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(Sheet1!R[-2]C:R[1997]C,"" 018 Automated Run
Aborted"")"
Range("BS3").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(Sheet1!R[-2]C[-69]:R[1997]C[-69],"" 001
***ALARM***"")"

With Sheets("sheet1").Range("B:B")
On Error Resume Next
Set oCell = .Find(What:="051", LookAt:=xlPart)
If Not oCell Is Nothing Then
sFirst = oCell.Address
Do
oCell.Offset(0, 1).Copy
Sheets("sheet7").Activate
Range("A1").PasteSpecial
Selection.Insert Shift:=xlDown
Sheets("sheet1").Activate
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <>
sFirst
End If
End With

With Sheets("sheet1").Range("B:B")
On Error Resume Next
Set oCell = .Find(What:="054", LookAt:=xlPart)
If Not oCell Is Nothing Then
sFirst = oCell.Address
Do
oCell.Offset(0, 1).Copy
Sheets("sheet7").Activate
Range("B1").PasteSpecial
Selection.Insert Shift:=xlDown
Sheets("sheet1").Activate
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <>
sFirst
End If

Sheets("sheet7").Activate
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1:B1").Select
Selection.Copy
Application.CutCopyMode = False
Range("A1:B1").Select
Selection.Cut
Sheets("Sheet2").Select
ActiveWindow.LargeScroll ToRight:=3
Range("BZ3").Select
ActiveSheet.Paste
Sheets("Sheet7").Select
Range("A2:B2").Select
Selection.Cut
Sheets("Sheet2").Select
Range("CB3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=6
Range("CD3").Select
Sheets("Sheet7").Select
Range("A3:B3").Select
Selection.Cut
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("CF3").Select
Sheets("Sheet7").Select
Range("A4:B4").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet7").Select
Application.CutCopyMode = False
Selection.Cut
Sheets("Sheet2").Select
Range("CF3").Select
ActiveSheet.Paste
Sheets("Sheet7").Select
Range("A5:B5").Select
Selection.Cut
Sheets("Sheet2").Select
ActiveWindow.SmallScroll ToRight:=2
Range("CH3").Select
ActiveSheet.Paste
Sheets("Sheet7").Select
Range("A6:B6").Select
Selection.Cut
Sheets("Sheet2").Select
Range("CJ3").Select
ActiveSheet.Paste
Range("CI12").Select

End With


Sheets("Sheet6").Select
Range("a1").Select
Selection.Copy
Sheets("Sheet8").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1),
Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1))
ActiveWindow.SmallScroll ToRight:=8
Range("R2").Select
ActiveCell.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
Range("V2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]&"", ""&RC[-5]&"" ""&RC[-4]"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]&"" ""&RC[-3]"
Range("V2:W2").Select
Selection.Copy
Range("Y2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("Z2").Select
ActiveCell.Replace What:="x", Replacement:=":", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="x", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
Range("Z2").Select
Selection.TextToColumns Destination:=Range("Z2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
OtherChar _
:=" ", FieldInfo:=Array(1, 2)
ActiveWindow.SmallScroll ToRight:=2
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]& "" ""&RC[-3]"
Range("AB2").Select
ActiveWindow.SmallScroll ToRight:=5
Range("AB2").Select
Selection.Copy
Range("AC2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("AC2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
OtherChar _
:=" ", FieldInfo:=Array(1, 3)
Selection.Copy
Sheets("Sheet2").Select
ActiveWindow.LargeScroll ToRight:=4
Range("CL3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "mm/dd/yy"
Range("CM9").Select

Workbooks("P124.xls").Activate
Sheets("sheet1").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Workbooks("CODE.xls").Activate
Sheets("sheet2").Activate
Rows("3:3").Select
Selection.Copy
Workbooks("P124.xls").Activate
Sheets("sheet1").Activate
Range("a3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Workbooks("P124.xls").Activate
Sheets("sheet1").Activate


Workbooks("code.xls").Activate
Sheets("sheet8").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet7").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet6").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet4").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet1").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet3").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet2").Select
Range("CL3").Select
Selection.ClearContents
Range("CK3").Select
Selection.ClearContents
Range("CJ3").Select
Selection.ClearContents
Range("CI3").Select
Selection.ClearContents
Range("CH3").Select
Selection.ClearContents
Range("CG3").Select
Selection.ClearContents
Range("CF3").Select
Selection.ClearContents
Range("CE3").Select
Selection.ClearContents
Range("CD3").Select
Selection.ClearContents
Range("CC3").Select
Selection.ClearContents
Range("CB3").Select
Selection.ClearContents
Range("CA3").Select
Selection.ClearContents
Range("BZ3").Select
Selection.ClearContents
Range("BR3").Select
Selection.ClearContents
Range("BQ3").Select
Selection.ClearContents
Range("BP3").Select
Selection.ClearContents
Range("BO3").Select
Selection.ClearContents
Range("BN3").Select
Selection.ClearContents
Range("BL3").Select
Selection.ClearContents
Range("BK3").Select
Selection.ClearContents
Range("BJ3").Select
Selection.ClearContents
Range("BI3").Select
Selection.ClearContents
Range("BH3").Select
Selection.ClearContents
Range("BG3").Select
Selection.ClearContents
Range("BF3").Select
Selection.ClearContents
Range("BE3").Select
Selection.ClearContents
Range("BD3").Select
Selection.ClearContents
Range("BC3").Select
Selection.ClearContents
Range("BB3").Select
Selection.ClearContents
Range("BA3").Select
Selection.ClearContents
Range("AZ3").Select
Selection.ClearContents
Range("AY3").Select
Selection.ClearContents
Range("AX3").Select
Selection.ClearContents
Range("AW3").Select
Selection.ClearContents
Range("AV3").Select
Selection.ClearContents
Range("AU3").Select
Selection.ClearContents
Range("AT3").Select
Selection.ClearContents
Range("AS3").Select
Selection.ClearContents
Range("AR3").Select
Selection.ClearContents
Range("AQ3").Select
Selection.ClearContents
Range("AP3").Select
Selection.ClearContents
Range("AO3").Select
Selection.ClearContents
Range("AN3").Select
Selection.ClearContents
Range("AM3").Select
Selection.ClearContents
Range("AL3").Select
Selection.ClearContents
Range("AK3").Select
Selection.ClearContents
Range("AJ3").Select
Selection.ClearContents
Range("AI3").Select
Selection.ClearContents
Range("AH3").Select
Selection.ClearContents
Range("AG3").Select
Selection.ClearContents
Range("AF3").Select
Selection.ClearContents
Range("AE3").Select
Selection.ClearContents
Range("AD3").Select
Selection.ClearContents
Range("AC3").Select
Selection.ClearContents
Range("AB3").Select
Selection.ClearContents
Range("AA3").Select
Selection.ClearContents
Range("Z3").Select
Selection.ClearContents
Range("Y3").Select
Selection.ClearContents
Range("X3").Select
Selection.ClearContents
Range("W3").Select
Selection.ClearContents
Range("V3").Select
Selection.ClearContents
Range("U3").Select
Selection.ClearContents
Range("T3").Select
Selection.ClearContents
Range("S3").Select
Selection.ClearContents
Range("R3").Select
Selection.ClearContents
Range("Q3").Select
Selection.ClearContents
Range("P3").Select
Selection.ClearContents
Range("O3").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("M3").Select
Selection.ClearContents
Range("L3").Select
Selection.ClearContents
Range("K3").Select
Selection.ClearContents
Range("J3").Select
Selection.ClearContents
Range("I3").Select
Selection.ClearContents
Range("H3").Select
Selection.ClearContents
Range("G3").Select
Selection.ClearContents
Range("F3").Select
Selection.ClearContents
Range("E3").Select
Selection.ClearContents
Range("D3").Select
Selection.ClearContents
Range("C3").Select
Selection.ClearContents
Range("A3").Select
Selection.ClearContents
Range("H10").Select


On Error Resume Next
Kill "\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data
log trending Version 2.0\Data log files (by machine)\P124\Known dat
files\*.dat"
On Error GoTo 0

On Error Resume Next
Kill "\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\Data
log trending Version 2.0\Data log files (by machine)\P124\Known txt
files\*.txt"
On Error GoTo 0



Loop
 
B

Barb Reinhardt

Where specifically is the error?

I did clean up some of the code at the end where you clear contents on a
whole bunch of individual cells. This can be done this way

Sub Test()
Dim mySheet As Worksheet
Dim myRange As Range
Set myWS = ThisWorkbook.Sheets("Sheet2")
Set myRange = Union(myWS.Range("A3"), myWS.Range("H3"),
myWS.Range("C3:BL3"), myWS.Range("BN3:BR3"), myWS.Range("BZ3:CL3"))
myRange.ClearContents

End Sub
 
J

Jean-Yves

Hi,
Insert this statement before your iteration
if myfyles.count= 0 then
msgbox "no file found"
exit sub
end if
 
S

s_smith_iet

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("\\Mascarolinabdc\puball\Newport Precision
\Spreadsheets\Data log trending Version 2.0\Data log files (by
machine)
\P124\TXT")

This is where I first get the error.

I put a on error resume next before it
and a on error goto 0 after it and then it does the same at the next
one.

If possible once this happens the first time I want it to exit the
macro
 
S

s_smith_iet

It doesn't look like that would sort the files.

This works it just goes out on error. I would like to firgure out a
code that would work with this

Is ther such thing as
On error exit sub?
 
G

Gary Keramidas

this should sort them:

Sub test()
Dim fpath As String
Dim fname As String
Dim fName2 As String
Dim y As Long
Dim z As Long
Dim i As Long
Dim j As Long
Dim temp As Variant
Dim datFilesToProcess() As Variant
Dim txtFilesToProcess() As Variant
fpath = "C:\Documents and Settings\Owner\Desktop\Data log trending Version
2.0\"


fname = Dir(fpath & "*.dat") ' determine file to open
Do While fname > ""
ReDim Preserve datFilesToProcess(0 To z)
datFilesToProcess(z) = fname
z = z + 1
fname = Dir()
Loop

fName2 = Dir(fpath & "*.txt") ' determine file to open
Do While fName2 > ""
ReDim Preserve txtFilesToProcess(0 To y)
txtFilesToProcess(y) = fName2
y = y + 1
fName2 = Dir()
Loop

For i = LBound(datFilesToProcess) To UBound(datFilesToProcess) - 1
For j = i + 1 To UBound(datFilesToProcess)
If datFilesToProcess(i) > datFilesToProcess(j) Then
temp = datFilesToProcess(j)
datFilesToProcess(j) = datFilesToProcess(i)
datFilesToProcess(i) = temp
End If
Next j
Next i

For i = LBound(txtFilesToProcess) To UBound(txtFilesToProcess) - 1
For j = i + 1 To UBound(txtFilesToProcess)
If txtFilesToProcess(i) > txtFilesToProcess(j) Then
temp = txtFilesToProcess(j)
datFilesToProcess(j) = txtFilesToProcess(i)
txtFilesToProcess(i) = temp
End If
Next j
Next i
End Sub
 
S

s_smith_iet

Gary,

It isn't working, not changing the names or sorting them.

Am I supposed to add something to this code? Other then changing the
path?
 
S

s_smith_iet

Ok I got it
I used:

On error goto end sub
=========================
My original code
=========================
EndSub:
EndSub

THanks for your help everyone
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deactivate macros but reactivate again once another macro is run 2
VBA for Dependents shortcut menu 0
Saving file with ref. to one cell 0
On error goto specified Line 6
Help merging two VBA codes 2
macro trouble 2
works in 2007 but not in 2010 0
Copy filtered data to sheet 2 1

Top