While Vlookup in macro excel asking password

[gssachin]

Hi,

I put following code in my workbook macro

.Formula = ("=VLOOKUP("a",'C:\Misc\TestDir\[2016_TestWB.xls]Data'!$B$2:$E$4,3,0)")

My C:\Misc\TestDir\2016_TestWB.xls is password protected (Password is "Abc123")

If my above file open and I run the Macro then its working fine (Not getting popup for "Password")
But when the said workbook is close then Excel asking password and then running the code.

So where I can declare the password of workbook so excel will not open the file but also not give popup to enter password also.

 

[baitmaster]

couple of options to try / consider

- check if the file is closed, if so open it, write formulas then close it again without saving changes
- add application.displayalerts = false before you write formula. (Remember to switch back to true afterwards)