Code looping through files

[Gary Keramidas]

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?

 

[rjr]

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

 

[Tom Ogilvy]

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

 

[rjr]

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

 

[Gary Keramidas]

are you using xp?

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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?

 

[rjr]

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

 

[rjr]

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

 

[Gary Keramidas]

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.

 

[Tom Ogilvy]

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.

 

[rjr]

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

 

[rjr]

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]

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

 

[rjr]

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

 

[rjr]

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

 

[rjr]

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

 

[rjr]

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.

 

[Otto Moehrbach]

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]

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