I am using a macro to open up unknown files from one folder, put them
in another and change the name. what happenes is when the folder runs
out of files it stops the macro mid point. I need to get past this
error with out a msg box or any stoppage.


Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
f2.Move ("C:\Documents and Settings\Owner\Desktop\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("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\DAT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
On Error Resume Next
f2.Move ("C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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



Please help

Exactly where in the code does the code stop? I see that you have On Error
Resume Next that I assume is to cope with following line of code f2.Move
("C:\Documents .....etc. if it is unable to perform the move.

If using the On Error to overcome a problem in lieu of testing for a
condition because testing is not appropriate or whatever, you should insert
On Error Goto 0 after the line of code otherwise all future errors are
ignored and it makes it difficult to ascertain exactly what is failing.

--
Regards,

OssieMac


" wrote:

I am using a macro to open up unknown files from one folder, put them
in another and change the name. what happenes is when the folder runs
out of files it stops the macro mid point. I need to get past this
error with out a msg box or any stoppage.


Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
f2.Move ("C:\Documents and Settings\Owner\Desktop\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("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\DAT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
On Error Resume Next
f2.Move ("C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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



Please help

On Aug 17, 9:34*pm, OssieMac
wrote:
Exactly where in the code does the code stop? I see that you have On Error
Resume Next that I assume is to cope with following line of code f2.Move
("C:\Documents .....etc. if it is unable to perform the move.

If using the On Error to overcome a problem in lieu of testing for a
condition because testing is not appropriate or whatever, you should insert
On Error Goto 0 after the line of code otherwise all future errors are
ignored and it makes it difficult to ascertain exactly what is failing.

--
Regards,

OssieMac



" wrote:
I am using a macro to open up unknown files from one folder, put them
in another and change the name. *what happenes is when the folder runs
out of files it stops the macro mid point. *I need to get past this
error with out a msg box or any stoppage.

Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"

Set fso = CreateObject("Scripting.FileSystemObject")
* * Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop <-------------------------
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
--------------------------------------------------------------------------------------------------------------------------


This is where the code stops
If I put on error goto 0 or on error resume next it doesn't effect the
macro it still ends on error

Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"


Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT
\" <-------------------------------------
& tmp.Sheets(1).Cells(Count, 1).Value)

__________________________________________________ _____________________________________


This is where I get the error

JLGWhiz

I tried what you suggested but I just can't seem to get it to
work....I have no idea where to put it.......

Looking at your original posting, it seems you have the On Error statement in
the wrong place. I think it would work better like this.

On Error Resume Next
Set f2 = fso.GetFile("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\DAT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
On Error GoTo 0

" wrote:

JLGWhiz

I tried what you suggested but I just can't seem to get it to
work....I have no idea where to put it.......

Couple of things to try but first avoid using Count as a variable because it
is a reserved word. Could even be your main problem.

Insert the following line of code before the problem code and then check
what filename is being extracted from the worksheet and at what address it is
attempting to find it.

On Error Goto errorHandler

at the bottom of the sub just before end sub insert the following

Exit Sub 'Prevents this code running unless error sends it here

errorHandler:
MsgBox "Filename is " & tmp.Sheets(1).Cells(Count, 1).Value

MsgBox Sheets(1).Cells(lngCount, 1).Address

End Sub

If there is no value because you have gone past the end of the data in the
worksheet then use
If / then / else / end if and test for tmp.Sheets(1).Cells(Count, 1).Value =
"" (no value) and handle it from there.

--
Regards,

OssieMac


" wrote:

Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"


Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT
\" <-------------------------------------
& tmp.Sheets(1).Cells(Count, 1).Value)

__________________________________________________ _____________________________________


This is where I get the error

Am I correct in assuming that where you have For Count = 1 to 1 that you have
substituted this when trying to test your code and it should actually be as
follows

For Count = 1 to Counter

If so then edit the following code as per the comments because with your
method Count will finish up 1 greater than the number of files because it
gets 1 added to it after being used in the last valid loop. The following
method only adds 1 for each valid loop.

counter = 0 'Initialize to zero instead of 1

For Each fc In myfiles
counter = counter + 1 'set counter before using its value
tmp.Sheets(1).Cells(counter, 1).Value = fc.Name
tmp.Sheets(1).Cells(counter, 2).Value = fc.datelastmodified
'counter = counter + 1 'Remove this line
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 Counter

--
Regards,

OssieMac

Not sure where you are getting the error but you could precede the particular
snippet with an If statement to exclude empty folders:

eample: If Not fc Is Nothing Then
'your code to extract the file data
End If

That way, if there is no file, it will bypass the search.

" wrote:

I am using a macro to open up unknown files from one folder, put them
in another and change the name. what happenes is when the folder runs
out of files it stops the macro mid point. I need to get past this
error with out a msg box or any stoppage.


Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
f2.Move ("C:\Documents and Settings\Owner\Desktop\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("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\DAT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
On Error Resume Next
f2.Move ("C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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



Please help

maybe you could adapt something like this, to determine the files to act on.
then just use lbound and ubound on the arrays.

paste this code in a new module. in the vb editor, click debug then add watch.
in the expression box enter txtfilestoprocess, click ok. do the same for
datfilestoprocess. then set a breakpoint on the last loop statement.
run the code
when it stops, click view and then watch window. expand by clicking the + sign
and see if your filenames are listed correctly.


Sub test()
Dim fpath As String
Dim fname As String
Dim fName2 As String
Dim y As Long
Dim z As Long
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
End Sub



--


Gary


wrote in message
...
I am using a macro to open up unknown files from one folder, put them
in another and change the name. what happenes is when the folder runs
out of files it stops the macro mid point. I need to get past this
error with out a msg box or any stoppage.


Do
ChDir "C:\Documents and Settings\Owner\Desktop\Data log trending
Version 2.0"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\TXT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
f2.Move ("C:\Documents and Settings\Owner\Desktop\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("C:\Documents and Settings\Owner\Desktop
\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("C:\Documents and Settings\Owner\Desktop
\Data log trending Version 2.0\Data log files (by machine)\P124\DAT\"
& tmp.Sheets(1).Cells(Count, 1).Value)
On Error Resume Next
f2.Move ("C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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 = "C:\Documents and Settings\Owner\Desktop\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



Please help