Path/File access error 75

[sharky12345]

I am trying to move and rename the activeworkbook to another folder but I keep getting an Error 75 Path/File access error.

I'm using this:

Dim sFolder As String
Dim dFolder As String

sFolder = ThisWorkbook.Path & "\"
dFolder = ThisWorkbook.Path & "\Update Folder\"

Name sFolder & ThisWorkbook.Name As dFolder & ThisWorkbook.Name

I have full read and write permissions on the drive in question, (which happens to be on a network), and the line which throws the error is:

Name sFolder & ThisWorkbook.Name As dFolder & ThisWorkbook.Name

I have tried saving a copy of the current workbook by using this:

Dim sFolder As String
Dim dFolder As String

sFolder = ThisWorkbook.Path & "\"
dFolder = ThisWorkbook.Path & "\Update Folder\"

ActiveWorkbook.SaveAs dFolder & "Test.xlsm"

and it works fine, the problem I then have is I get a permission denied error when I try to delete the original file, hence I thought the 'Name' procedure would be more efficient.

Is anyone able to offer me advice or an explanation as to why this won't work?

 

[gallen]

Include all your code. Where do you try and delete the original file? Is it closed before you try and delete?

 

[sharky12345]

What I've posted is all of my code with the exception of adding a Kill command to this, (if I try the 2nd piece of code where I try to use the Saveas):

Dim sFolder As String
Dim dFolder As String
Dim OriginalFile As String

sFolder = ThisWorkbook.Path & "\"
dFolder = ThisWorkbook.Path & "\Update Folder\"
OriginalFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name

ActiveWorkbook.SaveAs dFolder & "Test.xlsm"

Kill OriginalFile

Excuse my ignorance, but I thought the 'SaveAs' command effectively does close the original file, therefore leaving it ready to be deleted?

 

[gallen]

Hello,

It gets awkward getting names and extensions correct. This code works. Let me know if there's any aspect that is unclear. I've commented where possible

Sub SaveAsDelete()

    Dim sFullDestination As String
    Dim sNewName As String
    
    Dim sFileToKill As String
    
    'destination of new file
    sFullDestination = ThisWorkbook.Path & "\Update Folder\"
    
    'Name of new file
    
    sNewName = NameWithoutExtension(ThisWorkbook.Name) & "_TEST"
    
    'Remember this file's path and name
    sFileToKill = ThisWorkbook.FullName

    ThisWorkbook.SaveAs Filename:=sFullDestination & sNewName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Kill sFileToKill
End Sub

Private Function NameWithoutExtension(sName As String) As String
    Dim iPos As Integer
    
    'determine the position of the character immediately before the last period (name extension)
    iPos = InStrRev(sName, ".") - 1
    
    'Knowing the position of the file extension you can parse it out
    NameWithoutExtension = Left(sName, Len(sName) - (Len(sName) - iPos))
    
    
End Function

 

[sharky12345]

Thanks gallen - I'll give that a try today and come back to you!

 

[sharky12345]

gallen - I get 'sub/function not defined' on this line:

sNewName = NameWithoutExtension(ThisWorkbook.Name) & "_TEST"

with this line highlighted:

NameWithoutExtension

I've copied your code exactly with no changes.

 

[sharky12345]

Ignore the last gallen - I'd put the function into a separate module then realised it was private so moved it to where your suggested routine is and it works fine.

Thank you!!

 

[gallen]

Good news. Got there eventually.

 

[sharky12345]

Ok, what I thought was a problem solved has now thrown up some other errors.

I have used the following code but it randomly throws up an Error 70 Permission Denied in about 90% of cases:

'destination of new file
sFullDestination = ThisWorkbook.Path & "\"

'Name of new file
sNewName = NameWithoutExtension(ThisWorkbook.Name) & "_TEMP"

'Remember this file's path and name
sFileToKill = ThisWorkbook.FullName

ThisWorkbook.SaveAs filename:=sFullDestination & sNewName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Kill sFileToKill

and it always fails on this line:

Kill sFileToKill

I don't know if this is related, but if I put a break in the code before it gets to the Kill part and step through it, then it usually works without an error. So my thoughts are is it that somehow the original workbook is still being closed when it tries to kill? I have tried putting a 3-4 second pause after the .SaveAs part but this makes no difference.

I'm baffled....

 

[gallen]

I'm baffled too. Tried it several times on my PC and it works fine. The location I'm saving to is a network location so that can't be an issue.


I've even called the sub from a separate sub where the code needs to return to after "Saving As" and still no issue.