No, I am not saying any of those things.

Disregard my previous answer.

--
Regards,
Tom Ogilvy



"Dean" wrote in message
...
Are you saying that you have seen this kind of behavior with EXCEL VBA
macros and don't really know why it happens, but this is a fix to it? Or
are you saying you see something specific in the macro that is promoting
this?

Can you, in layman's terms, tell me how this happens? I assume something
in VBA is retained that is not really associated with the file (because I
get a fresh template each time).

Dean

"Tom Ogilvy" wrote in message
...
I believe it does have a persistent feature. You normally put in a
newsearch command like this:

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With-- Regards,Tom Ogilvy"Dean" wrote
in message ...
I probably meant a visual basic cache but that may be no less dumb! I'm
sorry Kai - your answer is not clear to me, since I'm a novice at amcros.
I don;'t know if you're claiming that the filenames must be saved
somewhere in the macro, but I don't think they are - it was designed to
work with whatever other files were in the same folder. In fact, I just
ran the macro after closing down EXCEL a few times and it didn't exhibit
this intermittent problem - this time. In any event, I know it will
happen again and I'd like to understand it, so here are two of the subs
in the macro that are, I think, responsible. Tell me if you see anything
that can cause it to, somehow, retain some memory of the input filenames
last time this output template was run, even if you go get a fresh output
template each time (to me, this sounds impossible).

Public Sub GetFileList()
sPath = ActiveWorkbook.Path & "\"
sOT = ActiveWorkbook.Name

Set fs = Application.FileSearch
With fs
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
ReDim arrFiles(.FoundFiles.Count - 1)
For d = 1 To .FoundFiles.Count
arrFiles(d - 1) = .FoundFiles(d)
Next
Else
MsgBox "No files found in " & sPath & " or its sub-folders."
End
End If
End With
End Sub

Public Sub ProcessFiles()
Application.ScreenUpdating = False
For d = 0 To UBound(arrFiles)
sFile = arrFiles(d)
GetTheDate

If sFile < ActiveWorkbook.Name Then
Workbooks.Open (arrFiles(d))
Application.StatusBar = "Processing file: " & d + 1 & " - " &
sFile
For s = 1 To ActiveWorkbook.Sheets.Count
Sheets(s).Select
sSheet = ActiveSheet.Name
dRowCount = ActiveSheet.UsedRange.Rows.Count

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
GetRowCount
If dRowCount = 4 Then
'transfer A
Range("A4:A" & dRowCount).Select
Selection.Copy
Workbooks(sOT).Activate
Sheets(sSheet).Select
SetSheetCounter
Selection.PasteSpecial Paste:=xlPasteValues
Selection.End(xlDown).Offset(1, 0).Select
dEnd = ActiveCell.Row
'transfer C and D
Workbooks(sFile).Activate
Range("C4:D" & dRowCount).Select
Selection.Copy
Workbooks(sOT).Activate
Sheets(sSheet).Select
Range("C" & dStart).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A" & dStart & ":A" & dEnd - 1).Value =
sDate
Workbooks(sFile).Activate
End If
End If
Next
Application.DisplayAlerts = False
Workbooks(sFile).Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

"Kai Uwe Schmidt" wrote in message
...
Hi Dean,

I never heard of an "Excel Cache"(there is only the "Personal
Workbook"). It
looks as if your macro uses a fixed file reference. Look for explicit
file
names in the 'copy & paste'-area (e.g. "set input file =
fso.getfilename
("X:\"...) . To make your macro run, you can either replace file names
in
the code or more simply save new input files with the fixedly
referenced
names.

Best regards,
Kai



"Dean" schrieb im Newsbeitrag
...
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