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