Fix/Change directory thats causing permission denied during file kill

[Bnagel]

I'll try my best to explain what i'm currently doing and where the issue happens.

Step 1:

Open "PSU_Master.xlsm" workbook. *********code ran on open below*******

-This workbook opens and shows two buttons *********shapes assigned with different macros********
-Also once this workbook open, it loads another workbook "Library.xlsm" hidden in the background, saves a copy to user\documents as "Library_Temp.xlsm".
-"Library_Temp.xlsm" is required for (Button 1) and everything works as needed.

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Worksheets("Welcome").Visible = True
Worksheets("Welcome").Activate
Worksheets("Project Setup Sheet").Visible = False
Worksheets("Drop Downs").Visible = False
Range("data_pcb_select").Value = "Select"
Range("data_panel").Value = ""
Range("data_module").Value = ""
Range("data_firmware").Value = ""
Range("data_reset_profile").Value = ""
Range("data_required_profile").Value = ""
Range("data_created").Value = "Select"
Range("data_eco_number").Value = ""
Range("data_add").Value = "No"
Range("data_refnum").Value = ""
Worksheets("Project Setup Sheet").Shapes("message_psu_warning").Visible = False

Dim my_wb As Workbook
Dim file_path As String
Dim filename7 As String
filename7 = "Library_Temp"
Application.EnableEvents = False
Application.DisplayAlerts = False
file_path = "S:\Production\PCB\Master Project Setup Sheet\Backup\Admin\NOT RELEASED\Library.xlsm" ******* For reference in Step 3, this is the network share location its changing to*********
Set my_wb = Workbooks.Open(FileName:=file_path, ReadOnly:=True)
ActiveWorkbook.SaveAs filename7, FileFormat:=52, CreateBackup:=False
ActiveWindow.Visible = False
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub



Step 2:
Click on (Button 2) to run Macro "Database_Temp()" *******macro code below*********
-This macro is opening "Database.xlsm" and saving a temp copy to user\documents as "Database_Temp.xlsm".
-After the open and save I want to close the original "PSU_Master.xlsm" with "ThisWorkbook.Close", and Kill "Library_Temp.xlsm"
-Everything in this macro works as needed, no debug fails

Sub Database_Temp()
Workbooks.Open "S:\Production\PCB\Master Project Setup Sheet\Database.xlsm"
Dim filename3 As String
filename3 = "Database_Temp"
Application.DisplayAlerts = False
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines 1, "Private Sub Workbook_BeforeClose(Cancel As Boolean)"
.InsertLines 2, "ThisWorkbook.Saved = True"
.InsertLines 3, "Kill(""Database_Temp.xlsm"")"
.InsertLines 4, "End Sub"
End With
ActiveWorkbook.SaveAs filename3, FileFormat:=52, CreateBackup:=False
ThisWorkbook.Close
Application.DisplayAlerts = True
End Sub

Step 3:
Part of the above macro, this is what runs during "ThisWorkbook.Close"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Saved = True ******this works******
ThisWorkbook.Activate ******this works****** I dont believe this is required but was something I was trying to fix this problem
Dim fp As String ******this works******
fp = ActiveWorkbook.FullName ******this did work****** (debug did shows the user\document directory where the Temp files are but now its showing the network share)******

******* this is where things change and start the fail, FP after this line shows the "Database.xlsm" directory which is a network share, therefor wrong directory for any temp file.*********

fp = Left(fp, InStrRev(fp, "\"))
fp = fp & "Library_Temp.xlsm"
Workbooks("Library_Temp.xlsm").Close SaveChanges = False
Kill fp ******this fails for permission denied, due to FP now referring to the incorrect directory even though about i define fp as ActiveWorkbook.Fullname ********
End Sub


Where i need help is in Step 3, Kill FP ("Library_Temp") from user\documents.
Multipule differnt users use this workbook thats why the temp files are stored in user\documents and i cant hard define the path.

I apologies if my code is sloppy, i'm not proficient in VBA but google and excel forums make all i do possible.

Thanks in advance for any help!!!

[Bnagel]

Bump,

This is the last piece I need in order to complete this project.

I've tried activating each workbook and still giving me the wrong directory.

Lastest:

Application.DisplayAlerts = False
Application.EnableEevents = False
ThisWorkbook.Saved = False
Workbook("Library_Temp.xlsm").Activate
Workbooks("Library_Temp.xlsm").Close SaveChanges:= False
Kill (ThisWorkbook.Path & "\" & ThisWorkbook.Name)

still showing wrong directory