Run Time Error 53 on FileCopy command

[justin.arnold2]

We have a spreadsheet with a few buttons on it. One of the buttons is
called Distribute Sheet. When the user clicks this button the excel
sheet is first converted to a pdf document and then stored on a
network drive. Once this is complete the source file is copied to
another network location and renamed "today's date".pdf. We've been
running this without a problem for months. All of a sudden we've been
receiving the Run Time Error 53 on the FileCopy SourceFile,
DestinationFile line. There have been no changes to the code nor have
there been changes to the users PC. Can someone help me sort this out?
Please see the code below. Any help would be greatly apprecaited.
Thanks

Private Sub cmdDistributeNightOrders_Click()

Dim fName
Dim MyMonth
Dim MyDay
Dim MyYear
Dim SourceFile, DestinationFile As String

MyMonth = Month(Now)
MyDay = Day(Now)
MyYear = Year(Now)

fName = MyMonth & "_" & MyDay & "_" & MyYear & ".pdf"

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets(Array("Night Orders", "Master Tank List")).Select

'****
'Generate Adobe File
'****

'**********************************************
'Christina's Adobe printer settings
'**********************************************

Application.ActivePrinter = "Adobe PDF on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne04:", Collate:=True

'****
'Pauses application to provide time for Adobe file to be generated
'****

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'****
'Copies Active Night Order to Archive folder (renames file using
current date)
'****

SourceFile = "\\Newellfile\public\Shared\Night Orders\Active Night
Orders\night orders.pdf"
DestinationFile = "\\Newellfile\public\Shared\Night Orders\Archive
Night Orders\" & fName.pdf

FileCopy SourceFile, DestinationFile

Worksheets("Night Orders").Activate


End Sub

 

[Jim Thomlinson]

Define what a runtime Error 53 is. What is the text of the error message. As
a guess though has anthing changed in terms of the directory structure or the
directory access?

As a side note SourceFile is not declared correctly. While it appears ot be
of type string it is actually of type variant. Check out this link...

http://www.cpearson.com/excel/variables.htm

NOTE: When you declare more than one variable on a single line, each
variable must be given its own type declaration. The declaration for one
variable does not affect the type of any other variable. For example, the
declaration

Dim X, Y, Z As Single

is not the same as declaration

Dim X As Single, Y As Single, Z As Single

It is the same as

Dim X As Variant, Y As Variant, Z As Single

or, more explicitly, as

Dim X As Variant
Dim Y As Variant
Dim Z As Single

 

[justin.arnold2]

Thanks for the tips. The error message reads:

Run-time error '53':
File not Found

It then gives me the option to End, Debug, or Help

When I click Debug it highlights the (FileCopy SourceFile,
DestinationFile) line.

Also, I'll change my dim statements for the SourceFile

If you have any more information on this error please let me know.
Thanks again.

 

[Dave Peterson]

Maybe you could check to see if the file exists first:

Dim TestStr as string 'add this near the other declarations
.....


SourceFile _
= "\\Newellfile\public\Shared\Night Orders\Active Night Orders\" _
& "night orders.pdf"

'you had a typo in your original code!!
DestinationFile _
= "\\Newellfile\public\Shared\Night Orders\Archive Night Orders\" _
& fName & ".pdf"

teststr = ""
on error resume next
teststr = dir(sourcefile)
on error goto 0

if teststr = "" then
msgbox "Failed--the sourcefile wasn't found!"
else
FileCopy SourceFile, DestinationFile
end if

Worksheets("Night Orders").Activate

End Sub

If you add
Option Explicit
to the top of this module, then your typo would have been found.

 

[justin.arnold2]

Maybe you could check to see if the file exists first:

Dim TestStr as string  'add this near the other declarations
....

    SourceFile _
       = "\\Newellfile\public\Shared\Night Orders\Active Night Orders\" _
           & "night orders.pdf"

    'you had a typo in your original code!!
    DestinationFile _
       = "\\Newellfile\public\Shared\Night Orders\Archive Night Orders\" _
           & fName & ".pdf"

    teststr = ""
    on error resume next
    teststr = dir(sourcefile)
    on error goto 0

    if teststr = "" then
        msgbox "Failed--the sourcefile wasn't found!"
    else
        FileCopy SourceFile, DestinationFile
    end if

    Worksheets("Night Orders").Activate

End Sub

If you add
Option Explicit
to the top of this module, then your typo would have been found.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks. You mentioned that there was a typo in my original code. What
exactly were you referring to? Thanks again.

 

[Dave Peterson]

Your original code had a line like:

