I needed to write a script to remove passwords from Excel files today (I did know, the password, this wasn’t a cracking exercise).
The function is pretty self explanatory. it takes two arguments, and removes the password.
Filenamewithpath = “D:\VBscripts\test.xlsx”
Password = “6tg4HHE”
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = FALSE
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open(Filenamewithpath„„Password)
objWorkbook.Password = “”
set objExcel = nothing
set objWorkbook = nothing
I don’t use Excel that much these days. Increasingly, I’m having to work with large relational databases, some of which are 50, 60GB big, and Excel doesn’t really play well with that much data.
However, sometimes, when you export a report, there’s nothing as easy as loading it into Excel, and messing around with it there. And probably messing around with it involves doing a vlookup.
Now, I’ll put it out there: I think I’m quite good at Excel. Which was why I was surprised to read this article about how vlookup is the wrong way of doing this sort of thing.
Index and Match are actually two functions, so it means passing the results of one function another. This is actually quite an advanced skill, so I guess this is why no one does this.
However, there’s one key thing here that makes Index and Match much, much faster than vlookup. And if there’s one reason you should switch, this is it:
Suppose you have a large table with many columns of product information. And suppose you want to look up a specific SKU in the table and return information about it from a variety of columns within the table.
If you use VLOOKUP you must look up the same SKU for each column of information you need. Those duplicate lookups take a long time to perform.
But if you use an INDEX-MATCH approach, you could set up one MATCH formula that returns only the row-index number for the product that interests you. Then you can use any number of INDEX formulas that get their row-index number from the cell with that single MATCH formula. Both MATCH and VLOOKUP take about the same time to calculate. But INDEX works almost instantly. So if you want to return ten items for a SKU, the VLOOKUP method will take roughly ten times longer than the INDEX-MATCH approach.