Runtime error 53 file not found vba

[dimsums]

Hi all,
For the past few weeks i have been using a code for my system and it has been working perfectly fine.
Recently i started getting error 53 and i have made no changes to the code.
Could someone tell me why?
(sorry for the messy code! I am still learning VBA)

Sub TransferDataEmail()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
'Dim ws3 As Worksheet
'Dim wkb1 As Workbook
'Dim SourceRng As Range, DestCell As Range
Set ws1 = Worksheets("Sheet3")
Set ws2 = Worksheets("TransferToRegister")
Dim LastRow As Long
ws1.Activate
'ws2.Unprotect "1985"
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LastRow, 1), Cells(LastRow, 11)).Copy
ws2.Range("A2:J2").PasteSpecial xlPasteValues
Range("A1").Select
Application.CutCopyMode = False

'-----------------------the email code starts here---------------------------------------------------------

Dim olApp As outlook.Application 'You set the reference to use Outlook via Tools Menu and References
Dim olMail As MailItem
Set olApp = New outlook.Application
Set olMail = olApp.CreateItem(olMailItem)


Dim activereport As String
activereport = ActiveWorkbook.Name


Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String


'Save active workbook to temporary file


Set cWB = ActiveWorkbook
Sheets("TransferToRegister").Copy ' If you only wish to send the active sheet then uncomment this line

Set tWB = ActiveWorkbook
FileName = "Copy of " & activereport 'You can define the name
FilePath = Environ("TEMP")

On Error Resume Next
Kill FilePath & "\" & FileName


On Error GoTo 0
Application.DisplayAlerts = False
tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=52

Application.DisplayAlerts = True

'Sending email through outlook

ActiveSheet.Unprotect
With olMail
.To = Worksheets("TransferToRegister").Range("U1").Value
.Subject = "OFI For " & Worksheets("TransferToRegister").Range("M1").Value
.Body = "Please attach any pictures/reference with this OFI"
.Attachments.Add ActiveWorkbook.FullName
.Display


End With


Set olMail = Nothing
Set olApp = Nothing

'Delete the temporary file and restore screen updating

tWB.ChangeFileAccess Mode:=xlReadOnly
Kill tWB.FullName
tWB.Close SaveChanges:=False
cWB.Activate
Application.ScreenUpdating = True


Set oMail = Nothing
Set oApp = Nothing

'------the transfer to trevors workbook code starts here----------------------------------------------------------------------

Dim wbk1 As Workbook
Dim wbk4 As Workbook
Dim pasteSheet As Worksheet
Dim copySheet As Worksheet

Set wkb1 = ThisWorkbook
Set wkb4 = Workbooks.Open("T:\ROC-IT PROGAM\OFI Management\OFIBridgeSheet.xlsm", UpdateLinks:=0)
Application.AskToUpdateLinks = False
Set pasteSheet = wkb4.Sheets("Sheet1")
Set copySheet = wkb1.Sheets("TransferToRegister")
copySheet.Unprotect
copySheet.Range("A2:J2").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb4.Close True
Application.ScreenUpdating = True

Worksheets("Sheet2").Activate

ThisWorkbook.Save
ThisWorkbook.Close SaveChanges:=True
End Sub

 

[mrshl9898]

Does "T:\ROC-IT PROGAM\OFI Management\OFIBridgeSheet.xlsm" exist? Drive still mapped etc?

Or it could be the Kill of an open file

VBA Kill .FullName not working

Hi all, I have this code that emails a sheet and removes the file from the HDD But now for strange reason the file that was created will be removed from the HDD but there is still a copy of it open in excel. this is very strange to me as the only adjustment I made to the code was adding an...

www.mrexcel.com

Where's the error when you debug?

 

[dimsums]

The "T:\ROC-IT PROGAM\OFI Management\OFIBridgeSheet.xlsm exists,
I tried using it on other computer and it works.
Sorry for the silly question but what does the drive still mapped mean??
When i debug it, the error is on -

On Error Resume Next
Kill FilePath & "\" & FileName

This line above

 

[mrshl9898]

If I remove the On Error I get the 53 on Kill as well.

Unless there is a file there "Copy of" your original workbook, then the file doesn't exist. You're not creating that file in your code so not sure what you are trying to do there.

From what I can see, you have a book, you're copying a tab to create a new non specified named file, then killing a 3rd different file.

That being said, with the On Error it skips past it.

Mapping is the 'T:/", sometimes at work our mapping disappears so just thought i'd ask in case the 53 was on that line.

 

[dimsums]

My intention is to create a workbook with the specific worksheet and add it as an attachment on outlook. Thats why the copy of workbook.
What do you suggest i do?

 

[mrshl9898]

What are you trying to kill?

If the rest is working fine no need to change it.

 

[dimsums]

So the 'Copy of worksheet, once its attached to the outlook email , it should be removed automatically.
That was my intention.

 

[mrshl9898]

You're killing it correctly later. Try:

Sub TransferDataEmail3()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
'Dim ws3 As Worksheet
'Dim wkb1 As Workbook
'Dim SourceRng As Range, DestCell As Range
Set ws1 = Worksheets("Sheet3")
Set ws2 = Worksheets("TransferToRegister")
Dim LastRow As Long
ws1.Activate
'ws2.Unprotect "1985"
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LastRow, 1), Cells(LastRow, 11)).Copy
ws2.Range("A2:J2").PasteSpecial xlPasteValues
Range("A1").Select
Application.CutCopyMode = False