DestinationFile _
= "\\Newellfile\public\Shared\Night Orders\Archive Night Orders\" & fName.pdf

You don't have a variable named fname.pdf.

I guess the actual error would be something else, though ("Object required" is
the error I got.)

 

[justin.arnold2]

Your original code had a line like:

DestinationFile _
 = "\\Newellfile\public\Shared\Night Orders\Archive Night Orders\" & fName.pdf

You don't have a variable named fname.pdf.

I guess the actualerrorwould be something else, though ("Object required" is
theerrorI got.)








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks again for your help. I went ahead an modified the code
according to your previous posts however the problem is still
occuring. I've verified that the source file exists. One thing that I
am confused about is the fact that I've used the original code for
several months and we haven't had a problem. As far as I can tell,
nothing has changed. What gives?

 

[Dave Peterson]

I don't understand what problem is occurring.

Maybe it's time to post your current code and describe the problem in more
detail.

 

[justin.arnold2]

Thanks. We have a workbook that multiple employees (with multiple
versions of Excel 2000, 2002, 2003) update on a daily basis. There are
3 sheets in the book however the problem is occuring with only one.
The sheet that we're having problems with (Night Orders) has 4 buttons
on it. Distribute, E-mail, Update and Close. The E-mail, Update and
Close buttons work without a problem. The Distribute button is what
I'm having problems with. When a user clicks on the distribute button
the 'Night Orders' sheet is first converted to a .pdf (Night
Orders.pdf) and saved to a public share on our file server. Once the
file is copied to our public share, the Night Orders.pdf file is then
copied to an Archive Folder on the same public share and renamed to
reflect the current date (3_12_2008.pdf). This allows us to keep up
with regulatory requirements. When a user click on the distribute
button the file is successfully converted to a .pdf and stored on the
public share (Night Orders.pdf). The problem occurs in the second part
of the code when the initial file (Night Orders.pdf) is copied to the
Archive folder and renamed. This is the point in which we receive the
'run time error 53' 'file not found' error message. When I click
debug, the code editor is highlighting the 'FileCopy SourceFile,
DestinationFile' line. I'm assuming that this is because the Source
File (Night Orders.pdf) cannot be found (If I browse to the public
share the file DOES exist). What's confusing is the fact that the code
has not been changed in any way over the last 6 months and this
problem just started occuring about 4 weeks ago. I've checked the
permission on the destination location(s) and everything is correct. I
hope this is enough detail for you to assist me with this issue. The
code that you requested is below. Thanks so much for your help.


Private Sub cmdDistributeNightOrders_Click()

Dim fName
Dim MyMonth
Dim MyDay
Dim MyYear
Dim SourceFile As String
Dim DestinationFile As String

MyMonth = Month(Now)
MyDay = Day(Now)
MyYear = Year(Now)

fName = MyMonth & "_" & MyDay & "_" & MyYear & ".pdf"

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets(Array("Night Orders", "Master Tank List")).Select

'****
'Generate Adobe File
'****
'
'**********************************************
'Christina's Adobe printer settings
'**********************************************

Application.ActivePrinter = "Adobe PDF on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne05:", Collate:=True

'****
'Pauses application to provide time for Adobe file to be generated
'****

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'****
'Copies Active Night Order to Archive folder (renames file using
current date)
'****

SourceFile = "\\Newellfile\Public\Shared\Night Orders\Active Night
Orders\Night Orders.pdf"
DestinationFile = "\\Newellfile\Public\Shared\Night Orders\Archive
Night Orders\" & fName

FileCopy SourceFile, DestinationFile

Worksheets("Night Orders").Activate


End Sub

 

[Dave Peterson]

I don't see where you included any of the previous suggestion to look for the
file first.

But if the file is there when you check manually, maybe delaying more than 5
seconds would help. (Just a guess.)

 

[justin.arnold2]

I actually implemented the following on a copy of the file and it's
indicating that the source file does not exist. I'll try giving Adobe
some more time to process and see what happens. Thanks once again for
your help.

Dim TestStr as string 'add this near the other declarations
.....


SourceFile _
= "\\Newellfile\public\Shared\Night Orders\Active Night Orders
\" _
& "night orders.pdf"


'you had a typo in your original code!!
DestinationFile _
= "\\Newellfile\public\Shared\Night Orders\Archive Night Orders
\" _
& fName & ".pdf"


teststr = ""
on error resume next
teststr = dir(sourcefile)
on error goto 0


if teststr = "" then
msgbox "Failed--the sourcefile wasn't found!"
else
FileCopy SourceFile, DestinationFile
end if


Worksheets("Night Orders").Activate


End Sub