Opening "corrupt" Excel files

[CedarTree]

Hello. Have coding in Access that opens up several Excel files, one at a time. Most files work fine. But some files cause Excel to crash. Turns out that they have v20.00 in the filename(?). Anyway, when I open the files manually, they also crash, unless I disable macros in Excel. So I disabled them and I add this line in Access VBA: objExcel.EnableEvents = False. But still, when I run my Access VBA, it gives me an error (-2147023170). PLEASE HELP! Thanks!!!

 

[Gasman]

So rename the files. That would be the first thing I would try?

 

[CedarTree]

Ha - was just trying that. No luck.

 

[CedarTree]

Is there a way to open Excel in safe mode from within Access?

 

[The_Doc_Man]

That error (-2147023170) is 0x800706BE, which suggests file corruption of some indeterminate type, perhaps caused by a failed update cycle. It is a catch-all error for failed updates. This error code pops up for RPC configuration errors, Windows Update errors, and other Windows config files.

The best suggestion I could find online is to manually attempt to run a Windows update cycle. If you really DO have an RPC (remote procedure call) error, YOU can't fix it. You can only hope to have Windows fix it for you.

Some specific things to try:

1. From the CMD prompt run as Administrator, you can type the SFC/scannow command and hit <ENTER>. Let that run. If you cannot run that command, try DISM/Online/Cleanup-Image/RestoreHealth and hit <ENTER>

SFC is "system file checker". DISM is "deployment image servicing and management" - one or both will be available from CMD prompt when run as admin. They are HIGHLY privileged and not to be "diddled around."

2. For WIN11, you can also get into Settings >> System >> Troubleshoot >> Other Troubleshooters >> Windows Update to see if you can get Windows to fix a failed update.

3. Finally, you can look up "Windows Error 0x800706BE" with a general web search and see all of the things this could be.

 

[isladogs]

Is there a way to open Excel in safe mode from within Access?

Sub OpenExcelSafeMode()
    Dim excelPath As String
    'specify Excel path (modify as necessary), /s is Safe mode
    excelPath = "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE /s"
      
    'Now use Shell to open Excel in Safe Mode
    Shell excelPath, vbNormalFocus
End Sub

 

[arnelgp]

if your excel files are corrupted first repair them.
try fixing by opening Excel first, then open 1 by 1 by "Open and Repair".
you may also try online recovery tools.

 

[gemma-the-husky]

Hello. Have coding in Access that opens up several Excel files, one at a time. Most files work fine. But some files cause Excel to crash. Turns out that they have v20.00 in the filename(?). Anyway, when I open the files manually, they also crash, unless I disable macros in Excel. So I disabled them and I add this line in Access VBA: objExcel.EnableEvents = False. But still, when I run my Access VBA, it gives me an error (-2147023170). PLEASE HELP! Thanks!!!

Maybe there's something funny in the column headers for your version 20. Underscores, or something like that.

Maybe it's the full stop/period in the file name causing confusion. In fact, can you have a period in a file name?

 

[CedarTree]

Sub OpenExcelSafeMode()
    Dim excelPath As String
    'specify Excel path (modify as necessary), /s is Safe mode
    excelPath = "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE /s"
     
    'Now use Shell to open Excel in Safe Mode
    Shell excelPath, vbNormalFocus
End Sub

Thanks - I guess I would need coding for Access to then grab that instance of Excel and set it equal to an object?

 

[CedarTree]

That error (-2147023170) is 0x800706BE, which suggests file corruption of some indeterminate type, perhaps caused by a failed update cycle. It is a catch-all error for failed updates. This error code pops up for RPC configuration errors, Windows Update errors, and other Windows config files.

The best suggestion I could find online is to manually attempt to run a Windows update cycle. If you really DO have an RPC (remote procedure call) error, YOU can't fix it. You can only hope to have Windows fix it for you.

Some specific things to try:

1. From the CMD prompt run as Administrator, you can type the SFC/scannow command and hit <ENTER>. Let that run. If you cannot run that command, try DISM/Online/Cleanup-Image/RestoreHealth and hit <ENTER>

SFC is "system file checker". DISM is "deployment image servicing and management" - one or both will be available from CMD prompt when run as admin. They are HIGHLY privileged and not to be "diddled around."

2. For WIN11, you can also get into Settings >> System >> Troubleshoot >> Other Troubleshooters >> Windows Update to see if you can get Windows to fix a failed update.

3. Finally, you can look up "Windows Error 0x800706BE" with a general web search and see all of the things this could be.

Thanks - I even tried this on another computer with no luck.

 

[theDBguy]

Thanks - I guess I would need coding for Access to then grab that instance of Excel and set it equal to an object?

Maybe try the a GetObject() method.

 

[The_Doc_Man]

Thanks - I even tried this on another computer with no luck.

Whoops? More than one computer showing the same error? If this test was to copy the file to another computer and try the operation, and you got the same error on two different computer, this isn't an update error unless the 2nd computer was an exact re-image copy of the 1st one. This is a file problem and you will have to look up methods of repairing a corrupted Workbook.

 

[gemma-the-husky]

I had some excel files that Access didn't like. They had been produced by SAP, I think. Do you know what system produces the files causing your problems. What file types are they? Can you get csvs instead?

 

[isladogs]

Thanks - I guess I would need coding for Access to then grab that instance of Excel and set it equal to an object?

Sorry but I don't understand your comment.
If you want to open a specific file in safe mode from Access then change it to:

Sub OpenExcelWorkkbookSafeMode()
    Dim excelPath As String
    'specify Excel and file paths (modify as necessary), /s is Safe mode,
    excelPath = "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE /s FullPathToExeclFile.xlsx"
   
    'Now use Shell to open Excel workbook in Safe Mode
    Shell excelPath, vbNormalFocus
End Sub

 

[Isaac]

Hello. Have coding in Access that opens up several Excel files, one at a time. Most files work fine. But some files cause Excel to crash. Turns out that they have v20.00 in the filename(?). Anyway, when I open the files manually, they also crash, unless I disable macros in Excel. So I disabled them and I add this line in Access VBA: objExcel.EnableEvents = False. But still, when I run my Access VBA, it gives me an error (-2147023170). PLEASE HELP! Thanks!!!

Clearly there is some code in the Excel files that you'll need to fully troubleshoot and debug

How could we possibly make educated guesses as to what that code is and what it's doing!??