Hello to all and thanks for all the participation and help.
They are downloaded directly from a mainframe and say they are csv format.
Tom had given me a microsoft paper on how to avoid the SYLK error warning
and it did just that. Initially I had an issue with two of the files, as
there was something in them that caused the code to stop but everyone else's
had continued. When I sent Otto the files he never had an issue and for a
while I didn't, but I beleive somewhere in this testing he and I are doing
mine got corrupted. As he said, when he sent me his files, that portion of
the code ran through just fine. I've sent him some and when I hear back from
him he will either have created the same problem, or not. He has been so
patient as have all of you that are attempting to help me out, and it's
certainly appreciated.
Just for the record: It's not a Mac; I don't zip the files; once I download
them they stay put; and will all be in csv format. I'm running XL 2003 with
Windows XP. I have all the references that were mentioned in these threads
turned on ie Excel 11 and Windows 11 under references.

I look forward to reporting the completion of this exercise, but it does
show that even the slightest malfunction or deviation in saving or
corruption can be a reason for hair pulling.

Any other questions I'll be happy to give info and as I said we'll report
back here with an outcome..

Bob Reynolds

"Dave Peterson" wrote in message
...
Does he zip the files when he sends them?

Maybe they get changed when he sends them to you as plain text (like UNIX
files
vs DOS files).



Otto Moehrbach wrote:

Tom and Dave

Thanks for your help with this. The OP (name is Bob) and I came up
with
something late yesterday that may shed some light on this.

It's about the 22 .csv files that he is working with. He downloads
these
files from some mainframe and places them in his Temp folder. He emails
those 22 files to me to use in my development of the code. I send him
the
code, he tries it, he'd like this change, I send him new code, he'd like
that change, and so on.

The point I am making here is that I do not change anything about the
22
files I have. They are simply tools to me. Apparently he has done
something to his files (maybe saved them at some point, maybe just some
of
them?). I say that because my code worked for him at first. Then it
didn't.
Because the files mean something to him, perhaps he has been opening them
and whatever. Something more than the Open - Copy - Close that my code
does
with them.

But late yesterday I sent him my 22 files and told him to
replace his 22 files with my 22 files. He did and the code worked just
fine
for him.

I told him to find some of those old 22 files and make the
problem repeat. He is doing that now. Then he will send me those
"problem"
files and I will see how they do on my system.

When my code is complete and he goes into production with
that
code, he will have no reason to do anything with those 22 files (he gets
a
new batch every so often) except place them in the Temp folder and run
the
code. It's just during this development phase that he might have done
other
things with those files. I asked him to scratch his memory about what he
might have done with those files. More to come on that.

I will be out-of-pocket for most of the day. Perhaps Bob
will
step into this thread in the meantime.

I would like your comments on this. Thanks again. Otto

"Tom Ogilvy" wrote in message
...
One Additional thought. Comment out the Application.DisplayAlerts
lines
(just for a test - eventually you will want to restore them) and see if
he
gets a prompt. It may be telling him the file is in use or something
rather than just the SYLK warning.

If nothing works, send me a copy of two of the files and I will see if
I
can work something up for you that doesn't require workbook open or do
it
yourself using Chips code at

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy




"Tom Ogilvy" wrote in message
...
Assuming the incorrect SYLK format isn't


My guess would be that he has saved the files with a CSV extension,
but
used a file type of xlWorkbookNormal since the previous problem he had
was in opening workbooks.

Have him see if he can open the file in notepad manually and see if
the
file looks like a straight text file.

If it doesn't appear as a straight CSV/Text file, then it is probably
a
workbook. If it does,

try adding a dummy line at the top of the file - something like
AAAAAAA<cr

If you do that , then Do it to all the CSV files and see if they will
then open (to work around the SYLK problem).

--
Regards,
Tom Ogilvy



"Otto Moehrbach" wrote in message
...
Dave
Yes, I setup my windows to display the full file name and those
files
are .csv files. I didn't use your code regarding the message box. I
only changed the code I posted to include your line (TheFile = ...
shown
below) in place of like lines I had (they are remarked out). Note
that
this code works on my XP Home but not on the OP's 2003 Pro.
Just now I contacted him by phone and changed the macro to have only
these lines of code:
Sub testopen()
ThePath = "C:\Temp\"
TheFile = Dir(ThePath & "*.csv")
If TheFile = "" Then
MsgBox "not found"
Else
MsgBox "found"
End If
End Sub
******He got the message "found".******
I then had him replace the "found" with TheFile in the "Else" MsgBox.
He ran it and got the name of the first .csv file in the folder.

Below is the same macro with the Open line. His system errors on the
Open line. Mine opens the file.
Sub testopen()
ThePath = "C:\Temp\"
TheFile = Dir(ThePath & "*.csv")
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & TheFile)
Application.DisplayAlerts = True
End Sub

Please stay with me on this if you can. To muddy the water some
more,
this code DID work on his computers (he has 2, both with 2003 Pro,
both
with the same error). Now it doesn't. Here is what I just did. I
have
HIS .csv files on my computer. I just sent him all my .csv files for
him to place into his Temp folder IN PLACE OF his .csv files. I'll
let
you know either way. Thanks for your time. Otto
"Dave Peterson" wrote in message
...
When you added the msgbox "not found", did you see the msgbox?

Are you sure that the files are really .csv files?

Are you looking at the whole file name in windows explorer?

Inside Windows Explorer
Tools|Folder Options|View tab|
Make sure "hide extensions for known file types" is not checked.

This is still not a Mac, right?

I'm out of suggestions, though.

Otto Moehrbach wrote:

Dave
We both changed the code as you said and it made no difference.
He got
the error and I didn't. Thanks and keep trying. The code is
below.
Otto
Sub testopen()
ThePath = "C:\Temp\"
'ChDrive ThePath
'ChDir ThePath
'TheFile = Dir("*.csv")
TheFile = Dir(ThePath & "*.csv")
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & TheFile)
Application.DisplayAlerts = True
"Dave Peterson" wrote in message
...
You may want to avoid the chdrive/chdir completely:

TheFile = Dir(ThePath & "*.csv")
if TheFile = "" then
msgbox "not found
else
'do the work
end if

Otto Moehrbach wrote:

Excel XP & Win XP
I'm helping an OP. He has Excel 2003 Pro.
He and I have now run the code shown below several times on both
our
computers. His computer fails on the "Set wb =
Workbooks.Open.........."
line every time.
Note that the code is looking for a .csv file. The Temp folder
has
a
bunch
of those. It also has one .xls file. We both changed the
".csv"
to
".xls"
in the below code and ran the code again.
In ALL cases, the file opens on my computer and NEVER opens on
his.
The error is always on the "Set wb..." line.
My question: Why does this code fail on his computer and not on
mine?
Thanks for your time. Otto
Sub testopen()
ThePath = "C:\Temp\"
ChDir ThePath
TheFile = Dir("*.csv")
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & TheFile)
Application.DisplayAlerts = True
End Sub
PS: I sent him the entire file, so it's not a case of
copy/paste.

--

Dave Peterson

--

Dave Peterson







--

Dave Peterson