Ok, I did a dump file list. It had all the files I was expecting, but also
the calling file. I failed to save that subroutine permanently, into the
file.

I copied in your prior debug type code, ran the file until the error
message, then dragged it down to end sub and hit f8, and it went to the
populate subroutine, which suggests it gets stuck just after copying in the
last file, but just before its final consolidation and cleanup phase. Even
though it says it is processing only file 19 of 20 at the bottom left, it
looks like the 20th file numerically (also 20th on the dump files list), so
the former counter probably just hadn't advanced yet. It was so close to
done, I probably cold have just manually restarted the macro at the populate
subroutine in order to have finished up!

I then realized I did not have the dump file code in there, so I made a new
renamed copy of the file, in preparation for copying the dump file code in.
For the heck of it, I tired rerunning the new renamed file first, before
doing so. It ran perfectly!

So, all I have done is rename the file, something I tried a few times
unsuccessfully before, included some macro code that is all commented out
(PLEASE TELL ME HOW THIS CAN MATTER - IT HAS TWICE NOW, THE FIRST TIME IT
ALLOWED THE MACRO TO TELL ME IT COULDN'T FIND THE CALLING FILE, SOMETHING IT
HADN'T DONE BEFORE WHEN IT WOULD JUST TERMINATE PREMATURELY), and did one
dump file list. Does it make sense that any of this could have fixed my
problem?!

PS Though I never got to it, and it might be moot now, I didn't understand
your instruction about commenting out the dumpfilelist within dumpfile list,
then run again?

Thanks!
Dean

"Peter T" <peter_t@discussions wrote in message
...
I'm not really following all this, also not sure why after the code breaks
you can't then uncomment those lines and then step through them.

It sounds like arrFiles contains the name of your open file and hence a
problem when the code tries to open such a file. Not sure how as from what
I
can make out the procedure GetFileName should eliminate.

In the same module add the following and run it

Sub DumpFileList()
Dim dFileCount As Long

GetFileList

ActiveWorkbook.Worksheets.Add
For dFileCount = 0 To UBound(arrFiles)
S = n = InStrRev(S, "\")

Cells(dFileCount + 1, 1) = _
Right(arrFiles(dFileCount), Len(arrFiles(dFileCount)) - _
InStrRev(arrFiles(dFileCount), "\"))
Cells(dFileCount + 1, 2) = arrFiles(dFileCount)

Next

End Sub

Inspect the list of filenames & fullnames, does it include everything you
expect or does it include your open file name.

If that looks OK, run your code again. When it breaks be sure to press
bedbug (not end), drag the yellow arrow all the way down to End sub, press
F8 which should take it back to ImportFiles, drag the yellow cursor down
to
End sub again. In DumpFileList comment out DumpFileList and run again. Are
the two lists the same.

Regards,
Peter T

"Dean" wrote in message
...
I assume that this was to be added to the macro with nothing taken out,
right? If so, this is what happened.

At the point where it usually halts (the 19th file being imported),
instead
it informed me that "outputtemplate.xls" is already open and asked if I
wanted to re-open it. Since "outputtemplate" is the very file that the
macro is inside of, I answered no! I then got a run time error 1004.
Ctrl
G didn't do anything, but I assume it is the same as choosing the debug
button, which I did. The yellow highlight was not in the "for" line but
in
the line after, "workbooks open..., so there was no way I could move it
to
the next uncommented line, except to move it backwards, which, I assume,
makes no sense.

This business of the macro asking for the same file which is already
calling
it has also been a fleeting symptom on this one machine. Yes, it is in
the
folder that the macro is supposedly polling for its contents, so it's not
as
outrageous as when it asks for files that were previously deleted.

Just to be sure, I reran the template twice without your changes and,
neither time, did it produce this dialog box asking if I wanted to
re-open
the calling file. I am not the sharpest knife in the drawer but,
considering that everything we added was commented out, that seems pretty
odd, don't you think?

Thanks a lot!
Dean


"Peter T" <peter_t@discussions wrote in message
...
Add the following after the For dFileCount = 0 line

For dFileCount = 0 To UBound(arrFiles)
' Application.ScreenUpdating = true
' Debug.Print Err.Number; Err.Description
' Debug.Print "dFileCount ", dFileCount
' Debug.Print "UBound(arrFiles) ", UBound(arrFiles)
' If dFileCount <= (arrFUBoundiles) Then
' Debug.Print (arrFUBoundiles)
' End If
' Exit Sub

When the code breaks:
- press ctrl-g to open the Immediate (debug) Window
- remove the comments
- drag the yellow cursor down to the first newly uncommented line
- press F8 repeatedly to Step through the code

Are the dubg comments consistent with what you expect, any error
messages
while stepping through.

Regards,
Peter T

"Dean" wrote in message
...
Well, this is where it gets even messier. The author kept revising
the
maco
to try to make the problems go away, with no success. What I sent you
was
his latest version. In this last version, there is no error message -
it
just stops prematurely at the spot where there used to be a file that
was
deleted or renamed. I think this is not materially different from the
original file, as far as the failure modes. It was also just stopping
without finishing, later on, aftre i deleted the renamed file. But,
in
terms of the original macro, when it first bombed out, after I renamed
the
file but before I chose to delete the renamed file, it said it could
not
find the file I had deleted, and when I hit debug, the yellow
backgorund
hihglight was at what is now:

For dFileCount = 0 To UBound(arrFiles)

If you can stomach to read on, here is the exact subroutine, as it was
then.
The actual line hihglighted is the very first line: For d = 0 To
UBound(arrFiles).

Public Sub ImportFiles()
For d = 0 To UBound(arrFiles)
Application.ScreenUpdating = False
If arrFiles(d) < Empty Then
GetTheDate (arrFiles(d))
Workbooks.Open (arrFiles(d))
Application.StatusBar = "Processing file " & d + 1 & ": "
&
arrFiles(d)
sDF = ActiveWorkbook.Name
For s = 1 To ActiveWorkbook.Sheets.Count
Sheets(s).Select
sSheet = ActiveSheet.Name
If sSheet = "lcg" Or sSheet = "LCG" Or _
sSheet = "lcv" Or sSheet = "LCV" Or _
sSheet = "mcg" Or sSheet = "MCG" Or _
sSheet = "mcv" Or sSheet = "MCV" Or _
sSheet = "scg" Or sSheet = "SCG" Or _
sSheet = "scv" Or sSheet = "SCV" Then
Cells.Select
Selection.MergeCells = False
Range("A4").Select

dRowCount = ActiveSheet.UsedRange.Rows.Count
Range("A4:A" & dRowCount).Select
Selection.Copy
Workbooks(sOT).Activate
Sheets(sSheet).Select
If Range("E4").Value = Empty Then
Range("E4").Select
End If
dStart = ActiveCell.Row
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, -2).Select
Workbooks(sDF).Activate
Range("A4").Select
Range("C4:C" & dRowCount & ",D4:D" &
dRowCount).Select
Selection.Copy
Workbooks(sOT).Activate
Selection.PasteSpecial Paste:=xlPasteValues
Selection.End(xlDown).Offset(1, 2).Select
Range("A" & dStart & ":A" & ActiveCell.Offset(-1,
0).Row).Value = sDate
Workbooks(sDF).Activate
End If
Next
Application.DisplayAlerts = False
Workbooks(sDF).Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
Next
Application.StatusBar = False
End Sub

Thnaks!
Dean

"Peter T" <peter_t@discussions wrote in message
...
At a quick glance of the code there are various scenarios that might
error.

Which line does the code stop on, if necessary press Ctrl-Break when
you
get
the get the error message.

Regards,
Peter T

"Dean" wrote in message
...
Peter T has requested the code for the macros in my problematic
template,
to
help figure out why one computer has a problem with it. Keep in
mind
that
it only doesn't work on one machine. The output file macro
bascially
looks
for all the files in the same folder as it, counts the files (I
think),
and
extracts the date from the input filenames (usually somehting like
"all
portfolio data - 05-31-06.xls") and also copies and pastes some
data
from
them into the output template from whihc the macro is called.

Right now, the macro always stops at file #19, even though there
are
usually
21 and 22 input files in my small test sample. No error message,
it
just
stops prior to processing the last couple of files, plus some
final
overhead, and apparently quits. Originally, the 19th file was a
file
that
turned out to have a weird filename that the macro could not
extract
the
date from. So I renamed it into a format that was simialr to the
other
fiels that were accepted. When I did this and reran the macro, it
crashed,
saying it couldn't find a file with the old filename, the one it
didn't
like, the one that I renamed. To be safe, I copied in a fresh
version
of
the output template file into the same folder and tried again.
But
the
error message was the same. So, I chose to simply delete the
(renamed)
input file that had been giving me the problem. As I said, now,
each
time
I
attempt to run it on my one best computer, it simply stops
prematurely.
Other, lesser, computers don't seem to have this problem. I tried
putting
it all in a new folder - nothing helped.

Since I always run with a fresh copy of the template, I cannot
fathom
how
it
can seemingly remember that some old filename, or old file, is now
not
included. It's supposed to find what files are in the same folder
when
you
run the macro. I'ts not supposed to already know what they might
be!

Thanks! Here is all the macro, done by someone skilled, someone
who
is
now
perplexed. It runs fine on his machine, as it does on my other
machines,
just not on my main computer. No macro buttons or toolbars are
involved.

Option Explicit

Public sPath As String, sAppName As String, sFileName As String,
sData
As
String
Public sSheet As String, sDate As String
Public sShares As String, sPrice As String, sTicker As String
Public FS
Public arrFiles, arrData
Public dFileCount As Double, dRowCount As Double, dSheets As Double
Public dPF As Double

Public Sub ImportFiles()
GetFileList
ProcessFiles
PopulateTemplate
SortByDate
End Sub

Public Sub GetFileList()
sPath = ActiveWorkbook.Path & "\"
sAppName = ActiveWorkbook.Name
If IsDim(arrFiles) = True Then arrFiles = Empty
If IsDim(arrData) = True Then arrData = Empty

Set FS = Application.FileSearch
With FS
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
ReDim arrFiles(0)
For dFileCount = 1 To .FoundFiles.Count
GetFileName (.FoundFiles(dFileCount))
If sFileName < ActiveWorkbook.Name Then
arrFiles(dFileCount - 1) =
.FoundFiles(dFileCount)
ReDim Preserve arrFiles(UBound(arrFiles) + 1)
End If
Next
Else
MsgBox "No files found in " & sPath & " or its
sub-folders."
End
End If
End With
If IsEmpty(arrFiles(UBound(arrFiles))) = True Then
ReDim Preserve arrFiles(UBound(arrFiles) - 1)
End If
End Sub

Public Sub ProcessFiles()
Application.ScreenUpdating = False
For dFileCount = 0 To UBound(arrFiles)
Workbooks.Open (arrFiles(dFileCount))
GetFileName (arrFiles(dFileCount))
GetFileDate
Application.StatusBar = "Processing file " & dFileCount & "
:
"
&
sFileName

For dSheets = 1 To Workbooks(sFileName).Sheets.Count
Sheets(dSheets).Select
If CheckSheetName = True Then
dRowCount = ActiveSheet.UsedRange.Rows.Count
Range("A4").Select
For dPF = 0 To dRowCount - 3
If ActiveCell.Offset(dPF, 0).Value < Empty And
IsNumeric(ActiveCell.Offset(dPF, 0).Value) = False Then
If dPF = 0 And IsDim(arrData) = False Then
ReDim arrData(0)
Else
ReDim Preserve arrData(UBound(arrData)
+
1)
End If
sData = UCase(sSheet) & ";" & sDate & ";" &
_
ActiveCell.Offset(dPF, 0).Value & ";" &
_
ActiveCell.Offset(dPF, 2).Value & ";" &
_
ActiveCell.Offset(dPF, 3).Value
arrData(UBound(arrData)) = sData
End If
Next
End If
If IsDim(arrData) = True Then
If IsEmpty(arrData(UBound(arrData))) = True Then
ReDim Preserve arrData(UBound(arrData) - 1)
End If
End If
Next
Application.DisplayAlerts = False
Workbooks(sFileName).Close
Application.DisplayAlerts = True
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Public Sub PopulateTemplate()
Application.ScreenUpdating = False
For dPF = 0 To UBound(arrData)
Application.StatusBar = "Populating template. Please
wait...
"
&
dPF & " of " & UBound(arrData)
SplitVariables (arrData(dPF))
Sheets(sSheet).Select
Range("A4").Select
If ActiveCell.Value < "" Then
If ActiveCell.Offset(1, 0).Value = "" Then
ActiveCell.Offset(1, 0).Select
Else
Selection.End(xlDown).Offset(1, 0).Select
End If
End If
ActiveCell.Value = sDate
ActiveCell.Offset(0, 2).Value = sShares
ActiveCell.Offset(0, 3).Value = sPrice
ActiveCell.Offset(0, 4).Value = sTicker
Next
arrData = Empty
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Public Sub GetFileName(TheFile As String)
sFileName = Strings.Replace(TheFile, sPath, "")
Do Until InStr(1, sFileName, "\") = 0
sFileName = Mid(sFileName, InStr(1, sFileName, "\") + 1,
Len(sFileName))
Loop
End Sub

Public Sub GetFileDate()
sDate = Strings.Replace(sFileName, ".xls", "")
sDate = Right(sDate, 10)
Do Until IsNumeric(Mid(sDate, 1, 1)) = True
sDate = Trim(Mid(sDate, 2, Len(sDate)))
Loop
If InStr(1, sDate, "-") = 0 And Len(sDate) = 6 Then
sDate = Mid(sDate, 1, 2) & "-" & Mid(sDate, 3, 2) & "-" &
Mid(sDate,
5, 2)
End If
sDate = Format(sDate, "M/d/yyyy")
If IsDate(sDate) = False Then
MsgBox "The following file does not appear to have a valid
date
in
the filename:" & vbNewLine & vbNewLine & _
sFileName & vbNewLine & vbNewLine & "Resetting this
file.",
vbCritical, "Invalid Date"
ResetWorkbook
End
End If
End Sub

Public Sub SortByDate()
Application.ScreenUpdating = False
For dSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(dSheets).Select
sSheet = ActiveSheet.Name
If CheckSheetName = True Then
GetRowCount
Range("A4:E" & dRowCount).Select
Selection.Sort Key1:=Range("A4"),
Order1:=xlDescending,
Key2:=Range("E4") _
, Order2:=xlAscending, Header:=xlNo
Range("A4").Select
Range("B4").Formula =
"=vlookup(E4,LOOKUP!C:D,2,FALSE)"
Range("B4").AddComment
Range("B4").Comment.Text Text:="Dean:" & Chr(10) &
"At
the
end, Dean will copy this down as far as he needs to."
End If
Next
Sheets(1).Select
Application.ScreenUpdating = True
End Sub

Public Sub ResetWorkbook()
Application.ScreenUpdating = False

Sheets("LCG").Select
Range("A4:A65536").EntireRow.Delete shift:=xlUp
Sheets("MCG").Select
Range("A4:A65536").EntireRow.Delete shift:=xlUp
Sheets("LCV").Select
Range("A4:A65536").EntireRow.Delete shift:=xlUp
Sheets("MCV").Select
Range("A4:A65536").EntireRow.Delete shift:=xlUp
Sheets("SCG").Select
Range("A4:A65536").EntireRow.Delete shift:=xlUp
Sheets("SCV").Select
Range("A4:A65536").EntireRow.Delete shift:=xlUp
Sheets("LCG").Select

Application.ScreenUpdating = True
End Sub

Public Function CheckSheetName() As Boolean
CheckSheetName = False
sSheet = ActiveSheet.Name
If sSheet = "lcg" Or sSheet = "LCG" Or _
sSheet = "lcv" Or sSheet = "LCV" Or _
sSheet = "mcg" Or sSheet = "MCG" Or _
sSheet = "mcv" Or sSheet = "MCV" Or _
sSheet = "scg" Or sSheet = "SCG" Or _
sSheet = "scv" Or sSheet = "SCV" Then
CheckSheetName = True
End If

End Function

Public Function IsDim(arr As Variant) As Boolean
On Error GoTo errNotDim
Dim d As Double
d = UBound(arr)
IsDim = True
Exit Function

errNotDim:
IsDim = False
End Function

Public Sub SplitVariables(TheString)
Dim arrVars(4), dVar As Double
For dVar = 0 To 3
arrVars(dVar) = Mid(TheString, 1, InStr(1, TheString,
";") -
1)
TheString = Mid(TheString, InStr(1, TheString, ";") + 1,
Len(TheString))
Next
arrVars(4) = TheString
sSheet = arrVars(0)
sDate = arrVars(1)
sTicker = arrVars(2)
sShares = arrVars(3)
sPrice = arrVars(4)
End Sub

Public Sub GetRowCount()
Range("A4").Select
If ActiveCell.Value < "" Then
If ActiveCell.Offset(1, 0).Value = "" Then
dRowCount = ActiveCell.Row
Else
Selection.End(xlDown).Select
dRowCount = ActiveCell.Row
End If
End If
Range("A4").Select
End Sub




"Dean" wrote in message
...
I have a template that basically uses a macro to copy and paste
from
all
input files that are placed in its same folder and paste that
stuff
into
itself. It also extracts the dates from the end of each of the
filenames.
I ran it with a lot of files there and it, basically, worked. I
know
that
it does not know the names of the files that will be there, in
advance.

Then, I start over with a fresh template and try to run it again
with
many
of the files removed from the folder, and VB gives me an error
message
telling me it can't find some of the removed files. Someone says
it's
some sort of caching, apparently. I don't understand. Is there
some
way
to clear EXCEL caches? How would a fresh copy know of files that
it,
presumably, has never seen?

Let me know if I need to show you the macro, which was created by
somebody
else. I hope not because it seems that my question is more basic

Thanks!
Dean