Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Code looping through files

Sorry, that would be

Help=About Microsoft Excel

Typo.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in Disabled
Items button at the bottom. If any of your files are listed there, then I
would remove them from being disabled (select the appropriate files one at
a time in the list and hit the Enable button for each).

You said you had opened them manually, but that may have been sometime in
the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first file
name. WHen I selected ok it errored out again with the 1004 error and the
debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first loop
worked fine and opened the first file, but when it did the loop errored
out as did Otto's, on the second round. This is the same thing that
happened with Otto's code and does it consistentantly with my desktop
and my laptop.

I'm sorry for not getting back with you on this. Can you think of a
reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center Edition
with all current updates and a valid updated Office Professional
product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same results.
Since it works for everyone else it obviously has to be something
with my computers, but WHAT? My dell laptop is relatively clean
without a lot of additional programs and the results are the same.
That's why I think the problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem is
using a Macintosh? If this proves to be the case, the code will fail
on the line indicated because Macs don't use the '\' character as a
path separator. Instead, they use, I think, a ':' character. Instead
of hard coding the '\', use Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what the
OP and I just did to establish a hard starting point. He set all
the code I had written for him aside. Instead, he used the macro
from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in the
macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come up
with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message ...
Since you don't show the code, have him send you the file that
fails to open and test the code with that file. Perhaps the file
is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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






















  #42   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Code looping through files

does it work if you only put 2 files in the folder? does it work if you create 2
new dummy files and put them in the folder? another folder with a different
name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without issue
at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in Disabled
Items button at the bottom. If any of your files are listed there, then I
would remove them from being disabled (select the appropriate files one at a
time in the list and hit the Enable button for each).

You said you had opened them manually, but that may have been sometime in the
past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first file
name. WHen I selected ok it errored out again with the 1004 error and the
debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first loop
worked fine and opened the first file, but when it did the loop errored
out as did Otto's, on the second round. This is the same thing that
happened with Otto's code and does it consistentantly with my desktop and
my laptop.

I'm sorry for not getting back with you on this. Can you think of a reason
your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center Edition
with all current updates and a valid updated Office Professional
product.
As a side note I've also placed it on my Dell D520 laptop with Windoes
XP Professional and Office 2003 with the exact same results. Since it
works for everyone else it obviously has to be something with my
computers, but WHAT? My dell laptop is relatively clean without a lot of
additional programs and the results are the same. That's why I think the
problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem is
using a Macintosh? If this proves to be the case, the code will fail on
the line indicated because Macs don't use the '\' character as a path
separator. Instead, they use, I think, a ':' character. Instead of hard
coding the '\', use Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what the OP
and I just did to establish a hard starting point. He set all the
code I had written for him aside. Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was used.

He established a path in his computer to match that used in the macro
and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come up with.
Thanks for your time. Otto"Tom Ogilvy" wrote in
message ...
Since you don't show the code, have him send you the file that fails
to open and test the code with that file. Perhaps the file is
damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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
























  #43   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if you
create 2 new dummy files and put them in the folder? another folder with a
different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without
issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are listed
there, then I would remove them from being disabled (select the
appropriate files one at a time in the list and hit the Enable button
for each).

You said you had opened them manually, but that may have been sometime
in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first
file name. WHen I selected ok it errored out again with the 1004 error
and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the loop
errored out as did Otto's, on the second round. This is the same
thing that happened with Otto's code and does it consistentantly with
my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of a
reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to be
something with my computers, but WHAT? My dell laptop is relatively
clean without a lot of additional programs and the results are the
same. That's why I think the problem lies with my machine's
setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem
is using a Macintosh? If this proves to be the case, the code will
fail on the line indicated because Macs don't use the '\'
character as a path separator. Instead, they use, I think, a ':'
character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what
the OP and I just did to establish a hard starting point. He set
all the code I had written for him aside. Instead, he used the
macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in the
macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come up
with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message ...
Since you don't show the code, have him send you the file that
fails to open and test the code with that file. Perhaps the
file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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


























  #44   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Code looping through files

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
  #45   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Code looping through files

That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if you
create 2 new dummy files and put them in the folder? another folder with
a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without
issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are listed
there, then I would remove them from being disabled (select the
appropriate files one at a time in the list and hit the Enable button
for each).

You said you had opened them manually, but that may have been sometime
in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first
file name. WHen I selected ok it errored out again with the 1004 error
and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the loop
errored out as did Otto's, on the second round. This is the same
thing that happened with Otto's code and does it consistentantly
with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of a
reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to be
something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with my
machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem
is using a Macintosh? If this proves to be the case, the code
will fail on the line indicated because Macs don't use the '\'
character as a path separator. Instead, they use, I think, a ':'
character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what
the OP and I just did to establish a hard starting point. He
set all the code I had written for him aside. Instead, he used
the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in the
macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come up
with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message ...
Since you don't show the code, have him send you the file that
fails to open and test the code with that file. Perhaps the
file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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






























  #46   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

Same error, first file opens ok and then once the loop starts on the second
one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if you
create 2 new dummy files and put them in the folder? another folder with
a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without
issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are listed
there, then I would remove them from being disabled (select the
appropriate files one at a time in the list and hit the Enable button
for each).

You said you had opened them manually, but that may have been sometime
in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first
file name. WHen I selected ok it errored out again with the 1004
error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the
loop errored out as did Otto's, on the second round. This is the
same thing that happened with Otto's code and does it
consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of a
reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to be
something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with my
machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem
is using a Macintosh? If this proves to be the case, the code
will fail on the line indicated because Macs don't use the '\'
character as a path separator. Instead, they use, I think, a ':'
character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what
the OP and I just did to establish a hard starting point. He
set all the code I had written for him aside. Instead, he used
the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in the
macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come
up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file that
fails to open and test the code with that file. Perhaps the
file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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






























  #47   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

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



  #48   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Code looping through files


