CODEWITHSUNDEEP
excelvba
Fritz Larco
Fritz Larco

Reputation: 121

Run-time Error '1004' - Method 'Open' of object 'Workbooks' failed

OK, so far I've uninstalled & re installed Office-2010 3 to 4 times, done hours of research for 3 days with no success. I started getting this error either from Vbscript or Excel VBA, performing the same command that has worked for months. I am not sure what I may have changed, I don't recall changing anything that could cause it but I nailed it down to the ReadOnly:=True parameter. I think something may be wrong with the registry... but re-installing should fix it right? It didn't...

Anyways, very simple command that I recorded on Excel 2010. This command WORKS FINE and the file opens:

Sub Macro1()
     Workbooks.Open Filename:="C:\temp\file_9928_131101.xlsx"
End Sub

But when I add the ReadOnly:=True parameter, it does NOT WORK:

Sub Macro1()
     Workbooks.Open Filename:="C:\temp\file_9928_131101.xlsx", ReadOnly:=True
End Sub

This is the returned error Run-time error '1004' Method 'Open' of object 'Workbooks' failed:

When I click Debug, the error is at the only line of code.

Upvotes: 4

Views: 75508

Answers (9)

Sai Sudheer Kumar Vura
Sai Sudheer Kumar Vura

Reputation: 1

Try to change the name of the file and save it once then your problem will be rectify very easy.

ex:- if the file name was Sales Report 1-12-2020 then change it to Sales_report_1-12-2020 or Sales_Report_1

Upvotes: 0

dotsent12
dotsent12

Reputation: 137

I was too, going crazy with Workbook.Open function, getting the weird Open fail error for no obvious reason. My code also did some some copy-pasting after Open function, which seemed to trigger it.

Eventually I found out that turning ScreenUpdating OFF seemed to cause this - keeping ScreenUpdating ON, made things click for me (maybe someone finds this helpful in the future). I'm one of those who tends to switch ScreenUpdating off for all code whenever possible.

Application.ScreenUpdating = False

Upvotes: 0

Sancarn
Sancarn

Reputation: 2824

I realise this is late but if you want to open & repair a corrupted workbook automatically use:

Set oWB = Workbooks.Open(Filename:="C:\my\file\path.xlsx", CorruptLoad:=XlCorruptLoad.xlRepairFile)

Upvotes: 1

Atanas Atanasov
Atanas Atanasov

Reputation: 369

I had a rogue excel process that was running in the background. When I killed it from the task manager the code worked. I hope this helps.

Upvotes: 0

user5144312
user5144312

Reputation:

I have had the same issue with an Access file stored in a local OneDrive folder, referencing an Excel file stored in the same local OneDrive folder. The solution was to move all files into a "static" (i.e., not synchronized, not OneDrive) folder.

Thought that this specific case/application might help someone.

Upvotes: 4

Russ
Russ

Reputation: 1

I know I'm answering late on this, but I resolved a similar issue (same error but running excel from a .Net app) by making sure VBA was installed correctly on the target machine.

Control Panel->Programs and Features->Uninstall a Program... find your Office install, Right-click and select "change" ->Add Remove Features->Office Shared Features->Visual Basic for Applications->Run From My Computer

This did it for me.

Upvotes: -2

Abhay
Abhay

Reputation: 1

If you are downloading the file from some external source (eg- email), just open the file directly from the email and then save the file. Try opening the file in macro ...hope it works...it worked for me....:)

Upvotes: 0

AndASM
AndASM

Reputation: 10308

Putting an answer here for others like myself who have this issue and the normal solutions don't work.

Another potential cause of this is corrupted temporary files. I think it may only apply if the file you are trying to open is on a network drive or other remote host.

Anyway, try wiping your temp folder (as in the one you get to if you type %temp% into Windows Explorer) then restarting the computer.

Upvotes: 5

user3412914
user3412914

Reputation: 61

The file is corrupted. Resave it with another name and change the name in the function. Try that it works and after that rename the file as you want to call it.

It worked for me and I had a corrupted file. The read only shouldn't be a problem.

Upvotes: 6

Related Questions