Code looping through files

[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.

 

[Dave Peterson]

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.

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,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

How about posting the exact code that you're using?

 

[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

 

[rjr]

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

 

[rjr]

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

Bob

 

[rjr]

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