They say you learn something new every day.

Posts tagged ‘excel’

Converting days to Numbers in Excel (15/11/2012)

I have a spreadsheet, with a list of days in it. I want to sort them by their order in the week (ie, Monday – Sunday), but Excel being Excel, it seems them as words and sorts them in alphabetical order.

I’ve had a bit of a play and found two functions I never really use, both of which can do this:

Match

=MATCH(A1,{“Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”,”Sunday”},0) 

This is neat and  concise, but just returns the array element number.

Lookup

=LOOKUP(A1,{“Friday”,5;”Monday”,1;”Saturday”,6;”Sunday”,7;”Thursday”,4;”Tues
day”,2;”Wednesday”,3}) 

 A bit longer, but allows you to return arbitrary values, not just the array position.

Advertisements

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

Shape Constants in Excel (07/09/2012)

So, I’ve been messing around automating Excel recently. And one thing I’ve discovered is that Microsoft has loads of “constants” which don’t work in vbscript.

It’s kind of annoying – but it does mean it helps with identifying what, say, 53, will do (it draws a box with an arrow).

Luckily, there’s a list of them all, which I’m duplicating here, just in case the original site disappears.

msoShapeMixed -2 
msoShapeRectangle 1 
msoShapeParallelogram 2 
msoShapeTrapezoid 3 
msoShapeDiamond 4 
msoShapeRoundedRectangle 5 
msoShapeOctagon 6 
msoShapeIsoscelesTriangle 7 
msoShapeRightTriangle 8 
msoShapeOval 9 
msoShapeHexagon 10 
msoShapeCross 11 
msoShapeRegularPentagon 12 
msoShapeCan 13 
msoShapeCube 14 
msoShapeBevel 15 
msoShapeFoldedCorner 16 
msoShapeSmileyFace 17 
msoShapeDonut 18 
msoShapeNoSymbol 19 
msoShapeBlockArc 20 
msoShapeHeart 21 
msoShapeLightningBolt 22 
msoShapeSun 23 
msoShapeMoon 24 
msoShapeArc 25 
msoShapeDoubleBracket 26 
msoShapeDoubleBrace 27 
msoShapePlaque 28 
msoShapeLeftBracket 29 
msoShapeRightBracket 30 
msoShapeLeftBrace 31 
msoShapeRightBrace 32 
msoShapeRightArrow 33 
msoShapeLeftArrow 34 
msoShapeUpArrow 35 
msoShapeDownArrow 36 
msoShapeLeftRightArrow 37 
msoShapeUpDownArrow 38 
msoShapeQuadArrow 39 
msoShapeLeftRightUpArrow 40 
msoShapeBentArrow 41 
msoShapeUTurnArrow 42 
msoShapeLeftUpArrow 43 
msoShapeBentUpArrow 44 
msoShapeCurvedRightArrow 45 
msoShapeCurvedLeftArrow 46 
msoShapeCurvedUpArrow 47 
msoShapeCurvedDownArrow 48 
msoShapeStripedRightArrow 49 
msoShapeNotchedRightArrow 50 
msoShapePentagon 51 
msoShapeChevron 52 
msoShapeRightArrowCallout 53 
msoShapeLeftArrowCallout 54 
msoShapeUpArrowCallout 55 
msoShapeDownArrowCallout 56 
msoShapeLeftRightArrowCallout 57 
msoShapeUpDownArrowCallout 58 
msoShapeQuadArrowCallout 59 
msoShapeCircularArrow 60 
msoShapeFlowchartProcess 61 
msoShapeFlowchartAlternateProcess 62 
msoShapeFlowchartDecision 63 
msoShapeFlowchartData 64 
msoShapeFlowchartPredefinedProcess 65 
msoShapeFlowchartInternalStorage 66 
msoShapeFlowchartDocument 67 
msoShapeFlowchartMultidocument 68 
msoShapeFlowchartTerminator 69 
msoShapeFlowchartPreparation 70 
msoShapeFlowchartManualInput 71 
msoShapeFlowchartManualOperation 72 
msoShapeFlowchartConnector 73 
msoShapeFlowchartOffpageConnector 74 
msoShapeFlowchartCard 75 
msoShapeFlowchartPunchedTape 76 
msoShapeFlowchartSummingJunction 77 
msoShapeFlowchartOr 78 
msoShapeFlowchartCollate 79 
msoShapeFlowchartSort 80 
msoShapeFlowchartExtract 81 
msoShapeFlowchartMerge 82 
msoShapeFlowchartStoredData 83 
msoShapeFlowchartDelay 84 
msoShapeFlowchartSequentialAccessStorage 85 
msoShapeFlowchartMagneticDisk 86 
msoShapeFlowchartDirectAccessStorage 87 
msoShapeFlowchartDisplay 88 
msoShapeExplosion1 89 
msoShapeExplosion2 90 
msoShape4pointStar 91 
msoShape5pointStar 92 
msoShape8pointStar 93 
msoShape16pointStar 94 
msoShape24pointStar 95 
msoShape32pointStar 96 
msoShapeUpRibbon 97 
msoShapeDownRibbon 98 
msoShapeCurvedUpRibbon 99 
msoShapeCurvedDownRibbon 100 
msoShapeVerticalScroll 101 
msoShapeHorizontalScroll 102 
msoShapeWave 103 
msoShapeDoubleWave 104 
msoShapeRectangularCallout 105 
msoShapeRoundedRectangularCallout 106 
msoShapeOvalCallout 107 
msoShapeCloudCallout 108 
msoShapeLineCallout1 109 
msoShapeLineCallout2 110 
msoShapeLineCallout3 111 
msoShapeLineCallout4 112 
msoShapeLineCallout1AccentBar 113 
msoShapeLineCallout2AccentBar 114 
msoShapeLineCallout3AccentBar 115 
msoShapeLineCallout4AccentBar 116 
msoShapeLineCallout1NoBorder 117 
msoShapeLineCallout2NoBorder 118 
msoShapeLineCallout3NoBorder 119 
msoShapeLineCallout4NoBorder 120 
msoShapeLineCallout1BorderandAccentBar 121 
msoShapeLineCallout2BorderandAccentBar 122 
msoShapeLineCallout3BorderandAccentBar 123 
msoShapeLineCallout4BorderandAccentBar 124 
msoShapeActionButtonCustom 125 
msoShapeActionButtonHome 126 
msoShapeActionButtonHelp 127 
msoShapeActionButtonInformation 128 
msoShapeActionButtonBackorPrevious 129 
msoShapeActionButtonForwardorNext 130 
msoShapeActionButtonBeginning 131 
msoShapeActionButtonEnd 132 
msoShapeActionButtonReturn 133 
msoShapeActionButtonDocument 134 
msoShapeActionButtonSound 135 
msoShapeActionButtonMovie 136 
msoShapeBalloon 137 
msoShapeNotPrimitive 138 

Automating Excel (04/09/2012)

When I’m outputting text or data, I tend to output it into text files. They’re simple and clean. But people often want Excel files. Yeah, I don’t understand it either.

I sat down today and had a look at writing extra data to an Excel file, and found that adding data to the last line isn’t always simple.

set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(FILENAME)
 objExcel.Sheets(SHEETNAME).Select
LastRow = objExcel.ActiveSheet.UsedRange.Rows.Count
 objExcel.Cells(LastRow + 1, 1).Value = “Test value”
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

This function loads an Excel file, finds the first blank row and writes to it, then saves and closes the file. And does so, all invisibly to the user.

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