Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
It wasn't that I couldn't comment out the rows, it's just that I didn't
think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
What confuses me is how putting in a bunch of stuff
that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
I am confused by which message you mean by my last message. Perhaps, our
posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
Yes our two recent posts crossed.
Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
I think it is this whole concept of memory leaks that is beyond my simple
intellect! ... that it appears that you can run a macro, then exit the file without saving, and yet, somehow, EXCEL remembers what files were called, and expects to find them again - stuff like that. Is there any URL or thread that I might use to educate myself on this, just conceptually? Unfrotmautely, I did reinstall EXCEL. Can I use system restore to reverse that and, if so, do you think I should bother if all is just as it was before. Isn't a clean copy always a good idea, if you're willing to suffer through the installation? Thanks so much for all your hard work. I'm sorry that there had been so many iterations by the time you saw it! Dean "Peter T" <peter_t@discussions wrote in message ... Yes our two recent posts crossed. Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
There are many reasons for memory leaks, some very hard to track down. In
the case of the code you posted the use of statement is 'End' will cause that particularly with all those global variables and arrays. But I don't think that's not why the code failed. There can be issues with the FileSearch but also don't think relevant here as it starts with NewSearch (I might be wrong). Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). If you've already re-installed Excel don't worry about trying to 'reverse'. Regards, Peter T "Dean" wrote in message ... I think it is this whole concept of memory leaks that is beyond my simple intellect! ... that it appears that you can run a macro, then exit the file without saving, and yet, somehow, EXCEL remembers what files were called, and expects to find them again - stuff like that. Is there any URL or thread that I might use to educate myself on this, just conceptually? Unfrotmautely, I did reinstall EXCEL. Can I use system restore to reverse that and, if so, do you think I should bother if all is just as it was before. Isn't a clean copy always a good idea, if you're willing to suffer through the installation? Thanks so much for all your hard work. I'm sorry that there had been so many iterations by the time you saw it! Dean "Peter T" <peter_t@discussions wrote in message ... Yes our two recent posts crossed. Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
<< Not sure what you mean by "somehow, EXCEL remembers what files were
called" other than use of the globals, variables declared at the top of the module (the code could be written without those). At the expense of belaboring things, I am referring to the fact that I ran the macro and learned that it did not like one of the input file filenames it saw - let's call it "value file1" - I later learned that the programmer didn't allow for just any filename. So, I renamed (didn't copy) the input file to "portfolio file 1", a name that I knew would be compatible, Then, I got a FRESH COPY of the template with the macro in it and reran the macro. It then crashed saying it couldn't find "value file 1". To my simple brain, this should be impossible, unless parts of the macro somehow reach outside the calling file. Dean "Peter T" <peter_t@discussions wrote in message ... There are many reasons for memory leaks, some very hard to track down. In the case of the code you posted the use of statement is 'End' will cause that particularly with all those global variables and arrays. But I don't think that's not why the code failed. There can be issues with the FileSearch but also don't think relevant here as it starts with NewSearch (I might be wrong). Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). If you've already re-installed Excel don't worry about trying to 'reverse'. Regards, Peter T "Dean" wrote in message ... I think it is this whole concept of memory leaks that is beyond my simple intellect! ... that it appears that you can run a macro, then exit the file without saving, and yet, somehow, EXCEL remembers what files were called, and expects to find them again - stuff like that. Is there any URL or thread that I might use to educate myself on this, just conceptually? Unfrotmautely, I did reinstall EXCEL. Can I use system restore to reverse that and, if so, do you think I should bother if all is just as it was before. Isn't a clean copy always a good idea, if you're willing to suffer through the installation? Thanks so much for all your hard work. I'm sorry that there had been so many iterations by the time you saw it! Dean "Peter T" <peter_t@discussions wrote in message ... Yes our two recent posts crossed. Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
Perhaps I didn't pick up on what you re-describe below, it's been difficult
to follow and got distracted by other problems with the code. The only thing I can think of that might explain that scenario is if somehow the app-Filesearch is not correctly clearing an old search particularly in view of the filesearch object being retained as Global between sessions, however I would expect FS.NewSearch to reset. I tried to recreate your overall set-up with a few files and tried to run the original code. However your code was throwing up errors in various places, particularly incorrectly keeping track of arrays. So without spending a lot of time rewriting not possible to get to the bottom of it. However at no time did Filesearch falsely find the oldname of a renamed file. Despite that I'm still slightly suspicious that in your set-up of files Filesearch just might return the old-name of a renamed file. See if you can re-create the problem as before. If so entirely delete or move the rogue renamed file out of the search path. Make a new similar file with same new-name in the search path. Repeat the search, any difference. If the oldname was found first time but recreate the problem again and in Sub GetFileList(), after the line With FS, add the following .MatchTextExactly = True (not sure why that would make a difference but just in case) In passing I can't see any reason the code can't work with "value file1". The function GetFileDate tries to find if the last part of the name is a valid date. "1" is a valid date, namely 12-31-1899. If the name does not contain a trailing valid date the function would loop forever. Regards, Peter T "Dean" wrote in message ... << Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). At the expense of belaboring things, I am referring to the fact that I ran the macro and learned that it did not like one of the input file filenames it saw - let's call it "value file1" - I later learned that the programmer didn't allow for just any filename. So, I renamed (didn't copy) the input file to "portfolio file 1", a name that I knew would be compatible, Then, I got a FRESH COPY of the template with the macro in it and reran the macro. It then crashed saying it couldn't find "value file 1". To my simple brain, this should be impossible, unless parts of the macro somehow reach outside the calling file. Dean "Peter T" <peter_t@discussions wrote in message ... There are many reasons for memory leaks, some very hard to track down. In the case of the code you posted the use of statement is 'End' will cause that particularly with all those global variables and arrays. But I don't think that's not why the code failed. There can be issues with the FileSearch but also don't think relevant here as it starts with NewSearch (I might be wrong). Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). If you've already re-installed Excel don't worry about trying to 'reverse'. Regards, Peter T "Dean" wrote in message ... I think it is this whole concept of memory leaks that is beyond my simple intellect! ... that it appears that you can run a macro, then exit the file without saving, and yet, somehow, EXCEL remembers what files were called, and expects to find them again - stuff like that. Is there any URL or thread that I might use to educate myself on this, just conceptually? Unfrotmautely, I did reinstall EXCEL. Can I use system restore to reverse that and, if so, do you think I should bother if all is just as it was before. Isn't a clean copy always a good idea, if you're willing to suffer through the installation? Thanks so much for all your hard work. I'm sorry that there had been so many iterations by the time you saw it! Dean "Peter T" <peter_t@discussions wrote in message ... Yes our two recent posts crossed. Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
I wasn't intending the value file 1 name to be taken literally - the issue
seemed to be that the filename started with the word value, not that this might change your answer. And regardless of why I changed the filename, I did change it, and the file kept looking for the old input filename. Then, when I deleted the file altogether, the macro would not finish. The author of the macro now says (and I quote): "It wasn't handling the OT workbook correctly and was trying to process it as well. I'd thought I had worked around it well enough knowing it was going to be picked up by the FileSearch object, but I missed a line and it was what was causing the program to crash on the last file every time. " In any event, I will ask the author to look at this thread, but it has gotten too high level for me to understand now, and I think it's time for me to let it go, since everything is working now, for whatever reasons, though it bothers me that the main problem was exhibited only on my most used, most powerful, computer. Fortunately, this macro was for a one time only job so I can probably 'get over it'. Thanks so much for all your help and advice. I'm sure it's to prevent spam, but do I add dot com to your e-mail address shown? Dean "Peter T" <peter_t@discussions wrote in message ... Perhaps I didn't pick up on what you re-describe below, it's been difficult to follow and got distracted by other problems with the code. The only thing I can think of that might explain that scenario is if somehow the app-Filesearch is not correctly clearing an old search particularly in view of the filesearch object being retained as Global between sessions, however I would expect FS.NewSearch to reset. I tried to recreate your overall set-up with a few files and tried to run the original code. However your code was throwing up errors in various places, particularly incorrectly keeping track of arrays. So without spending a lot of time rewriting not possible to get to the bottom of it. However at no time did Filesearch falsely find the oldname of a renamed file. Despite that I'm still slightly suspicious that in your set-up of files Filesearch just might return the old-name of a renamed file. See if you can re-create the problem as before. If so entirely delete or move the rogue renamed file out of the search path. Make a new similar file with same new-name in the search path. Repeat the search, any difference. If the oldname was found first time but recreate the problem again and in Sub GetFileList(), after the line With FS, add the following .MatchTextExactly = True (not sure why that would make a difference but just in case) In passing I can't see any reason the code can't work with "value file1". The function GetFileDate tries to find if the last part of the name is a valid date. "1" is a valid date, namely 12-31-1899. If the name does not contain a trailing valid date the function would loop forever. Regards, Peter T "Dean" wrote in message ... << Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). At the expense of belaboring things, I am referring to the fact that I ran the macro and learned that it did not like one of the input file filenames it saw - let's call it "value file1" - I later learned that the programmer didn't allow for just any filename. So, I renamed (didn't copy) the input file to "portfolio file 1", a name that I knew would be compatible, Then, I got a FRESH COPY of the template with the macro in it and reran the macro. It then crashed saying it couldn't find "value file 1". To my simple brain, this should be impossible, unless parts of the macro somehow reach outside the calling file. Dean "Peter T" <peter_t@discussions wrote in message ... There are many reasons for memory leaks, some very hard to track down. In the case of the code you posted the use of statement is 'End' will cause that particularly with all those global variables and arrays. But I don't think that's not why the code failed. There can be issues with the FileSearch but also don't think relevant here as it starts with NewSearch (I might be wrong). Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). If you've already re-installed Excel don't worry about trying to 'reverse'. Regards, Peter T "Dean" wrote in message ... I think it is this whole concept of memory leaks that is beyond my simple intellect! ... that it appears that you can run a macro, then exit the file without saving, and yet, somehow, EXCEL remembers what files were called, and expects to find them again - stuff like that. Is there any URL or thread that I might use to educate myself on this, just conceptually? Unfrotmautely, I did reinstall EXCEL. Can I use system restore to reverse that and, if so, do you think I should bother if all is just as it was before. Isn't a clean copy always a good idea, if you're willing to suffer through the installation? Thanks so much for all your hard work. I'm sorry that there had been so many iterations by the time you saw it! Dean "Peter T" <peter_t@discussions wrote in message ... Yes our two recent posts crossed. Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching?
Ah well, if it works it works.
for whatever reasons, though it bothers me that the main problem was exhibited only on my most used, most powerful, computer. I wouldn't blame it on your system or your Excel installation. Thanks so much for all your help and advice. I'm sure it's to prevent spam, but do I add dot com to your e-mail address shown? It was a bit more than I bargained when you dragged me here from your other thread <g but you're welcome. My address below, fill in the spaces with the obvious. Regards, Peter T pmbthornton gmail com "Dean" wrote in message ... I wasn't intending the value file 1 name to be taken literally - the issue seemed to be that the filename started with the word value, not that this might change your answer. And regardless of why I changed the filename, I did change it, and the file kept looking for the old input filename. Then, when I deleted the file altogether, the macro would not finish. The author of the macro now says (and I quote): "It wasn't handling the OT workbook correctly and was trying to process it as well. I'd thought I had worked around it well enough knowing it was going to be picked up by the FileSearch object, but I missed a line and it was what was causing the program to crash on the last file every time. " In any event, I will ask the author to look at this thread, but it has gotten too high level for me to understand now, and I think it's time for me to let it go, since everything is working now, for whatever reasons, though it bothers me that the main problem was exhibited only on my most used, most powerful, computer. Fortunately, this macro was for a one time only job so I can probably 'get over it'. Thanks so much for all your help and advice. I'm sure it's to prevent spam, but do I add dot com to your e-mail address shown? Dean "Peter T" <peter_t@discussions wrote in message ... Perhaps I didn't pick up on what you re-describe below, it's been difficult to follow and got distracted by other problems with the code. The only thing I can think of that might explain that scenario is if somehow the app-Filesearch is not correctly clearing an old search particularly in view of the filesearch object being retained as Global between sessions, however I would expect FS.NewSearch to reset. I tried to recreate your overall set-up with a few files and tried to run the original code. However your code was throwing up errors in various places, particularly incorrectly keeping track of arrays. So without spending a lot of time rewriting not possible to get to the bottom of it. However at no time did Filesearch falsely find the oldname of a renamed file. Despite that I'm still slightly suspicious that in your set-up of files Filesearch just might return the old-name of a renamed file. See if you can re-create the problem as before. If so entirely delete or move the rogue renamed file out of the search path. Make a new similar file with same new-name in the search path. Repeat the search, any difference. If the oldname was found first time but recreate the problem again and in Sub GetFileList(), after the line With FS, add the following .MatchTextExactly = True (not sure why that would make a difference but just in case) In passing I can't see any reason the code can't work with "value file1". The function GetFileDate tries to find if the last part of the name is a valid date. "1" is a valid date, namely 12-31-1899. If the name does not contain a trailing valid date the function would loop forever. Regards, Peter T "Dean" wrote in message ... << Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). At the expense of belaboring things, I am referring to the fact that I ran the macro and learned that it did not like one of the input file filenames it saw - let's call it "value file1" - I later learned that the programmer didn't allow for just any filename. So, I renamed (didn't copy) the input file to "portfolio file 1", a name that I knew would be compatible, Then, I got a FRESH COPY of the template with the macro in it and reran the macro. It then crashed saying it couldn't find "value file 1". To my simple brain, this should be impossible, unless parts of the macro somehow reach outside the calling file. Dean "Peter T" <peter_t@discussions wrote in message ... There are many reasons for memory leaks, some very hard to track down. In the case of the code you posted the use of statement is 'End' will cause that particularly with all those global variables and arrays. But I don't think that's not why the code failed. There can be issues with the FileSearch but also don't think relevant here as it starts with NewSearch (I might be wrong). Not sure what you mean by "somehow, EXCEL remembers what files were called" other than use of the globals, variables declared at the top of the module (the code could be written without those). If you've already re-installed Excel don't worry about trying to 'reverse'. Regards, Peter T "Dean" wrote in message ... I think it is this whole concept of memory leaks that is beyond my simple intellect! ... that it appears that you can run a macro, then exit the file without saving, and yet, somehow, EXCEL remembers what files were called, and expects to find them again - stuff like that. Is there any URL or thread that I might use to educate myself on this, just conceptually? Unfrotmautely, I did reinstall EXCEL. Can I use system restore to reverse that and, if so, do you think I should bother if all is just as it was before. Isn't a clean copy always a good idea, if you're willing to suffer through the installation? Thanks so much for all your hard work. I'm sorry that there had been so many iterations by the time you saw it! Dean "Peter T" <peter_t@discussions wrote in message ... Yes our two recent posts crossed. Concerning your question in capitals in your other recent message, and similar 'what's going on' Q below I can only assume it's down to a combination of you not understanding the code (not your fault), a difference in the files and/or locations in respective systems and in particular the way the code is written. As I mentioned a while back there are a number of things that could lead to errors which are not handled, and other things that could result in memory leaks. However nothing I see likely to require re-installing Excel as you expected in your other thread. Regards, Peter T "Dean" wrote in message ... I am confused by which message you mean by my last message. Perhaps, our posts are crossing one another. I am looking at your initial code that is in my present renamed file and it is all green fonted. I was virtually certain it was in the last run too, when something seemingly caused the macro to begin having questions about whether I wanted to reopen the calling file, something it was not doing previously - it was just terminating prematurely. I agree that a commented macro cannot matter, so I have to assume that, somehow, something caused it to start that line of inquiry, a symptom that I had seen some time ago, admittedly. In terms of why the file is now working perfectly when the only difference is this that it has been again renamed, albeit with commented out code, and the fact that I did a file dump list must have something to do with the latter, I assume. Can doing a file dump list somehow clear out EXCEL's or VBA's memory? Indeed, this file never had a problem on other computers. I also note that, after the file would stop prematurely, at the bottom left, the message "processing file 19 of 20" would linger until I closed out EXCEL completely. Does any of this make sense? D "Peter T" <peter_t@discussions wrote in message ... What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. When a code line is commented, ie preceded with an apostrophe, the line will turn green (subject your VBE options). In this state commented code does absolutely nothing. In other words inserting what I previously suggested will neither remove errors nor create them. However, the particular code I suggested should not cause any errors even with the apostrophes removed and hence executed. When the code breaks remove the comments, drag the yellow cursor to the first line of code and 'step through' with F8. You should see stuff in the immediate window as I described before. Why didn't you try the test code I suggested in my last message. Regards, Peter T "Dean" wrote in message ... It wasn't that I couldn't comment out the rows, it's just that I didn't think you would want me to. The reason I didn't is because, with this new filename dialog box, error, it seems like the yellow cursor was no longer where it used to be. In the past, when the macro bombed because it could not find an old input filename I had renamed, it would be yellow highlighted at the "For dFileCount = 0 ..."row, which is just prior to the stuff we inserted - just a reminder, after I deleted the renamed file, the macro no longer produced an error message, it just terminated prematurely. Now, the yellow cursor is at the line just after all the stuff we inserted so I didn't think commenting it out would help, since the macro was already past it - at least for the current iteration. It's as if, now, I have a new problem to deal with. What confuses me is how putting in a bunch of stuff that is all commented out can change the running of the macro, but indeed it seems to have created a new problem to boot. Kindly respond. I will try your suggestion. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel + MS Query + table definition caching | Excel Discussion (Misc queries) | |||
images caching | Excel Programming | |||
EXCEL CACHING ... | Excel Programming | |||
Linked files - Caching of data | Excel Discussion (Misc queries) | |||
COM Interface and IDE caching | Excel Programming |