My code works fine on winXP but get error 52 on win7

Hi all

I will start with saying i am very new to VB code and have taught myself everything so far and sorry if this is the wrong forum.
i have created a code that is ment to copy and paste data form a specific location to another this all works ok.
my problem is people at my work dont always get that the filename must always be the same so my code will see it.
I resolved this by making it bring up a error box to tell them what to do. all worked well on computer with win XP
but now on win7 OS it just gives me error 52 al the time unless the path actually exists
here is my code

Sub report433()

If Dir("E:\Todays data\[433] bag throughput.xls") = "" Then
MsgBox "file not found - make sure file is saved into- E:\todays data\ and file name is- [433] bag throughput", vbExclamation
Exit Sub

End If

Dim wbMyBook As Workbook
Set wbMyBook = ActiveWorkbook
Workbooks.Open ("E:\Todays data\[433] bag throughput.xls")

ActiveWorkbook.Sheets("sheet1").Range("A1:Z1000").Copy Destination:=wbMyBook.Sheets("433").Range("A1")
ActiveWorkbook.Close

End Sub

Code:

---

If Dir("E:\Todays data\[433] bag throughput.xls") = "" Then
      MsgBox "file not found - make sure file is saved into- E:\todays data\ and file name is- [433] bag throughput", vbExclamation
      Exit Sub
End If

---

Where is this pointing too, a real filename or a filename made at runtime??? This is what is happening. Also you are using VBA, so you have to move this Thread into the VBA Section of the Forum...

problem only occurs when target doesnt exist ie. filename different which is mostly the case
it used to just bring up a msg box stating what to do as in my code but
now it just has error 52

how do i transfer this post

What do you mean Exit Sub - Where is this pointing to?

I see nothing wrong with his code. The only thing I question is the use of brackets. It valid on XP but maybe they are not allowed on Win7, I don't know.

Exit sub just ends the program after you ok the msg box

My first thought is that there is no E drive on the Windows 7 machine, or if it is the same machine with a dual boot possibly the drive letters are swapped when windows 7 is booted.

I am a bit confused by the error number <> message as they do not match

53 is file not found
52 is bad filename or number

FYI if I try this under XP

Code:

---

MsgBox Dir("j:\myfolder\text.jpg")

---

I get error 52 J drive exists but it is a DVD drive with no disc in it
If I switch the J to m which does not exist I get no error

So is this E drive a CD/DVD with no disc in the drive?

ahh yes e drive has become the cd/dvd for win7 computer
have changed drive letters and all is now good

thanks for your help guys

When you try to call a Drive, then please by all means try the On Error Resume Next command. This will then be able to hide all errors, when you are working with Drives, as such and this also works for the Directories/Folders and then also looking for Files, in general and advanced operations, as well...

I disagree, On Error Resume Next is not the way to go. And if you do use it then you need to check the error code after each line where an error might occur otherwise your code and the user will think that there is nothing wrong and this can lead to all sorts of problems.

Much better to use an Error Handler then you can choose to ignore some errors and let the user know about others, For example you may want to tell them to place a disk in the drive or that an error has occured and the process can not be completed or whatever the case may be.

In some cases OERN will workout but it is not good practice to use and a very bad habit to develop.

Quote:

---

Originally Posted by DataMiser

In some cases OERN will workout but it is not good practice to use and a very bad habit to develop.

---

Yes, but it makes all the Errors go away and we don't have to think about them ever again - ah bliss :D:D:D

Yeah, who wants to mess around with errors anyway? Just let it fly and pretend nothing wrong has happened.

Well I agree that hiding errors isn't the way to go. But they are only used for certain types of processes like File I/O Operations, etc...