New feature! Upgrade and increase expert visibility of your issues with Priority Questions.
Solved
Excel Connection to Other Encrypted Excel File
Posted on 2011-03-08
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
Making a data connection to an encrypted file indeed is problematic.
A possible solution could be to open the file with a macro, automatically copy & paste the required data to your unencrypted excel file, and close the encrypted file again.
This can be performed without the user having to do anything, althoug in this way the password used to open the encrypted file will be visible to anyone who knows how to open the vba editor.
A better option would be to ask the user for a password, and then use this to open the file.
A possible solution could be to open the file with a macro, automatically copy & paste the required data to your unencrypted excel file, and close the encrypted file again.
This can be performed without the user having to do anything, althoug in this way the password used to open the encrypted file will be visible to anyone who knows how to open the vba editor.
A better option would be to ask the user for a password, and then use this to open the file.
Thank you both for your guidance and thank you akoster for your possible solution. Is there any chance you could share a simple example of how a macro would look to open the file that required a password? Thanks again.
Sure !
paste this code in the sheet1 section of the vba editor and run it.
of course you'll have to update the source_* and dest_cell settings...
Sub copy_from_protected_file()
Dim source_file As String, source_pwd As String, source_sheet As String, source_range As String, dest_cell As String
Dim source As Workbook
Dim dest As Range
source_file = "D:\temp\2.xlsx"
source_pwd = "test"
source_sheet = "Sheet1"
source_range = "A1:C4"
dest_cell = "B6"
'-- specify destination range
Set dest = ActiveSheet.Range(dest_cell)
'-- open encrypted file
Set source = Workbooks.Open(Filename:=source_file, Password:=source_pwd)
'-- copy source range
source.Worksheets(source_sheet).Range(source_range).Copy
Me.Paste Destination:=dest
'-- close encrypted file
source.Close False
Set source = Nothing
End Sub
paste this code in the sheet1 section of the vba editor and run it.
of course you'll have to update the source_* and dest_cell settings...
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Course of the Month9 days, 9 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.