Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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 -- Fuzzy logix "JNW" wrote: 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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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) -- Fuzzy logix "JNW" wrote: 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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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_fil...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" wrote: 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 -- Fuzzy logix "JNW" wrote: 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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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. -- Fuzzy logix "JNW" wrote: 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_fil...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" wrote: 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 -- Fuzzy logix "JNW" wrote: 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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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? -- Fuzzy logix "JNW" wrote: 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_fil...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" wrote: 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 -- Fuzzy logix "JNW" wrote: 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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path/File access error (Error 75) using Name Statement
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 -- Fuzzy logix "JNW" wrote: 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_fil...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" wrote: 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 -- Fuzzy logix "JNW" wrote: 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" wrote: 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("I nformation 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 -- Fuzzy logix -- Fuzzy logix |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Path/File access error: '.\VB??.tmp' | Excel Programming | |||
Path/File access error | Excel Discussion (Misc queries) | |||
Path/File access error: '\VBx.tmp' | Setting up and Configuration of Excel | |||
Path/File access error (Error 75) after using Name Statement | Excel Programming | |||
Path/File Access Error | Excel Programming |