Here goes: it needs a few .xls files in the C:\Temp directory and let'er run
Option Explicit
Dim c As Long
Dim RngB As Range
Dim i As Range
Dim wb As Workbook
Dim CancelA As Boolean
Sub ProcessData()
Dim wb As Workbook
Dim TheFile As String
Dim ThePath As String
ThePath = "C:\Temp"
Application.ScreenUpdating = False
ChDir ThePath
TheFile = Dir("*.xls")
Do While TheFile < ""
If TheFile < "Daily Error report MASTER.xls" Then
'MsgBox TheFile
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
Call AAAProcessData
ActiveWorkbook.Save
ActiveWorkbook.Saved = True
wb.Close
End If
TheFile = Dir
Loop
Workbooks.Open Filename:=ThePath & "\" & "Daily Error report
MASTER.xls"
Application.ScreenUpdating = True
End Sub
Sub AAAProcessData()
CancelA = False
Call DelColsSort
Call DelRows
Call Summarize
If CancelA = True Then Exit Sub
Call CleanUp
End Sub
Sub DelColsSort()
Range("A:A,B:B,D:D,E:E,G:G,H:H,I:I").Delete
[F1].Value = "RC Code"
[G1].Value = "Aging"
[H1].Value = "Count"
[F1:H1].HorizontalAlignment = xlCenter
End Sub
Sub DelRows()
Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
RngB.Offset(, -1).Resize(, 2).Sort Key1:=Range("B2"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For c = RngB.Count To 1 Step -1
If Left(RngB(c), 6) < "C0B90D" And _
Left(RngB(c), 6) < "C0B90E" And _
Left(RngB(c), 6) < "C0B90F" And _
Left(RngB(c), 6) < "C0B90G" Then
RngB(c).EntireRow.Delete
End If
Next c
End Sub
Sub Summarize()
Dim FirstCell As Range
Dim LastCell As Range
Dim Dest As Range
Call SetupFinal
If IsEmpty(Range("B2").Value) Then
CancelA = True
Exit Sub
End If
Set FirstCell = [B2]
Do
Set LastCell = Nothing
For c = 1 To 1000
If Left(FirstCell.Offset(c), 6) < Left(FirstCell, 6) Then
Set LastCell = FirstCell.Offset(c - 1)
Exit For
End If
Next c
Set Dest = Range("F2:F5").Find(What:=Left(FirstCell.Value, 6),
LookAt:=xlWhole)
Dest.Offset(, 1).Value = Application.Max(Range(FirstCell,
LastCell).Offset(, -1))
Dest.Offset(, 2).Value = Range(FirstCell, LastCell).Count
Set FirstCell = LastCell.Offset(1)
Loop Until IsEmpty(FirstCell.Value)
End Sub
Sub SetupFinal()
[F2].Value = "C0B90D"
[F3].Value = "C0B90E"
[F4].Value = "C0B90F"
[F5].Value = "C0B90G"
[F6].Value = "GTotal"
For Each i In Range("G2:H5")
i.Value = i.Value * 1
Next i
End Sub
Sub CleanUp()
Columns("F:H").Columns.AutoFit
[F2:H6].HorizontalAlignment = xlCenter
[F6].Value = "GTotal"
[G6].Value = Application.Max(Range("G2:G5"))
[H6].Value = Application.Sum(Range("H2:H5"))
[F6:H6].Font.Bold = True
End Sub
Thanks
"Dave Peterson" wrote in message
...
How about posting the exact code that you're using?
rjr wrote:
Got the runtime error 1004 method open of object workbooks failed with
the
debug
showing
Set wb = Workbooks.Open(list(i))
Bob
"Tom Ogilvy" wrote in message
...
Try it like this
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim list() As String
Dim i As Long
ReDim list(1 To 1)
MyPath = "C:\Temp"
ChDrive MyPath
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
list(UBound(list)) = MyPath & "\" & TheFile
ReDim Preserve list(1 To UBound(list) + 1)
TheFile = Dir
Loop
ReDim Preserve list(1 To UBound(list) - 1)
For i = 1 To UBound(list)
Set wb = Workbooks.Open(list(i))
MsgBox wb.FullName
wb.Close
Next i
End Sub
--
Regards,
Tom Ogilvy
"rjr" wrote in message
. ..
Dave, my apologies for not getting back sooner, kinda forgot where I'm
at
.
Yes I tried the subroutine you provided
Option Explicit
Sub Testme01()
Dim myName As String
Dim wb As Workbook
myName = "C:\Documents and
Settings\Owner\Desktop\KathleenFolder\asf.xls"
Set wb = Workbooks.Open(myName)
End Sub
and it worked fine. I changed the my name to c:\temp which is where we
we're now working out of. It seems the problem occurs when the code
loops
and it won't open the second file as it did the first. I am able to
open
all the files in that folder individually, but not through the code. I
also tried Gary's little routine that worked on 2003 and it stopped at
the same location as Otto's code did.
Is that a setting that might be creating problems for me inside of
2003?
Thanks again
Bob Reynolds
"Dave Peterson" wrote in message
...
First, did you try that little subroutine?
Otto posted this code as the basis for the code you're using.
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir
Loop
End Sub
This could cause trouble if the logged drive isn't the C: drive.
(Changing the
folder won't change the drive.)
Does adding another line help?
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
ChDrive myPath '<--- Added
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir
Loop
End Sub
rjr wrote:
Latest Update.
Otto and I worked and worked and discovered he is using Excel 2002
Professional and I am using Excel 2003 Professional with Windows XP
Media
Edition 2005. I can't get it to work on my computer at all, so I
thought I'd
try my laptop which is new and has Excel 2003 Professional, along
with
Windows XP Professional also. Then I decided to try my wife's
laptop.
She is
running Windows XP Home and Office 2000 Professional and
consequently
Excel
2000 Professional.
Lo and Behold the code ran correctly and did exactly what Otto swore
it
would do and did on his computer.
Now my question is does anyone know of a reason this should be
possible
or
is there an issue with 2003 that needs to be turned on to make them
work.?
I really need to make this work on an Excel 2003 program so I'm
kinda
back
where I was.....
Any advice would be greatly appreciated and I would also like to
thank
everyone that helped in gaining resolution with this problem,
especially on
New Year's Eve.
Bob Reynolds
"Dave Peterson" wrote in message
...
I wanted you to try the separate procedure as a test--not a
replacement for
the
original procedure.
And I can't think of any other reasons--and I wouldn't guess that
reinstalling
Office (or windows!) would help--but I've been wrong lots of
times.
rjr wrote:
"Dave Peterson" wrote in message
...
I wouldn't guess so--but that's just a guess.
I'm still guessing that it's something simple--like a locked
file.
***
none of the files are locked or protected.
If you create a separate procedure in a test module:
*****No because what we're trying to do is go down the directory
of
.xls
files and open, do the procedure and then close -- move to next
one
and
redo
the procedure until all files (approx 22) have had the code run
on
them
and
it's completed.
I will add that if I take this portion (where the error occurs)
of
the
code
out of it, the code runs perfectly on each file operated
independently
but
it requires me to run it on each one individually.
Option Explicit
sub Testme01()
dim myName as string
dim wb as workbook
myName = "C:\Documents and
Settings\Owner\Desktop\KathleenFolder\asf.xls"
set wb = workbooks.open(myname)
End sub
===
I don't think that this will help...
But if you move that asf.xls file to a different location, does
the
original
procedure work ok?
I can't imagine what can be happening where you can open this
file
manually, but
have problems when you open it via code.
It will open by single code, like I said, but once we try it
through
the
loop he hangs up right there. Otto has it working fine (With the
same
files)
on his computer and I even transferred all my miles and code to
another
laptop I have running windows xp pro and office 2003 with the
same
exact
results I get. That's why I was thinking it might be a setting on
my
computer...Again, if it works on other computers that would tend
to
isolate
mine as the cause. Is there any reasons that you can think
of.....
Lastly
do you think removing and reinstalling office would help. I can't
see
where
it would since all features are installed, but I don't know
enough
to
make a
really educated guess....
Thanks
Bob Reynolds
rjr wrote:
C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls
This is the location of where the file is and the asf.xls is
the
file
that
was the first one to be opened. Would a new installation of
Office
Professional help, do you think???
Bob
"Dave Peterson" wrote in message
...
I don't have a guess, but I am curious about what the msgbox
showed.
What was the filename (or path and filename)?
rjr wrote:
I put Tom's in and the msgbox returned the complete
directory
that
we're
using as the directory and the proper file name. It still
errored
out
on
the
Set wb line...
I'm thinking that it has to do with some setting I've got
on
my
computer
that's keeping this from running.
Otto had run it two different ways and also used my file
and
his
ran
perfectly, but I've continued getting the error.
Bob
"Tom Ogilvy" wrote in message
...
Here is another guess -
Change the code to:
Do While TheFile < ""
If TheFile < "Daily Error report MASTER.xls"
Then
MsgBox ThePath & "\" & TheFile
Set wb = Workbooks.Open(ThePath & "\" &
TheFile)
make sure you actually need to append the "\" and don't
actually
have
a
filename result like:
C:\\Report1.xls
In windows NT type Operating systems, this will still
work,
but
in
Windows
9x, it will error.
--
Regards,
Tom Ogilvy
"rjr" wrote in
message
. ..
Run Time Error 1004 Method OPEN of object.workbooks
failed.
This
is
in
the Microsoft vb alert window. the Error place in the
code
is at
Do While TheFile < ""
If TheFile < "Daily Error report MASTER.xls"
Then
MsgBox TheFile
Set wb = Workbooks.Open(ThePath & "\" &
TheFile) <<
error
line.
I've sent Otto the files while he was working on the
code.
The
code
works
perfectly for him at his computer.
I can open ALL files from file open or double clicking
on
the
file.
"Dave Peterson" wrote in
message
...
What's the error that is displayed?
Any chance that Otto has access to that folder and you
don't?
Can you open the file just via File|Open?
rjr wrote:
Hi, I've got the problem that Otto is helping me with.
I've
done
a
complete
detect and repair and as Chip has suggested I've
looked
in the
References
List box and I do have Microsoft Forms 2.0 Object
Library
installed
and
checked. I also have the FM20.dll installed and
visible.
I
also
attempted to
include a user form and it was installed without a
hitch,
absolutely
no
problem.
I did send the file to Otto and he can't recreate the
problem.
The
code
performs as it should on his machine and completes. I
still
get
the
error
and the code stops, as Otto has said. Is there
anything
that
anyone
can
think of that I can do that he and I haven't. If
uninstalling
and
reinstalling Excel would work I could do that also.
Thanks Bob Reynolds
"Chip Pearson" wrote in message
...
Do you know the exact wording of the error message?
Is
it
coming
from
Excel or from VBA?
he doesn't is "Microsoft Forms 2.0 Object Library".
He
doesn't
have
anything like that to check.
That's a big clue. Does he really not have the
Forms
library
listed
in
the References list box? Even if it not checked, it
should
be
present
in
the list. MSFORMS is part of the basic Excel
installation,
not
optional in
any way. If "Microsoft Forms 2.0 Object Library"
doesn't
appear
at
all in
the References list, the basic installation of Excel
is
bad.
The first thing I'd advise to your user is to
unregister and
re-register
Excel. Close Excel (and all other Office programs),
go
to
the
Windows
Start menu, choose Run and enter
RegSvr32 "Excel.exe" /unregserver
The repeat the process with
RegSvr32 "Excel.exe" /regserver
You may have to use the full file path of Excel
instead
of
just
"Excel.exe". In the Immediate window of VBA, enter
the
following
and
press
Enter.
?Application.Path & "\Excel.exe"
This will display the full pathname of Excel.exe.
Use
that
filename
in
place of "Excel.exe" in the RegSvr32 commands.
Now try to open the workbook. In VBA, ensure that
"Microsoft
Forms
2.0
Object Library" appears in the list of references.
Try
adding
a
UserForm
to the project. Doing so will automatically add a
reference
to
MSFORMS.
If this fails, and you (by which I mean your user)
get
an
error
message
when trying to add a UserForm, try to add the
reference
to
MSFORMS
manually. In the References dialog, click "Browse"
and
navigate
to
the
file
C:\WINDOWS\system32\FM20.DLL
If you find the file, click OK or Open. If you
don't
find
the
file,
then
you'll probably want to run "Detect And Repair" from
the
Help
menu.
If
this doesn't fix the problem, you'll likely need to
uninstall
and
reinstall Office.
A missing MSFORMS reference item and/or a missing
FM20.DLL
file
indicates
that something serious has gone on.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Otto Moehrbach" wrote in
message
...
Excel XP & Win XP
I'm helping an OP with a project. The code loops
through
all
the
files
in a folder, opens each, does things, closes the
file
and
opens
the
next
file, etc.
The code is placed in the Personal.xls file. It
works
fine
for
me.
It
fails on opening the first file for him (will not
open
the
file).
An
error is produced saying that opening the file
failed.
He
has
Excel
2003.
He sent me his Personal.xls file. I changed the
name
and
put
it
in
my
XLSTART folder. It works fine with my path. I
created his
path
on
my
computer and that works fine too.
I checked the VBE - Tools - References. The only
thing I
have
checked
that he doesn't is "Microsoft Forms 2.0 Object
Library".
He
doesn't
have
anything like that to check.
Does anyone have any ideas of what I could do?
Thanks
for
your
time.
Otto
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson