They say you learn something new every day.

Posts tagged ‘microsoft’

Excel Passwords (17/09/2012)

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”

RemovePassword Filenamewithpath,Password

FUNCTION RemovePassword(Filenamewithpath,Password)

Set objExcel = CreateObject(“Excel.Application”)
 objExcel.Visible = FALSE
 objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Open(Filenamewithpath„„Password)
 objWorkbook.Password = “”
 objWorkbook.SaveAs Filenamewithpath
 objWorkbook.close

  set objExcel = nothing
  set objWorkbook = nothing 

END FUNCTION

Lookup Lookdown (31/03/2012)

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. 

Tag Cloud