are you using xp?

create new user and try to run it under the new user.
--


Gary


"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the second
one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if you
create 2 new dummy files and put them in the folder? another folder with a
different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without
issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are listed
there, then I would remove them from being disabled (select the
appropriate files one at a time in the list and hit the Enable button for
each).

You said you had opened them manually, but that may have been sometime in
the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first
file name. WHen I selected ok it errored out again with the 1004 error
and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first loop
worked fine and opened the first file, but when it did the loop
errored out as did Otto's, on the second round. This is the same thing
that happened with Otto's code and does it consistentantly with my
desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of a
reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center Edition
with all current updates and a valid updated Office Professional
product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same results.
Since it works for everyone else it obviously has to be something
with my computers, but WHAT? My dell laptop is relatively clean
without a lot of additional programs and the results are the same.
That's why I think the problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem is
using a Macintosh? If this proves to be the case, the code will
fail on the line indicated because Macs don't use the '\' character
as a path separator. Instead, they use, I think, a ':' character.
Instead of hard coding the '\', use Application.PathSeparator.
E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what the
OP and I just did to establish a hard starting point. He set all
the code I had written for him aside. Instead, he used the macro
from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in the
macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come up
with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message ...
Since you don't show the code, have him send you the file that
fails to open and test the code with that file. Perhaps the file
is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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
































  #49   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Code looping through files

