Have one brand new document 1.xlsm. Have one encrypted
document 2.xlsx, which just has data in the upper left of 'Sheet1'. From inside of 1.xlsm, would like to make a Data Connection to 2.xlsx, but when I try this if I click on the 'Test Connection' button, I get the message:
Microsoft Data Link Error: Test connection failed because of an error in initializing provider. External table is not in the expected format.
My intention is to add the data from 'Sheet1' of the encrypted
2.xlsx as a table on 'Sheet2' of 1.xlsm and then permanently hide Sheet2 in 1.xlsm.
The solution I am looking for is how to make the connection from within 1.xlsm to the encrypted
2.xlsx without getting an error message and without having to enter the password that is necessary to enter when just opening 2.xlsx. If there is a way to include the password in the Connection String of the connection, that would be fine, or any other solution you could share would be great too.
I'm including the two sample files I've been testing with.
Also, I am using Microsoft Excel 2007.
Also, the password for 2.xlsx is 'test'.
Thank you so much for your time. 1.xlsm 2.xlsx