macro opening too many files at once.
 

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

macro opening too many files at once.
 

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" wrote:

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