method open of object workbooks failed

[dpaton05]

While working on a spreadsheet at work, I was getting the error "method open of object workbooks failed", but when I got home, the same file was not giving me any errors. Any ideas why?

 

[dpaton05]

I'm not sure Norie but I have read that different versions of office can cause corruption and I was working on it at work, where they have office 2016 Professional. I was also working on it at home where I have office 365. Therefore, I suspect that the different versions in office caused it but i'm not sure as I have worked on spreadsheets at home and at work before and they haven't become corrupted.

 

[DanteAmor]

Hi, you can upload your file with the macro and the file that you can not open to review them.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Michael M]

Did you try the methods mentioned in my esarlier post on a clean workbook

If you step through the code 1 line at a time, does the file get corrupted ??
If not, you may have to put a wait line in to slow the code down !!!

Generally, cross version work will not be an issue, unless you are going backward and your code has lines in it that are only in the later versions !!
But I haven't seen anything in your code, apart from needing a clean out, that would cause these problems.

 

[dpaton05]

Here is the latest version of my quoting tool. I have tried stepping through the code but I couldn’t see anything from that.

Depending on the organisation that is under the heading “Requesting Organisation” on the sheet “Costing_tool” will partly determine what document that row of the quote is copied to. The rows are also copied to financial year documents. If “Ang Wes” is displayed under the requesting organisation, the rows are copied to Internal Work Allocation Sheets depending on the financial year. Therefore, if a row has the date within the financial year 2018-2019 and if it has “Ang Wes” under the requesting organisation on the “costing_tool” sheet, it will be copied to the “2018-2019 Internal Work Allocation Sheet”.

If there is something other than “Ang Wes” displayed under the requesting organisation and the date of the row is within the 2018-2019 financial year, the name of the document it will be copied to is “2018 2019 NPSS Work Allocation Sheet”. This is what should happen, or what I want to happen.

I should be able to enter data for each row on the NPSS_quote_sheet sheet, which is within the quoting tool, in columns A:G. This will then calculate a price ex GST. When each line of the quote has been entered, I press the “Send to costing tool” button. This part works fine, in the calculation and copying to the “Costing_tool”.

I run into problems when all of the quote rows have been entered in the costing_tool and I try to copy the rows to the relevant allocation sheets by pressing one of the copy to relevant sheet buttons on the costing_tool. It should work as everyone tells me the code looks ok but it keeps telling me “Method Open of object workbooks failed”. All workbooks are stored in the same folder.

In the files I have attached for every row, none have a requesting organisation of “Ang Wes” and a date in July 2018, therefore they should all go to the July sheet within the “2018 – 2019 NPSS Work Allocation Sheet”. It used to work and I have no idea what has happened to it. Can someone help me please?

Here is a link to the quoting tool
https://www.dropbox.com/s/fjljdrd0afd0wgs/quoting tool 11.7 WCI.xlsm?dl=0

Here is the 2018-2019 NPSS Work Allocation Sheet, where the quote rows are being copied to
https://www.dropbox.com/s/zi724xqtu1xdi70/2018 - 2019 NPSS Work Allocation Sheet.xlsm?dl=0

Thanks guys,
Dave

 

[DanteAmor]

When trying to open the file, excel sent this message:

"Excel found unreadable content in"

I opened the file from excel, I saved it again as an xlsm file and now it works.
I return the file for you to try.

https://www.dropbox.com/s/axlrxtabt7mqhgk/2018 - 2019 NPSS Work Allocation Sheet.xlsm?dl=0

 

[dpaton05]

Thanks for that Dante, I will have to try it tomorrow at work as that was where I was having all the problems. I don't understand, it was opening fine for me without any errors. Would it of made any difference that I use office 365 at home and office 2016 professional plus at work? I am just trying to understand what may have been the problem so I can avoid doing it again. I also don't understand, I was constantly saving the file in excel as something else so why wouldn't I have fixed it? How did you fix it?

 

[dpaton05]

It wasn't the allocation sheets that I was having the problem with, as you have uploaded in your previous message is one of the allocation sheets I use. I was having problems with the quoting tool. The quoting tool worked fine to copy rows of a quote from the "NPSS_quote_sheet" sheet to the "Costing_tool" sheet but when I try to copy each row of the quote from the "Costing_tool" sheet to the relevant allocation workbook, depending on the date, I would get the error Method open of object workbooks failed. I hope that makes sense.

Thanks Dante and Michael,
Dave

 

[DanteAmor]

Comment what steps I must take to get to that error.

 

[dpaton05]

All I did Dante is I open the quoting tool file, go to the Costing_tool sheet then press the "Copy to relevant sheet and keep contents" button and it gives me the error. I also make sure both files are in the same folder.

 

[DanteAmor]

All I did Dante is I open the quoting tool file, go to the Costing_tool sheet then press the "Copy to relevant sheet and keep contents" button and it gives me the error. I also make sure both files are in the same folder.

I did the test as you say and I have no problems.


The problem is your file, did you try the file that I sent you?


Discard your file.
Create a new file, with new sheets and fill in the information. Save the file with the desired name and run the macro again.