Is the code in a General/Standard module (The results of Insert=Module in
the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if you
create 2 new dummy files and put them in the folder? another folder
with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without
issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are listed
there, then I would remove them from being disabled (select the
appropriate files one at a time in the list and hit the Enable button
for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first
file name. WHen I selected ok it errored out again with the 1004
error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the
loop errored out as did Otto's, on the second round. This is the
same thing that happened with Otto's code and does it
consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of
a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to be
something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with
my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't use
the '\' character as a path separator. Instead, they use, I
think, a ':' character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what
the OP and I just did to establish a hard starting point. He
set all the code I had written for him aside. Instead, he
used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come
up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file. Perhaps
the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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
































  #50   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Code looping through files

Here is another to try

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub

Does it error on a single file? If so, anything special about that file?

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the
loop errored out as did Otto's, on the second round. This is the
same thing that happened with Otto's code and does it
consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of
a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to
be something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with
my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they use,
I think, a ':' character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come
up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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




































  #51   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

This is located in a Personal.xls file and not affixed to a particular
worksheet..

I'm pasting the code now and will see how it works.
Thanks
BOB

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the
loop errored out as did Otto's, on the second round. This is the
same thing that happened with Otto's code and does it
consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of
a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to
be something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with
my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they use,
I think, a ':' character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come
up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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


































  #52   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

Tom that code gave me the same error on the second file with the
Set wb = Workbooks.Open(fl.Path)
as the problem area.
I'm interested this code is in personal.xls and is listed under the general
tab. Is there someplace else that I could put it that it might work??

Also in reply to Gary, yes I'm running XP under "owner" which is me, and
administrator privleges. Are you saying to try it opening it with say
"Guest" and then try it?

Thanks to all
BOB

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the
loop errored out as did Otto's, on the second round. This is the
same thing that happened with Otto's code and does it
consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of
a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to
be something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with
my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they use,
I think, a ':' character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come
up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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


































  #53   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Code looping through files


just create a new user account and run it there. but try it in a regular code
module instead of the personal.xls first

you can delete the user account after you've tested it.
--


Gary


"rjr" wrote in message
.. .
Tom that code gave me the same error on the second file with the
Set wb = Workbooks.Open(fl.Path)
as the problem area.
I'm interested this code is in personal.xls and is listed under the general
tab. Is there someplace else that I could put it that it might work??

Also in reply to Gary, yes I'm running XP under "owner" which is me, and
administrator privleges. Are you saying to try it opening it with say "Guest"
and then try it?

Thanks to all
BOB

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module in
the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the second
one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if you
create 2 new dummy files and put them in the folder? another folder with
a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them without
issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are listed
there, then I would remove them from being disabled (select the
appropriate files one at a time in the list and hit the Enable button
for each).

You said you had opened them manually, but that may have been sometime
in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the first
file name. WHen I selected ok it errored out again with the 1004 error
and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the first
loop worked fine and opened the first file, but when it did the loop
errored out as did Otto's, on the second round. This is the same
thing that happened with Otto's code and does it consistentantly
with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think of a
reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to be
something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with my
machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the problem
is using a Macintosh? If this proves to be the case, the code
will fail on the line indicated because Macs don't use the '\'
character as a path separator. Instead, they use, I think, a ':'
character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's what
the OP and I just did to establish a hard starting point. He
set all the code I had written for him aside. Instead, he used
the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call was
used.

He established a path in his computer to match that used in the
macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can come up
with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message ...
Since you don't show the code, have him send you the file that
fails to open and test the code with that file. Perhaps the
file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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




































  #54   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Code looping through files

None of these files could be in the xl2007 file format? I believe you can
install a compatibility addin that allows xl2003 to open these files, but I
suspect that the Workbooks.Open method in VBA would not be able to do it
(can't say for sure). Just another guess. That wouldn't be consistent
with success in xl2000 or xl2002 which you claimed (on or the other), but
maybe that was with different files.

--
Regards,
Tom Ogilvy


"rjr" wrote in message
.. .
Tom that code gave me the same error on the second file with the
Set wb = Workbooks.Open(fl.Path)
as the problem area.
I'm interested this code is in personal.xls and is listed under the
general tab. Is there someplace else that I could put it that it might
work??

Also in reply to Gary, yes I'm running XP under "owner" which is me, and
administrator privleges. Are you saying to try it opening it with say
"Guest" and then try it?

Thanks to all
BOB

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the
first loop worked fine and opened the first file, but when it
did the loop errored out as did Otto's, on the second round.
This is the same thing that happened with Otto's code and does
it consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think
of a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to
be something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with
my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they use,
I think, a ':' character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can
come up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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




































  #55   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

Tom, That appears to be working and doing the loop thing. I ran it twice
completely through.. Now I just need to put it in the code OTTO gave me so
all the other things are carried out. Since I'm way outa my league any
chance you could take what I sent and replace what needs to be done with
this. Then it won't be making errors again.

Thanks so much
BOB

"Tom Ogilvy" wrote in message
...
Here is another to try

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub

Does it error on a single file? If so, anything special about that file?

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the
first loop worked fine and opened the first file, but when it
did the loop errored out as did Otto's, on the second round.
This is the same thing that happened with Otto's code and does
it consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think
of a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop with
Windoes XP Professional and Office 2003 with the exact same
results. Since it works for everyone else it obviously has to
be something with my computers, but WHAT? My dell laptop is
relatively clean without a lot of additional programs and the
results are the same. That's why I think the problem lies with
my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they use,
I think, a ':' character. Instead of hard coding the '\', use
Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can
come up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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






































  #56   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

All files were opened in excel while on the mainframe and saved to the locat
harddrive as an .xls file. Don't have any access to 2007 anything....
When the choice came up it was the 97-2002..... format we saved it with.

Thanks
BOB

"Tom Ogilvy" wrote in message
...
None of these files could be in the xl2007 file format? I believe you can
install a compatibility addin that allows xl2003 to open these files, but
I suspect that the Workbooks.Open method in VBA would not be able to do it
(can't say for sure). Just another guess. That wouldn't be consistent
with success in xl2000 or xl2002 which you claimed (on or the other), but
maybe that was with different files.

--
Regards,
Tom Ogilvy


"rjr" wrote in message
.. .
Tom that code gave me the same error on the second file with the
Set wb = Workbooks.Open(fl.Path)
as the problem area.
I'm interested this code is in personal.xls and is listed under the
general tab. Is there someplace else that I could put it that it might
work??

Also in reply to Gary, yes I'm running XP under "owner" which is me, and
administrator privleges. Are you saying to try it opening it with say
"Guest" and then try it?

Thanks to all
BOB

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the
first loop worked fine and opened the first file, but when it
did the loop errored out as did Otto's, on the second round.
This is the same thing that happened with Otto's code and does
it consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think
of a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop
with Windoes XP Professional and Office 2003 with the exact
same results. Since it works for everyone else it obviously
has to be something with my computers, but WHAT? My dell
laptop is relatively clean without a lot of additional
programs and the results are the same. That's why I think the
problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they
use, I think, a ':' character. Instead of hard coding the
'\', use Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can
come up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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






































  #57   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Code looping through files

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 lcase(TheFile) < lcase("Daily Error report MASTER.xls") Then
'MsgBox TheFile
On Error Resume Next
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
if err.number = 0 then
Call AAAProcessData
wb.Close SaveChanges:=True
else
msgbox "Not processed: " & TheFile
err.clear
end if
On Error goto 0
End If
TheFile = Dir
Loop
Workbooks.Open Filename:=ThePath & "\" _
& "Daily Error report MASTER.xls"
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
.. .
Tom, That appears to be working and doing the loop thing. I ran it twice
completely through.. Now I just need to put it in the code OTTO gave me so
all the other things are carried out. Since I'm way outa my league any
chance you could take what I sent and replace what needs to be done with
this. Then it won't be making errors again.

Thanks so much
BOB

"Tom Ogilvy" wrote in message
...
Here is another to try

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub

Does it error on a single file? If so, anything special about that file?

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of Insert=Module
in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled (select
the appropriate files one at a time in the list and hit the Enable
button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with the
1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the
first loop worked fine and opened the first file, but when it
did the loop errored out as did Otto's, on the second round.
This is the same thing that happened with Otto's code and does
it consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think
of a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop
with Windoes XP Professional and Office 2003 with the exact
same results. Since it works for everyone else it obviously
has to be something with my computers, but WHAT? My dell
laptop is relatively clean without a lot of additional
programs and the results are the same. That's why I think the
problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the case,
the code will fail on the line indicated because Macs don't
use the '\' character as a path separator. Instead, they
use, I think, a ':' character. Instead of hard coding the
'\', use Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator &
TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used in
the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can
come up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will open.

--
Regards,
Tom Ogilvy

"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






































  #58   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

Tom Thanks so much, I will try tomorrow during the day and let you know
thanks to all
Bob


"Tom Ogilvy" wrote in message
...
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 lcase(TheFile) < lcase("Daily Error report MASTER.xls")
Then
'MsgBox TheFile
On Error Resume Next
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
if err.number = 0 then
Call AAAProcessData
wb.Close SaveChanges:=True
else
msgbox "Not processed: " & TheFile
err.clear
end if
On Error goto 0
End If
TheFile = Dir
Loop
Workbooks.Open Filename:=ThePath & "\" _
& "Daily Error report MASTER.xls"
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
.. .
Tom, That appears to be working and doing the loop thing. I ran it twice
completely through.. Now I just need to put it in the code OTTO gave me
so all the other things are carried out. Since I'm way outa my league any
chance you could take what I sent and replace what needs to be done with
this. Then it won't be making errors again.

Thanks so much
BOB

"Tom Ogilvy" wrote in message
...
Here is another to try

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub

Does it error on a single file? If so, anything special about that
file?

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of
Insert=Module in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled
(select the appropriate files one at a time in the list and hit
the Enable button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with
the 1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the
first loop worked fine and opened the first file, but when it
did the loop errored out as did Otto's, on the second round.
This is the same thing that happened with Otto's code and does
it consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think
of a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop
with Windoes XP Professional and Office 2003 with the exact
same results. Since it works for everyone else it obviously
has to be something with my computers, but WHAT? My dell
laptop is relatively clean without a lot of additional
programs and the results are the same. That's why I think
the problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the
case, the code will fail on the line indicated because Macs
don't use the '\' character as a path separator. Instead,
they use, I think, a ':' character. Instead of hard coding
the '\', use Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator
& TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used
in the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can
come up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will
open.

--
Regards,
Tom Ogilvy

"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








































  #59   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files

Now when I run the code I get the message box and every file says
notprocessed and the files haven't changed. I no longer have a error to show
what part of the code is the problem. Any suggestions now.
Thanks
BOB Reynolds

"Tom Ogilvy" wrote in message
...
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 lcase(TheFile) < lcase("Daily Error report MASTER.xls")
Then
'MsgBox TheFile
On Error Resume Next
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
if err.number = 0 then
Call AAAProcessData
wb.Close SaveChanges:=True
else
msgbox "Not processed: " & TheFile
err.clear
end if
On Error goto 0
End If
TheFile = Dir
Loop
Workbooks.Open Filename:=ThePath & "\" _
& "Daily Error report MASTER.xls"
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
.. .
Tom, That appears to be working and doing the loop thing. I ran it twice
completely through.. Now I just need to put it in the code OTTO gave me
so all the other things are carried out. Since I'm way outa my league any
chance you could take what I sent and replace what needs to be done with
this. Then it won't be making errors again.

Thanks so much
BOB

"Tom Ogilvy" wrote in message
...
Here is another to try

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub

Does it error on a single file? If so, anything special about that
file?

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Is the code in a General/Standard module (The results of
Insert=Module in the VBE)?

rather than in a sheet module or the Thisworkbook module.

If not, try it in a General/Standard module.

Also, this is slightly modified.

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy

"rjr" wrote in message
.. .
Same error, first file opens ok and then once the loop starts on the
second one the error with the offending line at
Set wb = Workbooks.Open(fl.Path)

Bob

"Tom Ogilvy" wrote in message
...
That is the issue.
It works for everyone else.

Version of Excel shouldn't make any difference and doesn't for me.

Anyway, you can try this one which uses another approach:

Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
If fl.Name Like "*.xls" Then
Set wb = Workbooks.Open(fl.Path)
MsgBox wb.FullName
wb.Close
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"rjr" wrote in message
. ..
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife.
Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
does it work if you only put 2 files in the folder? does it work if
you create 2 new dummy files and put them in the folder? another
folder with a different name?

--


Gary


"rjr" wrote in message
. ..
No disabled items found.
Opened them manually as late as this evening. I've opened them
without issue at least 15 times today.

Bob

"Tom Ogilvy" wrote in message
...
That eliminates using the DIR command as a possible source of the
problem.

Since the problem is in xl2003, then perhaps:

In excel I would go to Tools=About Microsoft Excel, then look in
Disabled Items button at the bottom. If any of your files are
listed there, then I would remove them from being disabled
(select the appropriate files one at a time in the list and hit
the Enable button for each).

You said you had opened them manually, but that may have been
sometime in the past.

--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you,
I ran it and it ran to the first msgbox and then displayed the
first file name. WHen I selected ok it errored out again with
the 1004 error and the debug highlighted this area of code
Set wb = Workbooks.Open(list(i))

Any help??
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
.. .
Gary my apologies, I used your code as a standalone and the
first loop worked fine and opened the first file, but when it
did the loop errored out as did Otto's, on the second round.
This is the same thing that happened with Otto's code and does
it consistentantly with my desktop and my laptop.

I'm sorry for not getting back with you on this. Can you think
of a reason your 2003 would work and mine wouldn't?
Bob Reynolds


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this works for me

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
TheFile = Dir(MyPath & "\" & "*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir()
Loop
End Sub

--


Gary


"rjr" wrote in message
.. .
Hello again to all,
I wish it were that simple. Nope I'm windows XP Media Center
Edition with all current updates and a valid updated Office
Professional product.
As a side note I've also placed it on my Dell D520 laptop
with Windoes XP Professional and Office 2003 with the exact
same results. Since it works for everyone else it obviously
has to be something with my computers, but WHAT? My dell
laptop is relatively clean without a lot of additional
programs and the results are the same. That's why I think
the problem lies with my machine's setup...

Bob R

"Chip Pearson" wrote in message
...
Otto,

This is out of the blue, but perhaps the user having the
problem is using a Macintosh? If this proves to be the
case, the code will fail on the line indicated because Macs
don't use the '\' character as a path separator. Instead,
they use, I think, a ':' character. Instead of hard coding
the '\', use Application.PathSeparator. E.g.,

Set wb = Workbooks.Open(MyPath & Application.PathSeparator
& TheFile)

The code you posted works fine for me in Excel 2007 for
Windows.

--
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
...
Tom
Thanks for your help as well as Dave and Chip. Here's
what the OP and I just did to establish a hard starting
point. He set all the code I had written for him aside.
Instead, he used the macro from:
http://www.contextures.com/xlfaqMac.html#LoopBooks

with NO CHANGES of any kind. No other macro or macro call
was used.

He established a path in his computer to match that used
in the macro and placed his test files in that folder.

He ran the macro.

The error message is:

Run-time error '1004'

Method 'Open' of object 'Workbook' failed.

The highlighted code line is the "Set wb = ............"

The full macro is:

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 SubWe will try any suggestion you and the others can
come up with. Thanks for your time. Otto"Tom Ogilvy"
wrote in message
...
Since you don't show the code, have him send you the file
that fails to open and test the code with that file.
Perhaps the file is damaged or not a file Excel will
open.

--
Regards,
Tom Ogilvy

"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








































  #60   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files new line

Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this morning. Here's
the outcome.
I used the F8 to step through it and see what was happening and also just
ran the code and let her rip.
I got several messages regarding the files weren't processed, using the full
code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked, I got the
first two didn't work, but the rest did as they were supposed to, opened the
file and made the specific changes and closed the fiel.
The first two files weren't opening as they should and I then opened them
both manually and they opened and looked fine, and then I closed them out.
They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to open. The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will try new
files, but it would seem to me that if one or more works properly then
something in the file would be the problem. Unfortunately all files were
saved the same way and all were exported to excel in csv format, the files
were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it attempts to
start opening the file it would pause and make sure the file had enough time
to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub





  #61   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files new line

Hello again,
Otto and I have talking this morning and here are some clarifications about
what we have discovered.
first the source of my files are from a mainframe which saves as a csv file
and I open it in MS Excel. Once opened I save it as an Excel .xls file and
download it to my computer. All the files that we've been dealing with are
of this nature.

Otto has asked me to document the downloading process for these files and
post them here in case we have a issue with the excel files. I will repost
after this evening to see if I can document the process and recreate the
issues. If I cant' recreate then it will have to be in the initial files
that were saved in excel. If I do recreate the problem I'll return and ask
for more advice. I tryly appreciate all of you for sticking by and trying to
help me out.
Thanks

Bob Reynolds


Next Otto asked me to clean out the folder and then create new .xls files
(blank) and save them in the directory. The names were Book (1) (2) etc... I
have six of those files placed in the folder and we discovered there was no
problems with the opening and closing of the files. Otto gave me Debra's
maco from her werbsite and all seems well at this time.
"rjr" wrote in message
.. .
Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this morning.
Here's the outcome.
I used the F8 to step through it and see what was happening and also just
ran the code and let her rip.
I got several messages regarding the files weren't processed, using the
full code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked, I got
the first two didn't work, but the rest did as they were supposed to,
opened the file and made the specific changes and closed the fiel.
The first two files weren't opening as they should and I then opened them
both manually and they opened and looked fine, and then I closed them out.
They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to open. The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will try new
files, but it would seem to me that if one or more works properly then
something in the file would be the problem. Unfortunately all files were
saved the same way and all were exported to excel in csv format, the files
were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it attempts
to start opening the file it would pause and make sure the file had enough
time to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub





  #62   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Code looping through files

It ran ok for me.

But I would still add the check for lcase() and the chdrive, too.

And since you're opening, changing and saving the files, I would use Tom's
suggestion of getting a list of names first, then process the list.

rjr wrote:

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


--

Dave Peterson
  #63   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,090
Default Code looping through files new line

Bob neglected to mention one thing. This morning I had him clear out his
folder of all the .csv - .xls files. Then I had him create new blank Excel
files and save them into that folder. About 4-6 files. Then he ran the
code from Debra's site. It ran perfectly without a hitch. That's why I
asked him to document the procedure that was used to generate his original
files (the troublesome files) and post it here for all to peruse. I myself
have zero experience with importing non-Excel files into Excel. Otto
"rjr" wrote in message
. ..
Hello again,
Otto and I have talking this morning and here are some clarifications
about what we have discovered.
first the source of my files are from a mainframe which saves as a csv
file and I open it in MS Excel. Once opened I save it as an Excel .xls
file and download it to my computer. All the files that we've been dealing
with are of this nature.

Otto has asked me to document the downloading process for these files and
post them here in case we have a issue with the excel files. I will repost
after this evening to see if I can document the process and recreate the
issues. If I cant' recreate then it will have to be in the initial files
that were saved in excel. If I do recreate the problem I'll return and ask
for more advice. I tryly appreciate all of you for sticking by and trying
to help me out.
Thanks

Bob Reynolds


Next Otto asked me to clean out the folder and then create new .xls files
(blank) and save them in the directory. The names were Book (1) (2) etc...
I have six of those files placed in the folder and we discovered there was
no problems with the opening and closing of the files. Otto gave me
Debra's maco from her werbsite and all seems well at this time.
"rjr" wrote in message
.. .
Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this morning.
Here's the outcome.
I used the F8 to step through it and see what was happening and also just
ran the code and let her rip.
I got several messages regarding the files weren't processed, using the
full code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked, I got
the first two didn't work, but the rest did as they were supposed to,
opened the file and made the specific changes and closed the fiel.
The first two files weren't opening as they should and I then opened them
both manually and they opened and looked fine, and then I closed them
out. They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to open. The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will try new
files, but it would seem to me that if one or more works properly then
something in the file would be the problem. Unfortunately all files were
saved the same way and all were exported to excel in csv format, the
files were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it attempts
to start opening the file it would pause and make sure the file had
enough time to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub







  #64   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files new line

Thanks Tom, I'm going to sit with my wife tonight and see what options we
have. If there is the ability to direct save as a csv I'll give it a try. If
I recall correctly it can be done, but I must check to make sure. This
evening (late) I'm going to have a sample of everything I can get and then
do some troubleshooting and fact finding.

No there were no manual manipulations she just saved them in .xls format...
With everyone's help it seems that we've narrowed it down considerably, but
I won't give up yet.


Thanks so much to all
Bob Reynolds


"Tom Ogilvy" wrote in message
...
Why not let your excel code open the original CSV files, or did you do
some
manual manipulation to after you opened them manually.

You would point the code at a directory with the CSV files and change the
code to look for

*.csv rather than *.xls

--
Regards,
Tom Ogilvy





"rjr" wrote:

Hello again,
Otto and I have talking this morning and here are some clarifications
about
what we have discovered.
first the source of my files are from a mainframe which saves as a csv
file
and I open it in MS Excel. Once opened I save it as an Excel .xls file
and
download it to my computer. All the files that we've been dealing with
are
of this nature.

Otto has asked me to document the downloading process for these files and
post them here in case we have a issue with the excel files. I will
repost
after this evening to see if I can document the process and recreate the
issues. If I cant' recreate then it will have to be in the initial files
that were saved in excel. If I do recreate the problem I'll return and
ask
for more advice. I tryly appreciate all of you for sticking by and trying
to
help me out.
Thanks

Bob Reynolds


Next Otto asked me to clean out the folder and then create new .xls files
(blank) and save them in the directory. The names were Book (1) (2)
etc... I
have six of those files placed in the folder and we discovered there was
no
problems with the opening and closing of the files. Otto gave me Debra's
maco from her werbsite and all seems well at this time.
"rjr" wrote in message
.. .
Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this morning.
Here's the outcome.
I used the F8 to step through it and see what was happening and also
just
ran the code and let her rip.
I got several messages regarding the files weren't processed, using the
full code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked, I got
the first two didn't work, but the rest did as they were supposed to,
opened the file and made the specific changes and closed the fiel.
The first two files weren't opening as they should and I then opened
them
both manually and they opened and looked fine, and then I closed them
out.
They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to open. The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will try
new
files, but it would seem to me that if one or more works properly then
something in the file would be the problem. Unfortunately all files
were
saved the same way and all were exported to excel in csv format, the
files
were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it
attempts
to start opening the file it would pause and make sure the file had
enough
time to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub








  #65   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files new line

Tom, here is the answer to your question. When I opened the file it said it
was in csv format and questioned if I wanted to save it in that format. I
selected yes, gave it a name and saved it. I got a warning that possibly
some features of the file might not be supported in csv and did I want to
change the format. I said no and saved it with the csv designation.
Now when I double click it to open I get this warning, and it is labeled
***.csv. Excel has detected that "***.csv" is a SYLK file, but cannot load
it. Either the file has errors or it is not a SYLK file format. Click OK to
try to open the file in a different format.

I click OK and the file opens up, with the ***.csv in the header bar.
When I go to save it, I get a warning that it might contain features that
are not compatible with CSV. DO you want to keep the workbook in this
format?...............
I could only download 3 tonight and ran them with the only issue being a
msgbox appearing and saying that it is a SYLK file and can't be opened and
requireing me to click on yes or no to continue. When I click on yes the
code continues to run with no errors and performs as expected. I was
concerned about the warnings but they don't appear to be a problem. Are you
familiar with any of these issues or anyting I should look out for. I will
download more tomorrow and try all of the out again.
Thanks
BOB Reynolds

"Tom Ogilvy" wrote in message
...
You said:

from a mainframe which saves as a csv file


so there is no need to do anything but have your code open the existing
CSV
file as produced by the Mainframe. then in your code where you close the
file, you would do

wb.SaveAs Replace(wb.fullname,".csv",".xls"), xlWorkbook.Normal

So I don't know what you mean when you say

If there is the ability to direct save as a csv


--
Regards,
Tom Ogilvy


"rjr" wrote:

Thanks Tom, I'm going to sit with my wife tonight and see what options we
have. If there is the ability to direct save as a csv I'll give it a try.
If
I recall correctly it can be done, but I must check to make sure. This
evening (late) I'm going to have a sample of everything I can get and
then
do some troubleshooting and fact finding.

No there were no manual manipulations she just saved them in .xls
format...
With everyone's help it seems that we've narrowed it down considerably,
but
I won't give up yet.


Thanks so much to all
Bob Reynolds


"Tom Ogilvy" wrote in message
...
Why not let your excel code open the original CSV files, or did you do
some
manual manipulation to after you opened them manually.

You would point the code at a directory with the CSV files and change
the
code to look for

*.csv rather than *.xls

--
Regards,
Tom Ogilvy





"rjr" wrote:

Hello again,
Otto and I have talking this morning and here are some clarifications
about
what we have discovered.
first the source of my files are from a mainframe which saves as a csv
file
and I open it in MS Excel. Once opened I save it as an Excel .xls file
and
download it to my computer. All the files that we've been dealing with
are
of this nature.

Otto has asked me to document the downloading process for these files
and
post them here in case we have a issue with the excel files. I will
repost
after this evening to see if I can document the process and recreate
the
issues. If I cant' recreate then it will have to be in the initial
files
that were saved in excel. If I do recreate the problem I'll return and
ask
for more advice. I tryly appreciate all of you for sticking by and
trying
to
help me out.
Thanks

Bob Reynolds


Next Otto asked me to clean out the folder and then create new .xls
files
(blank) and save them in the directory. The names were Book (1) (2)
etc... I
have six of those files placed in the folder and we discovered there
was
no
problems with the opening and closing of the files. Otto gave me
Debra's
maco from her werbsite and all seems well at this time.
"rjr" wrote in message
.. .
Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this
morning.
Here's the outcome.
I used the F8 to step through it and see what was happening and also
just
ran the code and let her rip.
I got several messages regarding the files weren't processed, using
the
full code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked, I
got
the first two didn't work, but the rest did as they were supposed
to,
opened the file and made the specific changes and closed the fiel.
The first two files weren't opening as they should and I then opened
them
both manually and they opened and looked fine, and then I closed
them
out.
They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to open.
The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will
try
new
files, but it would seem to me that if one or more works properly
then
something in the file would be the problem. Unfortunately all files
were
saved the same way and all were exported to excel in csv format, the
files
were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it
attempts
to start opening the file it would pause and make sure the file had
enough
time to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub













  #66   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files new line

That was the alert I was getting about the SYLK. Thank you for that code
I'll certainly add it and report back.

Bob
"Tom Ogilvy" wrote in message
...
I say you should get no warning on opening if the file is a CSV file unless
the first two characters in the file are "ID".
http://support.microsoft.com/kb/323626
"SYLK: File format is not valid" error message when you open file

If your file is like that stated in the article, then you can ovecome this
prompt (it won't appear) in your code with

Application.DisplayAlerts = False '<== added
wb = Workbooks.Open(sPath & "\" & filename)
Application.DisplayAlerts = True '<=== added

(I don't remember you exact line of code, so this is representative).

--
Regards,
Tom Ogilvy






"rjr" wrote:

Tom, here is the answer to your question. When I opened the file it said
it
was in csv format and questioned if I wanted to save it in that format. I
selected yes, gave it a name and saved it. I got a warning that possibly
some features of the file might not be supported in csv and did I want to
change the format. I said no and saved it with the csv designation.
Now when I double click it to open I get this warning, and it is labeled
***.csv. Excel has detected that "***.csv" is a SYLK file, but cannot
load
it. Either the file has errors or it is not a SYLK file format. Click OK
to
try to open the file in a different format.

I click OK and the file opens up, with the ***.csv in the header bar.
When I go to save it, I get a warning that it might contain features that
are not compatible with CSV. DO you want to keep the workbook in this
format?...............
I could only download 3 tonight and ran them with the only issue being a
msgbox appearing and saying that it is a SYLK file and can't be opened
and
requireing me to click on yes or no to continue. When I click on yes the
code continues to run with no errors and performs as expected. I was
concerned about the warnings but they don't appear to be a problem. Are
you
familiar with any of these issues or anyting I should look out for. I
will
download more tomorrow and try all of the out again.
Thanks
BOB Reynolds

"Tom Ogilvy" wrote in message
...
You said:

from a mainframe which saves as a csv file

so there is no need to do anything but have your code open the existing
CSV
file as produced by the Mainframe. then in your code where you close
the
file, you would do

wb.SaveAs Replace(wb.fullname,".csv",".xls"), xlWorkbook.Normal

So I don't know what you mean when you say

If there is the ability to direct save as a csv

--
Regards,
Tom Ogilvy


"rjr" wrote:

Thanks Tom, I'm going to sit with my wife tonight and see what options
we
have. If there is the ability to direct save as a csv I'll give it a
try.
If
I recall correctly it can be done, but I must check to make sure. This
evening (late) I'm going to have a sample of everything I can get and
then
do some troubleshooting and fact finding.

No there were no manual manipulations she just saved them in .xls
format...
With everyone's help it seems that we've narrowed it down
considerably,
but
I won't give up yet.


Thanks so much to all
Bob Reynolds


"Tom Ogilvy" wrote in message
...
Why not let your excel code open the original CSV files, or did you
do
some
manual manipulation to after you opened them manually.

You would point the code at a directory with the CSV files and
change
the
code to look for

*.csv rather than *.xls

--
Regards,
Tom Ogilvy





"rjr" wrote:

Hello again,
Otto and I have talking this morning and here are some
clarifications
about
what we have discovered.
first the source of my files are from a mainframe which saves as a
csv
file
and I open it in MS Excel. Once opened I save it as an Excel .xls
file
and
download it to my computer. All the files that we've been dealing
with
are
of this nature.

Otto has asked me to document the downloading process for these
files
and
post them here in case we have a issue with the excel files. I will
repost
after this evening to see if I can document the process and
recreate
the
issues. If I cant' recreate then it will have to be in the initial
files
that were saved in excel. If I do recreate the problem I'll return
and
ask
for more advice. I tryly appreciate all of you for sticking by and
trying
to
help me out.
Thanks

Bob Reynolds


Next Otto asked me to clean out the folder and then create new .xls
files
(blank) and save them in the directory. The names were Book (1) (2)
etc... I
have six of those files placed in the folder and we discovered
there
was
no
problems with the opening and closing of the files. Otto gave me
Debra's
maco from her werbsite and all seems well at this time.
"rjr" wrote in message
.. .
Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this
morning.
Here's the outcome.
I used the F8 to step through it and see what was happening and
also
just
ran the code and let her rip.
I got several messages regarding the files weren't processed,
using
the
full code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked,
I
got
the first two didn't work, but the rest did as they were supposed
to,
opened the file and made the specific changes and closed the
fiel.
The first two files weren't opening as they should and I then
opened
them
both manually and they opened and looked fine, and then I closed
them
out.
They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to
open.
The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will
try
new
files, but it would seem to me that if one or more works properly
then
something in the file would be the problem. Unfortunately all
files
were
saved the same way and all were exported to excel in csv format,
the
files
were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it
attempts
to start opening the file it would pause and make sure the file
had
enough
time to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub














  #67   Report Post  
Posted to microsoft.public.excel.programming
rjr rjr is offline
external usenet poster
 
Posts: 50
Default Code looping through files new line

Tom,

Thank you for that bit of code. It worked fine and I was quite perplexed on
how to make the apostraphe appear. Otto is helping me and we're using the
snippet you provided here and once again Thanks and if I need to bring up
other questions, I'll post in a new question cause this is hard to follow.
My Thanks to all,


Bob Reynolds
"Tom Ogilvy" wrote in message
...
I say you should get no warning on opening if the file is a CSV file unless
the first two characters in the file are "ID".
http://support.microsoft.com/kb/323626
"SYLK: File format is not valid" error message when you open file

If your file is like that stated in the article, then you can ovecome this
prompt (it won't appear) in your code with

Application.DisplayAlerts = False '<== added
wb = Workbooks.Open(sPath & "\" & filename)
Application.DisplayAlerts = True '<=== added

(I don't remember you exact line of code, so this is representative).

--
Regards,
Tom Ogilvy






"rjr" wrote:

Tom, here is the answer to your question. When I opened the file it said
it
was in csv format and questioned if I wanted to save it in that format. I
selected yes, gave it a name and saved it. I got a warning that possibly
some features of the file might not be supported in csv and did I want to
change the format. I said no and saved it with the csv designation.
Now when I double click it to open I get this warning, and it is labeled
***.csv. Excel has detected that "***.csv" is a SYLK file, but cannot
load
it. Either the file has errors or it is not a SYLK file format. Click OK
to
try to open the file in a different format.

I click OK and the file opens up, with the ***.csv in the header bar.
When I go to save it, I get a warning that it might contain features that
are not compatible with CSV. DO you want to keep the workbook in this
format?...............
I could only download 3 tonight and ran them with the only issue being a
msgbox appearing and saying that it is a SYLK file and can't be opened
and
requireing me to click on yes or no to continue. When I click on yes the
code continues to run with no errors and performs as expected. I was
concerned about the warnings but they don't appear to be a problem. Are
you
familiar with any of these issues or anyting I should look out for. I
will
download more tomorrow and try all of the out again.
Thanks
BOB Reynolds

"Tom Ogilvy" wrote in message
...
You said:

from a mainframe which saves as a csv file

so there is no need to do anything but have your code open the existing
CSV
file as produced by the Mainframe. then in your code where you close
the
file, you would do

wb.SaveAs Replace(wb.fullname,".csv",".xls"), xlWorkbook.Normal

So I don't know what you mean when you say

If there is the ability to direct save as a csv

--
Regards,
Tom Ogilvy


"rjr" wrote:

Thanks Tom, I'm going to sit with my wife tonight and see what options
we
have. If there is the ability to direct save as a csv I'll give it a
try.
If
I recall correctly it can be done, but I must check to make sure. This
evening (late) I'm going to have a sample of everything I can get and
then
do some troubleshooting and fact finding.

No there were no manual manipulations she just saved them in .xls
format...
With everyone's help it seems that we've narrowed it down
considerably,
but
I won't give up yet.


Thanks so much to all
Bob Reynolds


"Tom Ogilvy" wrote in message
...
Why not let your excel code open the original CSV files, or did you
do
some
manual manipulation to after you opened them manually.

You would point the code at a directory with the CSV files and
change
the
code to look for

*.csv rather than *.xls

--
Regards,
Tom Ogilvy





"rjr" wrote:

Hello again,
Otto and I have talking this morning and here are some
clarifications
about
what we have discovered.
first the source of my files are from a mainframe which saves as a
csv
file
and I open it in MS Excel. Once opened I save it as an Excel .xls
file
and
download it to my computer. All the files that we've been dealing
with
are
of this nature.

Otto has asked me to document the downloading process for these
files
and
post them here in case we have a issue with the excel files. I will
repost
after this evening to see if I can document the process and
recreate
the
issues. If I cant' recreate then it will have to be in the initial
files
that were saved in excel. If I do recreate the problem I'll return
and
ask
for more advice. I tryly appreciate all of you for sticking by and
trying
to
help me out.
Thanks

Bob Reynolds


Next Otto asked me to clean out the folder and then create new .xls
files
(blank) and save them in the directory. The names were Book (1) (2)
etc... I
have six of those files placed in the folder and we discovered
there
was
no
problems with the opening and closing of the files. Otto gave me
Debra's
maco from her werbsite and all seems well at this time.
"rjr" wrote in message
.. .
Started new here, the others are getting pretty deep.

OK, Have tried using this with several different issues this
morning.
Here's the outcome.
I used the F8 to step through it and see what was happening and
also
just
ran the code and let her rip.
I got several messages regarding the files weren't processed,
using
the
full code. and I got a couple there were processed.
then using the test code Tom wanted me to last night that worked,
I
got
the first two didn't work, but the rest did as they were supposed
to,
opened the file and made the specific changes and closed the
fiel.
The first two files weren't opening as they should and I then
opened
them
both manually and they opened and looked fine, and then I closed
them
out.
They are the first two in alphabetical order.
I then changed the order and was able to get two of the 22 to
open.
The
others seemed to hang on
et wb = Workbooks.Open(fl.Path)
and then went to the error. I've got to go to work today and will
try
new
files, but it would seem to me that if one or more works properly
then
something in the file would be the problem. Unfortunately all
files
were
saved the same way and all were exported to excel in csv format,
the
files
were opened and then saved as in xls format.

Is there a need to put a slight pause into the code that when it
attempts
to start opening the file it would pause and make sure the file
had
enough
time to open, or am I asking a stupid thing???

Thanks again
Bob Reynolds
Sub AllFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
sFol = "C:\temp"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
For Each fl In fld.Files
if lcase(fl.Path) < lcase(thisworkbook.Fullname) then
If fl.Name Like "*.xls" Then
On Error Resume Next
Set wb = Workbooks.Open(fl.Path)
if err.number = 0 then
MsgBox wb.FullName
wb.Close
else
msgbox "Not processed: " & fl.Path
err.Clear
end if
On Error goto 0
End If
End if
Next
End Sub














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping thru Files In FolderSub Jim May Excel Programming 4 August 2nd 06 11:49 PM
Looping through excel files to add to a new workbook Geoff Excel Programming 8 April 29th 06 11:54 AM
Looping through files in a folder prepotency[_12_] Excel Programming 1 July 13th 05 04:23 PM
Looping thru files extracting data gtslabs[_2_] Excel Programming 3 June 3rd 05 04:22 PM
Looping thru files Tom Excel Programming 4 January 9th 04 06:05 PM


All times are GMT +1. The time now is 02:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"