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.

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 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
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" wrote in message
...
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 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

I don't see anything wrong with the code.

Maybe the other suggestion will help you debug the problem.

Otto Moehrbach wrote:

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" wrote in message
...
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 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
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
"Dave Peterson" wrote in message
...
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 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

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

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

I'd remove that .displayalerts message.

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

Otto Moehrbach wrote:

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

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