Path/File access error (Error 75) using Name Statement

[blayne]

I wrote the following code to rename a folder based on information entered
into an excel file contained in the folder

' ROUTINE TO MODIFY EXISTING INFORMATION
If Workbooks("FILE MANAGEMENT
SYSTEM.xls").Sheets("ONE").Range("CHANGE_EXISTING").Value = "YES" Then
Set InformationRegister =
Application.Workbooks("INFORMATION.xls").Sheets("Information register")
Call WritetoInfoRegister
Application.Run ".xls'!TenderInfoReport"
Call OpenRegister ' open register file
Call RewriteToRegister ' write new information to file
Call CLOSERegister ' close register file
Application.Workbooks("INFORMATION.xls").Save
Reset

Call RenameFolder

End If
End If
End If

The RenameFolder reads

Sub RenameFolder()
Dim OldName As String
Dim NewName As String
Dim dummyvar

Call CalcPath
' define the folder names
OldName = path & " " & Workbooks("FILE MANAGEMENT
SYSTEM.xls").Sheets("START"). _
Range("ProjShortName") & " " & Workbooks("FILE MANAGEMENT
SYSTEM.xls").Sheets("START"). _
Range("status2")
NewName = path & " " & Workbooks("FILE MANAGEMENT
SYSTEM.xls").Sheets("START"). _
Range("ProjShortName") & " " & Workbooks("FILE MANAGEMENT
SYSTEM.xls").Sheets("START"). _
Range("bidstatus")
dummyvar = "C:\File Setup"
Name OldName As NewName
End Sub

This is a very small portion but is the revelant section of the the code
I have tried the workaround found for excel 2000 to reset the pointer
and it has not worked

is there anyone who may have any ideas

 

[JNW]

This can occur if you are renaming the folder that contains the open
workbook. This error can also occur while attempting to save a file that
would replace an existing read-only file. Without knowing where things are
located it's hard to say.

Where does the code stop?

 

[blayne]

The file path reads something like this;
C:\Directory\SubDirectory\JOB Folder\JOb File
What I am trying to do is
Write code to:
Open the JOb File
Change some of the information in the JOb File
through VBA User forms
Close the JOb File
and Change the Name of the JOB Folder
after the JOb File is Closed

What happens when the code executes is:
The Code opens the file
the User form modifies the information
the Code closes the file
but when the code reaches the Name statement I get
Path/File access error (Error 75)
At this point I am unable to change the Folder Name

I have tried to Manualy change the JOb folder name through Windows and I get
an
error message

I cannot change the file name untill I close Excel completely
The name statement code will work when I reopen Excel
and I can manualy change the JOb folder name

I have tried several "work arounds"
I tried to copy the Job file to a temporary folder and rename the JOB Folder
before saving the modified file to the renamed file
this did not work

I tried to redirect the Pointer using a dummy variable

I have reordered my code
so far nothing has worked

It is as if Windows "Thinks" the file is still open
evan though the Close method has executed
I have to close the Excel appllication to change this

is this a hang up in the Windows / Excel registery/buffers or "Whatever"
this is getting into an area I do not know alot about.

I am looking for some code to change the method I am using or the change
what file Windows thinks Excel has open

Thank you for your insights and help

 

[blayne]

JNW:
I did not completly answer the last part of your reply
the code ends after renameing the folder with unloading the user form and
ending the maco(s)

 

[JNW]

Are you creating a new folder? Or are you moving to an existing one? The
following website has all one might need to know to manupulate file location
and folders.

http://www.exceltip.com/st/Basic_file_and_folder_examples_using_VBA_in_Microsoft_Excel/443.html

With what you have included in the post, I can't seem to find where it is
causing an error. What line your code stopping on? I've included some code
that I've done to change a files name and move that file from one folder to
another (but not create a new folder), in hopes that it will help. Note that
the file that I am manipulating is closed at the beginning of the sub.


Sub NewOwnerFinish_Move()
Dim OldFileName As String
Dim NewFileName As String
Dim OldFilePath As String
Dim NewFilePath As String

With Sheets("New Owner Reservations")
OldFileName = .Range("D15") _
& ("-") & .Range("F15") _
& ("-") & .Range("H15") _
& ("_") & .Range("D13") _
& ("_") & .Range("D11") _
& ".xls"

NewFileName = .Range("D15") _
& ("-") & .Range("F15") _
& ("-") & .Range("H15") _
& ("_") & .Range("D13") _
& ("_") & "Booked" _
& ("_") & .Range("Q41") _
& ".xls"
End With

'OldFilePath = "C:\OldFolder\Filename.xls" ' original file location
OldFilePath = _
"\\trimain2\public\Agent Forms\" _
& "Developer Rep Forms\New Owner Reservations\" _
& "Requests\" & OldFileName

'NewFilePath = "C:\NewFolder\Filename.xls"
' new file location with folder changed from "Requests" to
"Finished"
NewFilePath = _
"\\trimain2\public\Agent Forms\" _
& "Developer Rep Forms\New Owner Reservations\" _
& "Finished\" & NewFileName

Name OldFilePath As NewFilePath ' move the file
End Sub

 

[blayne]

Your last answer may help.
I have continued to check my code in detail one section at a time
the source of my problem may be in the use of the GetOpenFilename Method
to open the file to be written to;
I then close the file and use the Name Statement to rename the folder the
file written to is in
My code stops at the name statement
I am not creating a new folder or moving the folder Just renaming it.

 

[blayne]

I am sure the source of my problem is the use of the Getfileopen method
using the links in the previous reply I tried the following code

open any Excel file using the code below

Sub errorcheck()
FileToOpen = Application _
.GetOpenFilename("Text Files (*.xls), *.xls")
If FileToOpen <> False Then
MsgBox "Open " & FileToOpen
End If
MsgBox "Active station and folder name: " & CurDir
If FileAlreadyOpen(CurDir) = True Then
MsgBox "file in use "

End If
End Sub

Function FileAlreadyOpen(FullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:\FolderName\FileName.xls") Then...
Dim f As Integer
f = FreeFile
On Error Resume Next
Open FullFileName For Binary Access Read Write Lock Read Write As #f
Close #f
' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
FileAlreadyOpen = True
Err.Clear
'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
Else
FileAlreadyOpen = False
End If
On Error GoTo 0
End Function

Now what I need to do is find a procedure to change the file in use status
using ChDir dosen't change this.
any Ideas?

 

[blayne]

I would like to thank you for your help
It was the use of the GetOpenFilename Method which was messing me up
Using any of the file system dialog boxes will cause the "pointer" to stay
on the file or directory "Clicked on" it will not move untill another dialog
box moves it. ChDir etc will not redirect it eather I hope this may help
anyone using the graphic dialogboxes to open files and then trying to rename
the folder the file was in afterwards
Blayne