I developed a word document with vba that uses an excel file. On my pc, the .docm resides in C:\Users\....\Documents\Custom Office Templates with the Developer>Tools>References>Microsoft Excel 16.0 Object Library Checked.


(I also set the Custom Office Templates folder as a trusted site.)

When I copy the .docm to my wife's pc's C:\Users\...\Documents\Custom Office Templates the Microsoft Excel 16.0 Object Library is not checked.

I enabled the Microsoft Excel 16.0 Object library through Developer>Tools>References> on my wife's copy and Saved. Multiple times. Multiple iterations.
But every time I open the .docm on my wife's machine, the Excel 16.0 Object Library is unchecked.

And the security warning continues to appear on each open.

What I want, of course, is that the Object Library to remain checked. I'd also prefer that the security message disappears.

You could change the code to use late binding to Excel, then the library entry doesn't need to be checked.

Declare the Excel app as an Object and use the numeric values of any Excel specific commands. There is an example of Excel being used this way on my web site at Extract Data from E-Mail. The code there is for Outlook, but the principles for Word are identical.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Thank you for your quick reply and helpful link @gmayor. Before my response, may I point to a typo in Extract Data from E-Mail A paragraph (fifth one, I believe) begins with a double-"T": "TThe basic process will..."

The article is very slick. And I understand most of it. But I think I've asked the wrong question. I now remember I have many "Quick Parts" that I doubt migrated with the document. So, though "late binding" may take care of the Excel issue, the application will remain unusable.

My solution probably requires a better migration strategy. Particularly if my machine ever crashes.

Thanks for the heads up on the typo (now fixed). As for the other matter, you are no doubt aware that quick parts cannot be stored in a document. If you plan on sharing the document widely, you will certainly need a different approach - probably by changing the document to a template, which can store such data. The late binding will also be required to overcome the compatibility with different Excel versions.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

The document I'm attempting to migrate is a .dotm. Is that what you mean by a template? If so, maybe the the quick parts are moving with it. I don't know because the other issues stop me from getting to the point where the VBA invokes them.

If you're willing to indulge me, here is where I am with the migration.

I believe my .dotm template migration needs to include:
1. Migrating the .docm template to my wife’s Documents>Custom Office Templates folder.
2. Establishing .dotm and Custom Office Template folder as a “Trusted Site”. (see below).
3. Copying an MS Excel file to a (new, properly named path and folder) on my wife’s machine.
3a. Permanently assigning the Microsoft Excel 16.0 Object Library to the Developer>Visual Basic>Tools>Project>References. (see below)
4. Appending my 3 “Quick Parts” Building Blocks to my wife’s normal.dotm.

I’ve done 1, 2, 3 above. And Lance Whitney’s How to transfer Microsoft Word macros to another computer | TechRepublic page deals with point 4.

But…
(3a) Despite enabling the .dotm’s Developer tab on my wife’s PC, check-marking the Microsoft Excel 16.0 Object Library and saving the .dotm, the assignment won’t stick. Subsequent re-open of the .dotm shows Microsoft Excel 16.0 Object Library still not checked.
(2) Originally there were two separate security blocks. I managed to eliminate one of them through MS file level security (though I don’t recall how I did it), but the “untrusted” message persists. When I try to File>Options>Trust Center>Trust Center Settings>Trusted Locations and try to add Documents>Custom Office Templates folder, I get a popup window indicating the location is already registered. But the warning continues to appear.

I am reluctant to proceed for fear of corrupting my wife’s PC as she uses it for her job. On the other hand, I need a backup plan if and when I replace my laptop.
I’m running Microsoft Windows 10 Home Build 19044 on a Dell Inspiron 15-3567 using Microsoft® Word 2016 MSO (Version 2209 Build 16.0.15629.20196) 32-bit.
Wife’s machine running Microsoft Windows 10 Home Build 19043 on an Acer Aspire TC-885 using Microsoft Word 365 MSO (Version 2209 Build 16.0.15629.20200) 64-bit.

A response I received in another forum suggests that Microsoft does not support VBA on Office 365. I find that comment suspect. I suppose invoking a "Contact Us" on the Microsoft site might be the next step. What do you think?

I don't usually have a problem with References unchecking themselves so I can only guess what might be happening on your machine.

Firstly, I would check that the code compiles correctly on her machine. If it works on a 32bit version of Word then you are 99% of the way there but there may be some code that fails to compile on a 64bit machine.

Secondly, verify which version of Excel she has on her machine. If it happens to be older that v16 then you should be able to reference Excel 15.0 instead.

Since the file you are transferring is a template which contains building blocks there shouldn't be a reason to also copy those building blocks to her Normal.dotm template. If you have code in your template that inserts those building blocks, just make sure the VBA gets the BBs from that template.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Thank you Guessed,

If nothing else, it's nice to know that this issue is not evidence of yet another illiteracy on my part. I did contact Microsoft on this issue. The agent contends that the issue is because our computers are not running the most recent BIOS and that I need to contact the hardware manufacturers for the most recent upgrade. I remain skeptical and asked to escalate the issue to verify before dealing with the manufacturers. And thus risking corrupting both our PCs.

Because the first form does appear, I didn't think to compile on her machine. I'll give that a try. However, clicking on that form's control displays the msgbox message I coded (to alert of the missing "Excel 16.0 Object" reference).

My wife is on her machine right now so I'll have to check later. But her MSO 365 Office shows Word as 2016 and I don't recall seeing anything but the Excel 16.0 in the reference list.[/QUOTE]

No, I'm not inserting into the Quick Parts. So I'll not worry about that right now.

You can compile by viewing the code and going to Debug > Compile Project.

I don't know much about computer hardware but updating the BIOS sounds completely unrelated to your issue and I wouldn't be following that path.

Graham's suggestion of changing the code to Late Binding is a guaranteed fix - have you had a go at doing that?

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Maybe I'm not understanding. I don't recall if I compiled on my wife's pc. Certainly that's how I developed the application on my machine. I suspect I did recompile on my wife's pc given my attempts at enabling the "Excel 16.0 Object" reference. I know I set breakpoints and stepped through to make sure the path and file names generated correctly.

Agreed.

I originally anticipated my wife would want to update the rosters directly through Excel. As it turns out, she prefers how my module maintains those lists. So, if I'm going to change code, I think I'll just go with a simple .csv. I'm working on that now. But I'd prefer not to risk changing code if this is truly a configuration issue.

Please know how I truly appreciate the effort and thinking you have put into this on my behalf. Thank you.

The macros will run faster if they don't have to wait for Excel to open so importing a text file source can be a lot quicker in execution.

Debugging form opening can be problematic since you just get a message that it fails showing the line of code that called the form rather than the line of code in the initialize macro where the problem resides. I usually add breakpoints in that code so I can step through the form code and isolate the issue that way.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

I'm going to mark this as resolved given the viable alternative of code change. The original issue remains a mystery.

Despite escalation, Microsoft has not gotten back to me. Not so surprising. Given their BIOS recommendation, I'd be too embarrassed to contact me too.

Finally your Chrysalis Design LinkedIn page shares a quite impressive bio and professional services. Congratulations.