Workbook.Open fail in 2003, good in XP

[Otto Moehrbach]

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]

Changing the directory doesn't change the drive.

I'd change the drive, too:

chdrive ThePath
chdir thepath

=====
And what's the name of the .csv file and the name of the .xls file?

 

[Otto Moehrbach]

Dave
The code I posted is just a snippet of the code I have. I deleted all the
other parts of the code and posted just the part that exhibited the problem.
And what I posted does exhibit the Open problem (with the OP's computer, not
mine).
The overall code is a For loop to open the first .csv file in the Temp
folder, copy the lone sheet and paste it into the active workbook, close the
..csv file, and move on to the next .csv file. The code works fine in my
computer but stops at the Open line with the first .csv file in the OP's
computer.
The .csv files have names like BIO.csv, POD.csv, MUA.csv, etc. The lone
..xls file in the folder is the destination file for all the copied sheets
and has the name "Daily Error report MASTER.xls"
Thanks for helping me with this. Otto

 

[Dave Peterson]

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

 

[Dave Peterson]

I don't see anything wrong with the code.

Maybe the other suggestion will help you debug the problem.

 

[Otto Moehrbach]

Dave
That didn't do anything with the problem. I include the code below.
The OP's computer errors out on the Open line and mine doesn't (my system
opens the file). Thanks again. Otto
Sub testopen()
ThePath = "C:\Temp\"
ChDrive ThePath
ChDir ThePath
TheFile = Dir("*.csv")
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & TheFile)
Application.DisplayAlerts = True
End Sub

 

[Otto Moehrbach]

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]

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]

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]

I'd remove that .displayalerts message.

Maybe it's hiding something that's causing the trouble.

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

 

[Tom Ogilvy]

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

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

 

[Tom Ogilvy]

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

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

 

[Otto Moehrbach]

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

 

[Dave Peterson]

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

 

[rjr]

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

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