Solved

Excel Connection to Other Encrypted Excel File

Posted on 2011-03-08
5
427 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:SEMCIT
  • 2
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
To the best of my knowledge there is no way at all of doing that. You would have to open the file.
0
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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.



0
 

Author Comment

by:SEMCIT
Comment Utility
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.
0
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
Comment Utility
Sure !
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

Open in new window


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...
0
 

Author Closing Comment

by:SEMCIT
Comment Utility
Thank you so much for letting me know that my original intent was not possible, but for offering me a workaround.  The sample you shared was even better than I expected.  Thanks again.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now