Do I put it in a module and call it in the Workbook_Open sub?

Yes, that is an option.

Also what happens if there are other references missing when my customers
open the report, how can I get around this

Exactly the same, but of course with different GUID's.
I posted some code only about 2 weeks ago that will show you how to get
these GUID's.

Is there something I can do so that if any reference are missing it ignores
them?

Normally it can't be ignored.

AddIns("Analysis ToolPak").Installed = True

Yes, I think that would do it.

RBS