Solved How to get destination file to open password protected file

February 24, 2016 at 08:43:25
Specs: Windows 7
There was a question back in 2012 re: linking to a password protected file. Great help re: VBA code to open protected worksheets. I did this, and it worked (ie. the workbooks were opened). However, the destination file isn't reading/recognizing them. Any idea why this is happening, and what I can do to make the destination file recognized the opened source files???

Thansk.

message edited by braincapers


See More: How to get destination file to open password protected file

Report •

✔ Best Answer
February 25, 2016 at 07:55:44
What happens if you open the files manually and enter the password? Do the links update as expected?

Since we have now eliminated "multiple instances" as the cause, I'd like to now eliminate the macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 24, 2016 at 11:47:30
Please don't take this the wrong way, but it is impossible for us to answer you question without more detail. Since we can't see your workbook from where we're sitting, we have no idea what you mean by "the destination file isn't reading/recognizing them".

Are you getting an error? Is nothing happening?

We don't even know what you are trying to do once the code opens the protected worksheets.

Please provide some more detail and we'll see what we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 24, 2016 at 12:23:38
Thanks so much for followin up on this.

Sorry, hopefully this will provide the necessary context and background (without being too long).

Each staff member updates the volume of their production each day into their respective password protected EXEL workbook. The department level file links to, and "grabs" the daily production from each of the staff member's individual files. When we open the department level file, and the links try to be updated, we are asked to input the passwords for each of the underlying staff files. To avoid needing to input the indivdiual passwords each time we go into the department level file, I wrote a macro (stolen from the thread back in 2012) that opens each of the files. Unfortunately, even though the indivdual files are open, the updates are not reflected in the department file.

There isn't an error message. It is just that the updates in the individual file do not flow through to the department file.

What is strange is that the cell reference looks as though the file is not open (ie. full network path, etc.) By this I mean, the cell reference will say something like:

=+'R:\Folder\Sub folder\[Staff Name- Volume Log.xlsx]Worksheet'!G146 when you would expect, given the source file is open, the cell reference to just show:

Staff Name Volume Log.xlsxWorksheet'!G146

Hopefully that makes sense. Thanks again so much for your time. If you need further details, obviously, please let me know.


Report •

#3
February 24, 2016 at 17:36:08
I think the key to your problem is the fact that the link does not seem to know that the source file is open.

That usually happens when the 2 files are open in separate instances of Excel.

With rare exceptions, when 2 instances of Excel are open at the same time, they don't talk to each other.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 24, 2016 at 18:47:47
Thanks a lot for this. I really appreciate it. I guess the question then becomes, if the issue is that the macro from the destination file opens up the source files in separate instances of EXCEL, is there a way of getting the source file to open in the same instance as the destination file? Or, am I just out of luck in terms of linking to password protected source files?

Thanks again so much for your time and help


Report •

#5
February 24, 2016 at 20:31:32
What version of Excel are you using?

In 2013, the macro I tested opens the source file in the same instance. However, 2013 is different than earlier versions in that it is actually harder to open 2 instances of Excel 2013 at the same time.

See here, if you are interested:

http://sqlblog.com/blogs/marco_russ...

As for the fact that (if) your macro is opening the source files in a separate instance, that's something I can't speak to until I have a chance to sit down with an earlier version of Excel and test it.

The only other version I have easy access to is 2010, which is still MDI, so I'll see if the macro opens the source file in a separate instance or the same. I may get to it tomorrow or it may have to wait until Friday.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 25, 2016 at 07:03:52
I tested the simple one line macro from the 2012 thread in both Excel 2010 and 2013.

Sub Open_Protected_File()
  Workbooks.Open Filename:= _
     "\\MyPath\Users\UserName\My Documents\MyFile.xlsm", Password:="Secret"
End Sub

It opens the file in the same instance of Excel and all links to the source file update as expected. Since I can't see your workbooks or process from where I am sitting, I can not say why your files are opening in a separate instance of Excel - if that is actually what is happening. I only suggested that because of the way your links are appearing in the destination workbook. There could be other reasons that I am not aware of.

Have you verified that "multiple instances" is really the problem? A quick way to check would be to open the Task Manager and click on the Processes tab. If there is more than one listing for EXCEL.EXE after you run the macro, then more than one instance is running. If not, then I don't know why your links don't recognize the source files as being open.

Please keep us updated.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 25, 2016 at 07:24:10
Thanks again for following up. I checked Task Manager and there is in fact only 1 instance of EXCEL running. All open files are appearing in the applications tab, but only 1 version of EXCEL appears on the process tab. Regrettably, since I work for a multi-million $$$ company that doesn't want to invest in technology, I am running EXCEL 2007. I don't know if that makes any difference or not. I will keep playing around and hope that something pops.

Thanks again for your help and engagement on this. I really appreciate it.


Report •

#8
February 25, 2016 at 07:55:44
✔ Best Answer
What happens if you open the files manually and enter the password? Do the links update as expected?

Since we have now eliminated "multiple instances" as the cause, I'd like to now eliminate the macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
February 25, 2016 at 08:15:34
I think I have, with the help of a co-worker, perhaps stumbled upon the problem. My macro to open the files was something like:

Workbooks.Open Filename:="\\network drive\sub drive\folder\Sub folder\File name.xlsx, Password:="XXXXX"

However, the cell formula in the destination file was:

R:\folder\Sub folder\File name.xlsx\worksheet,Cell

I am just playing around now, and have used the more detailed file location (ie. what was used for the macro) in the cell formulas, and it seems to be working. I have to continue to play around to make sure. But it looks promising.

Does that make sense that this would have been the problem.

Thanks again so much for your help.


Report •

#10
February 25, 2016 at 09:12:02
SOrry, I meant to say - thanks for asking the question re: what happens when we open the files manually. The links worked, and that is what triggered us to look at the specific links. So, again, thanks so much for your time and help. It still seems strange to me, but as long as it works.

Report •

#11
February 25, 2016 at 09:49:27
I think what you are saying makes sense, but not looking exactly at your links and the file paths, etc. it's hard for me to say "Yep, that's it".

I have seen cases where the letter designation for a network drive "shortcut" changes i.e. the shortcut still works, but the drive letter may not be the same as it was the last time is was open. Maybe that's somehow related.

In any case, it looks like you are sneaking up on the root cause. Keep at it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
March 1, 2016 at 13:36:22
The wonders of EXCEL never cease to amaze. It looks like the issue as I described above was the problem. By using the specific path (ie. as oppose the the shortcut, etc.) the links seem to be working just fine.

I want to thank you again so much for your time and insights. I really appreciate it.


Report •

Ask Question