Access Without Opening a Modify Protected file using VBA

[MeMikeExcel]

Hello,

I have a modify protected excel file and wanted to access it in the background to extract some data to another workbook without opening it.
Is it even possible to do such task using VBA ? Since the file is modify protected, a message automatically pops up to select Read-Only or enter the password when it is open. The pops up of the message box affects and interrupts the automation process with the VBA code. I would need to manually click on the Read-Only option to open the file before continuing the automation with the VBA Code. So, any suggestion to resolve this issue would be greatly appreciated.

 

[vletm]

Send a sample Excel-file, which has that kind of feature.

 

[MeMikeExcel]

Hello @vletm,

Please see the attached two files.

Extraction button is on the Test Main file.

Once the button is click, it will open the data file with the pops up of the Password dialog box.
I will need to intervene by selecting Read Only to open the file, as I do not have the password, to process the extraction code.
I want to eliminate or bypass the selection step in the extraction process. I do not know how to do that.

Thank you for your help in advance.

 

[vletm]

MeMikeExcel​

Was Your something about Access Without Opening ?
... and now, You want to open it ... it's different task.
If You're missing ... the password, then why You cannot ask it from real owner?

 

[vletm]

... You can get those four values from that file
# without opening it
# without the password
with basic formula, which You can see from this file
... take care that path to file is correct.
If this really needs VBA then
-- You can do same way with VBA.
Your button works only with Your settings.

 

[MeMikeExcel]

My thought was if I can do the extraction without opening the modify protected file, then that will great.
The reason I code to open the file first because i do not know how to code without opening the file but still be able to access it in the background.
Yes, i can ask for the password from the file owner and hard code it into coding to make it work. But I would like to gain the skill to learn how to code it without knowing the password to access the file.

 

[vletm]

You can see it from that file.

 

[MeMikeExcel]

Your example is a great reference !!! Thank you.
However, the sample files I provided only give a single column with few rows of data.
The actual file I am working on contains over hundreds of rows and maybe 10 columns of data for the extraction.
Would you have an example using VBA coding for this scenario ?

 

[vletm]

For me, that kind of situation gives a strong warning.
Too many time that happens while someone modifies file ... left one cell in edit-mode and left to holiday for few weeks.
It's possible that one ... many ... hundreds ... thousands of cells have not saved yet.
... that means, You'll get old data from that file.
Do it matter?
... do it matter if You'll show two weeks old data to someone?
>
You can always loop rows and columns as You want.
And this would be smoother if that data-file could be open.

 

[MeMikeExcel]

Ah yes, i understand what you mean on the old data from the file part.
However, the data file is an active file being use daily by two departments.
So, that the worry of left one cell in edit-mode and left to holiday for few weeks would not apply to this case.
I can probably use the Fill in my vba coding to go thru the multi-rows and columns of data on the extraction.

But if you have an example to share, then please let me know.

 

[vletm]

Seems that You've skipped to think ...
You could modify that cells A1 formula ... modify $A1 to $A1:AA1000 or as You would like to do.

 

[MeMikeExcel]

Ah yes, thank you for the reminder. I will try it out. Thank you.