RunTime Error 75 Path/File Access Error when renaming text files

Question

I have folder with 12 files in it which are text files compressed as .gz files; I loop through the folder and unzip these text files and copy the result to another folder.  I then need to rename these files so I loop through this folder and  copy these to yet another folder with a new name.  Whilst renaming the files I get a RunTime error 75 ‘Path/File Access Error’. All of this is done in code in an Access database.  When the RunTime error shows if I then click debug and run it; it works fine.  If I check the properties of the files they show as normal, not ReadOnly etc.

Below is the code I use for renaming the text files which are renamed “Other_1”, “Other_2” and so on. The reason for this is these files are linked to tables in another Database so for each lot of new files I do not have to make changes to the tables each time as new files will come in each day.

i am using "7zFM.exe" to unzip the files which works fine.

Any advice to stop this RunTime error occurring will be very much appreciated

With best regards

Spike

Set fso = CreateObject("scripting.filesystemobject")

Set fsofolder = fso.GetFolder("C:\SP\AM_new \AA_TxtFiles_exGZ")

    s = 1

    For Each fsofile In fsofolder.Files

        'rename file to 'Other' & number, file now in 'AA_TxtFiles_exGZ' folder with original name with no .gz suffix as unzipped

        strNewName = "C:\SP\AM_new\AA_TxtFiles" & "Other_" & s & ".txt"

        strOldName = "C:\SP\AM_new\AA_TxtFiles_exGZ" & fsofile.Name

        'Rename file

        Name strOldName As strNewName

        s = s + 1

    Next

Answer 1

very weird; if i unzip the files manually and copy them to the folder then the routine to rename them works fine!  Which points to either my code for unzipping them is somehow preventing them from being renamed or ?? See below for the code i am using. 

I think i am going to have to give up and unzip them manually but it would be nice not to and also know why it does not work

regards

Spike

'strFileNameGZ (file to be unzipped)

'strUnZipFolder(folder to plug unzipped files into)

Sub UnZip_GZFile(strFileNameGZ As String, strUnZipFolder As String)

Dim Path7Z As String

Dim FileNameGZ As String

Dim ShellStr As String

'path to unzip app

Path7Z = "C:\Program Files\GZ_App"

'Check if this is the path where 7zFM is installed.

If Dir(Path7Z & "7zFM.exe") = "" Then

    MsgBox "Please find the app 7za.exe and try again" _

    & Chr(13) & "Either install it in 'C:\Program Files\GZ_App' or change the location in the code" _

    & Chr(13) & "As the macro cannot continue it will now close"

    bolFail = True

   Exit Sub

End If

'Unzip the zip file in to the folder for unzipped files

ShellStr = Path7Z & "7z.exe e" & " " & Chr(34) & strFileNameGZ & Chr(34) & " -o" & Chr(34) & strUnZipFolder & Chr(34)

'run the unzip app

Call Shell(ShellStr, vbHide)

End Sub

Answer 2

The reason I asked is because these links are likely locking the files. You will have to find a way around that.

Answer 3

thank you i have tried again but get the same RunTime error.  unfortunately there is a table in the database that holds the macro i am running that is linked to the database with the linked tables!

regards

Spike

Answer 4

Try again when no Access database is running with the linked tables.