macro opening too many files at once.

[SteveDB1]

Hi all.
This will be the third time I've posted some variation of this. The last two
have disappeared in to the deep blue mist.

I was able to modify the copy4 macro from Ron DeBruin's website.
It actually works fairly well.
As an addendum to the initial post, I've made the following modifications
and they appear to be working, with the exception of one element that I can
identify thus far.
This macro looks in the directory A, compares to directory B files, and
either gives a message box stating it's already been processed, or starts
following through to process.

The last element opens a workbook, calls to a SaveNewFormat macro which then
processes the workbook, saves it as a read only, and then closes it.

Once that is complete, it jumps back to this macro, to look for the next
file to compare, and determine if it needs to be processed or not. If process
is yes, it opens another workbook, but for some reason not yet understood, it
either opens a secondary workbook, or leaves the first book open.... we're
still not clear on this yet.
If I understand it correctly, FNum does not advance, or if it does advance,
it then resets by looping twice through the FNum routine, below.
'----------------------
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set myBook = Nothing
On Error Resume Next
Set myBook = Workbooks.Open(myPath & MyFiles(Fnum))
On Error GoTo 0
Next Fnum
'--------------------
as stated, I'm not clear here on why it'd peform this twice. But it appears
to do just that. I think it's part of the FNum resetting itself to 1, from
some higher numeric value.
Your input is greatly appreciated.
Best.


'-----------------------------------------------------
Sub AFileSearch()
' this is a variation of Ron DeBruin's(DB) COPY4 macro presented on his
website.

Dim myPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim myBook As Workbook
Dim FSO As Object

myPath = "C:\StevesTemp\PreRun\" 'the path where source files located-
directory A.


FilesInPath = Dir(myPath & "DTR*.xl*") 'files to look for.
If FilesInPath = "" Then
MsgBox "No Files Found" 'if no files are found, print msg box output.
Exit Sub 'if no files found, stop running macro.
End If


Set FSO = CreateObject("scripting.filesystemobject") 'Apparently, this maps
my network drives.

Fnum = 0 'this must remain zero. DO NOT CHANGE. If you change it, it will
throw all kinds of errors.
Do While FilesInPath <> "" 'will continue to process as long as FilesInPath
does not equal blank.

FilesInPath = Left(FilesInPath, InStr(FilesInPath, ".") - 1) ' this appears
to remove the existing file's extension

If FSO.FileExists("C:\StevesTemp\PreRun\PostRun\" & FilesInPath & ".xlsx") =
True Then 'this is part of Ron DB's modification to compare files.
'final directory- directory B
MsgBox "The file: " & FilesInPath & " has been processed."

Else

Fnum = Fnum + 1 'counter
ReDim Preserve MyFiles(1 To Fnum) 'part of counter.
MyFiles(Fnum) = FilesInPath

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set myBook = Nothing
On Error Resume Next
Set myBook = Workbooks.Open(myPath & MyFiles(Fnum))
On Error GoTo 0
Next Fnum
End If


Call ASaveNewFormat 'this calls to a macro that will perform two tasks.
'1st it will save the file as a new, xlsx format (which can be
modified),
'2nd it will remove all of the file's empty rows/columns.

End If

FilesInPath = Dir()
'even though the file name of FilesInPath changes here, MyFiles() retains
the file name for the first file.
' there is nothing here to set MyFiles to the next file in the directory.



Loop '

End Sub

 

[JLGWhiz]

You might be right about Fnum not advancing. When the file is saved as a new
file, does the code close that file and return to the workbook running the
code as the active workbook? It seems like the problem might lie somewhere
in what the called macro does, more than in the posted code.

 

[SteveDB1]

Morning JLG,
(this is the third time I've posted this, and each time before they've
disappeared. I contacted customer support to find out why, and have yet to
get a response. My point being, if this identical post ends up reappearing,
please accept my apologies, because I really want this post answered, and am
frustrated by its continual disappearance)

I've been setting watches on the various functions so I can figure out
what's taking place with this macro.
I'll mix my responses with your statements.

You might be right about Fnum not advancing. When the file is saved as a new
file, does the code close that file and return to the workbook running the
code as the active workbook?

The code for the other macro being called to saves the file as a read only,
xlsx book, and then closes it.
Once it performs tthat task, it leaves macro2, and returns to macro one, to
reloop, and check for the next file.
This morning, I set a watch on only the Fnum just to focus my attention on
it alone.
Once it reached
set myBook = nothing
Fnum reset to 1, from 3.
I continued on, and Fnum then remained one, and repeated, opened the book
again. It then threw an error which stated myPath could not be found, and
gave a message stating that it could've been deleted, or write-protected,
etc....
This error is a new one which has not occurred before now.

It seems like the problem might lie somewhere
in what the called macro does, more than in the posted code.

The 2nd macro performs an intermediary task of saving the open workbook as
an XLSX book, and then calls to a third macro written by Jim Thomlinson to
"debloat" a workbook which has unused rows/columns in it (I was able to
reduce one file in particular from 17.4mb down to 350kb).

The macro that I've posted the code for is the last of a string of macros
that I'm trying to meld in to a single macro to allow us to clean our older
files of unused rows/columns, etc....
Before this last component, I found that I was able to take 513 files, and
reduce their overall size from 189mb down to 55mb.
While not a whole lot, it's enough to use it on other files. My colleague
has some 3000-4000 files, and will be using it on his to likewise "remove the
bloat."
It goes a long ways to saving server space, and minimizing our need to buy
more server storage space.

Back to the topic....
I just did another run through, with multiple watches set.

'----------------------
For Fnum = LBound(MyFiles) To UBound(MyFiles)

'this just reset Fnum back to 1, from 3, after having processed 2 files
'MyFiles now = the 2nd file name, and FilesInPath = 3rd file name.
Set myBook = Nothing

On Error Resume Next
Set myBook = Workbooks.Open(myPath & MyFiles(Fnum))
'This just opened the 2nd workbook, after having processed it through the
called macro (it ran once already, saved the 2nd workbook as the new file
format, and closed it) . I've done a few run throughs (with watches set) on
this, and it's now my "belief" that this is where the error occurs. MyFiles
is changed here from the FilesInPath name to the previously processed file.
I.e., FilesInPath = File_N, and MyFiles = File_N-1.


On Error GoTo 0
Next Fnum

'Next Fnum now loops back up to

Set myBook = Workbooks.Open(myPath & MyFiles(Fnum))

'which then throws the error of not being able to locate myPath.

'The actual message states:

"Run-time error '1004' C:\StevesTemp\PreRun\ could not be found. Check the
spelling of the file name, and verify the file location is correct. If you
are trying to open the file from your list of most recently files, make sure
that the file has not been renamed, moved, or deleted."

'As stated above, this is a new error to this morning. And at present, the
second workbook is presently open, from the previous run through.

'--------------------
Something else that I've noticed is that it seems that FilesInPath seems to
have an intermediary "" mark to it, as though it's either a null space just
to mark the change, and it then