'-----------------------the email code starts here---------------------------------------------------------

Dim olApp As outlook.Application 'You set the reference to use Outlook via Tools Menu and References
Dim olMail As MailItem
Set olApp = New outlook.Application
Set olMail = olApp.CreateItem(olMailItem)


Dim activereport As String
activereport = ActiveWorkbook.Name


Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String


'Save active workbook to temporary file


Set cWB = ActiveWorkbook
Sheets("TransferToRegister").Copy ' If you only wish to send the active sheet then uncomment this line

Set tWB = ActiveWorkbook
FileName = "Copy of " & activereport 'You can define the name
FilePath = Environ("TEMP")

On Error GoTo 0
Application.DisplayAlerts = False
tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=52

Application.DisplayAlerts = True

'Sending email through outlook

ActiveSheet.Unprotect
With olMail
.To = Worksheets("TransferToRegister").Range("U1").Value
.Subject = "OFI For " & Worksheets("TransferToRegister").Range("M1").Value
.Body = "Please attach any pictures/reference with this OFI"
.Attachments.Add ActiveWorkbook.FullName
.Display


End With


Set olMail = Nothing
Set olApp = Nothing

'Delete the temporary file and restore screen updating

With tWB
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
.Close SaveChanges:=False
End With

cWB.Activate
Application.ScreenUpdating = True


Set oMail = Nothing
Set oApp = Nothing

'------the transfer to trevors workbook code starts here----------------------------------------------------------------------

Dim wbk1 As Workbook
Dim wbk4 As Workbook
Dim pasteSheet As Worksheet
Dim copySheet As Worksheet

Set wkb1 = ThisWorkbook
Set wkb4 = Workbooks.Open("T:\ROC-IT PROGAM\OFI Management\OFIBridgeSheet.xlsm", UpdateLinks:=0)
Application.AskToUpdateLinks = False
Set pasteSheet = wkb4.Sheets("Sheet1")
Set copySheet = wkb1.Sheets("TransferToRegister")
copySheet.Unprotect
copySheet.Range("A2:J2").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb4.Close True
Application.ScreenUpdating = True

Worksheets("Sheet2").Activate

ThisWorkbook.Save
ThisWorkbook.Close SaveChanges:=True
End Sub

 

[dimsums]

Hi mrshl9898

I tried this code out and works just the way it was however, before the new data is transferred from the last line of Sheet 3 to TransferToRegister, it asks me if the data should be replaced.
Would you know how to not bring up this dialogue box and do the transfer as is?

 

[mrshl9898]

To turn off dialogue boxes you can use similarly to Application.ScreenUpdating

Application.DisplayAlerts = False
Application.DisplayAlerts = True

To toggle them Off and back On

Sub TransferDataEmail3()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
'Dim ws3 As Worksheet
'Dim wkb1 As Workbook
'Dim SourceRng As Range, DestCell As Range
Dim LastRow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws1 = Worksheets("Sheet3")
Set ws2 = Worksheets("TransferToRegister")
ws1.Activate
'ws2.Unprotect "1985"
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LastRow, 1), Cells(LastRow, 11)).Copy
ws2.Range("A2:J2").PasteSpecial xlPasteValues
Range("A1").Select
Application.CutCopyMode = False

'-----------------------the email code starts here---------------------------------------------------------

Dim olApp As outlook.Application 'You set the reference to use Outlook via Tools Menu and References
Dim olMail As MailItem
Set olApp = New outlook.Application
Set olMail = olApp.CreateItem(olMailItem)


Dim activereport As String
activereport = ActiveWorkbook.Name


Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String


'Save active workbook to temporary file


Set cWB = ActiveWorkbook
Sheets("TransferToRegister").Copy ' If you only wish to send the active sheet then uncomment this line

Set tWB = ActiveWorkbook
FileName = "Copy of " & activereport 'You can define the name
FilePath = Environ("TEMP")

On Error GoTo 0
tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=52


'Sending email through outlook

ActiveSheet.Unprotect
With olMail
.To = Worksheets("TransferToRegister").Range("U1").Value
.Subject = "OFI For " & Worksheets("TransferToRegister").Range("M1").Value
.Body = "Please attach any pictures/reference with this OFI"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With


Set olMail = Nothing
Set olApp = Nothing

'Delete the temporary file and restore screen updating

With tWB
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
.Close SaveChanges:=False
End With

cWB.Activate


Set oMail = Nothing
Set oApp = Nothing

'------the transfer to trevors workbook code starts here----------------------------------------------------------------------

Dim wbk1 As Workbook
Dim wbk4 As Workbook
Dim pasteSheet As Worksheet
Dim copySheet As Worksheet

Set wkb1 = ThisWorkbook
Set wkb4 = Workbooks.Open("T:\ROC-IT PROGAM\OFI Management\OFIBridgeSheet.xlsm", UpdateLinks:=0)
Application.AskToUpdateLinks = False
Set pasteSheet = wkb4.Sheets("Sheet1")
Set copySheet = wkb1.Sheets("TransferToRegister")
copySheet.Unprotect
copySheet.Range("A2:J2").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb4.Close True


Worksheets("Sheet2").Activate

ThisWorkbook.Save
ThisWorkbook.Close SaveChanges:=True

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub