Code looping through files

[Gary Keramidas]

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

 

[Otto Moehrbach]

Chip
I will check with him this morning and let you know. It never occurred
to me that he might have a Mac. Otto

 

[Dave Peterson]

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.

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

 

[Dave Peterson]

In a different branch of this thread, Bob wrote that he saw this in the msgbox
for the full name:

C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls

It doesn't look like it's a MAC (from a non-MAC user).

 

[Chip Pearson]

In a different branch of this thread,

It's a long thread and I jumped in the middle of it without reading
everything.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dave Peterson]

I had to go back to make sure that my memory wasn't playing tricks on me after I
read your post.

 

[Otto Moehrbach]

Dave
Bob replied in this thread that it was not a MAC. Otto

 

[Dave Peterson]

I suffer the same problem as Chip <vbg>.

Dave
Bob replied in this thread that it was not a MAC. Otto

 

[rjr]

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

 

[Gary Keramidas]

the code i posted worked in xl2003

--


Gary

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]

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

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

 

[Tom Ogilvy]

Certainly a reasonable suggestion, but more consistent with a file not found
type error.

 

[rjr]

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

 

[Tom Ogilvy]

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

 

[rjr]

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

 

[rjr]

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]

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

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

 

[Tom Ogilvy]

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.

 

[rjr]

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]

Sorry, that would be

Help=>About Microsoft Excel

Typo.