0

This code is opens a .XLSX file by use of the "xlExtractData" option, and then saves it with the same file name and path.

Application.DisplayAlerts = False
Set wb = Workbooks.Open(filename:=myPath & myFile, UpdateLinks:=0, CorruptLoad:=xlExtractData)

' Do not show the "This file already exists prompt"
Application.DisplayAlerts = False

' Save the repaired file
wb.SaveAs filename:=myPath & myFile, AccessMode:=xlExclusive, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges

' Close the saved file
wb.Close

It is working for most of the files (there are hundreds of files) but it randomly does not work on some files. In those cases, it shows an error that says "Cannot access file".

Why would it work for some files and not work for others, even so randomly?

nvkrj
  • 1,002
  • 1
  • 7
  • 17
  • What are myPath and myFile? – Vityata Nov 03 '16 at 15:54
  • myPath is the directory path in which your Excel file is present like "C:\Users\Guest\Documents\" and myFile is the name of the excel file including its extension, like "document.xlsx". Combing them, we get the complete path to the file. – nvkrj Nov 03 '16 at 16:25
  • Is the file on a shared drive and could it be in use? – Niclas Nov 03 '16 at 16:30
  • It could not be in use because it's a testing folder I've created that no one else works on. (It is on a remote desktop). Further, the macro has a function that checks whether files are in use and stops if even one file is in use. – nvkrj Nov 03 '16 at 16:32
  • @Nvj, are you sure that you always get the "\" in the end of the myPath? – Vityata Nov 03 '16 at 16:32
  • Yes because the macro has been working properly with the code until this change. – nvkrj Nov 03 '16 at 16:32
  • Have you checked this here? http://stackoverflow.com/questions/25777899/run-time-error-upon-opening-excel-file – Vityata Nov 03 '16 at 16:34
  • Or this http://stackoverflow.com/questions/24827292/run-time-error-cannot-access-file :-) Seems like a lot of people with this error they use the `FileSystemObject` to check if the file exist or not. – Niclas Nov 03 '16 at 16:39
  • @Vityata: It's not a problem with "Opening" the excel file. It is a problem with "saving" it. – nvkrj Nov 03 '16 at 16:39
  • @Niclas: Have viewed that already :(. If you see the code I've posted, I've already implemented the Application.DisplayAlerts = False thing. And I don't want any user interaction in the process - it should be automatic. – nvkrj Nov 03 '16 at 16:43
  • I am not talking about the `DisplayAlerts`. You are opening a file and saving it again. However, when you are "opening" the file, it might not exist and therefore, you will get the error "cannot access file", because you actually did not open it. So have you tried to implement the `fso.FileExists` to check if it actually exists or not. – Niclas Nov 03 '16 at 17:00
  • @Niclas: The file does exist. I have checked and rechecked it. And, when the macro shows error and I open Windows Explorer to that file.. it is there along with a temp copy opened by the VBA script. Though I think FSO may help alleviate the problem. Ill give it a try and let you know tomorrow.. thanks for the help :) – nvkrj Nov 03 '16 at 17:07
  • Fair enough. Read somewhere that a guy with the problem solved it by using `ChDir()`before the FileSaveAs(). Not sure if it is allowed to link to it, but take a look, it might help you http://www.excelforum.com/showthread.php?t=686067&p=2127553&viewfull=1#post2127553 – Niclas Nov 03 '16 at 17:11
  • @Niclas: I reran the whole macro (it is a long process that takes around 30 minutes) again and this time received no error (I just had a hunch that it would give no errors). However, I don't want this to randomly give an error.. so I will try both of your above solutions. Anyway, thanks a lot for the help :) – nvkrj Nov 03 '16 at 18:13

0 Answers0