They say you learn something new every day.

Posts tagged ‘vbscript’

Dynamic Vbscript Arrays (25/06/2013)

For some reason, you can’t do this in vbscript:

x = 15 
Dim demoArray(x) 

It throws this error:

Microsoft VBScript compilation (0x800A0402) 
Expected integer constant

Instead, you have to do this:

x = 15 
Dim demoArray() 
ReDim demoArray(x) 

Advertisements

Command Line ImageMagick (23/12/2012)

For some websites that I run I fairly regularly need to resize some photos to a certain width. ImageMagick is a great application for command line image manipulation.

The syntax to convert a file to a specific width and the corresponding height (which is what I want to do) is:

convert InputName.jpg -resize 550x Outputname.jpg 

What I’ve found useful is running command line scripts through vbscript:

Set wshShell = WScript.CreateObject(“WSCript.shell”)
wshShell.Run “cmd.exe /C convert InputName.jpg -resize 550x Outputname.jpg”, 0, True

I know you can do all sorts of clever things from the command line, but not quite as many as vbscript can. I find it really useful to be able to access all of the vbscript logic, and read and write text files and so on, while at the same time being able to issue commands to the command prompt.

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.

Table Details (07/03/2012)

I spent a silly amount of time yesterday troubleshooting an access error. It kept saying I had an error in my SQL, yet I knew it was correct.

In the end, it turned out one of the columns had a reserved name.

The moral of the story is, always enclose your column names with square brackets:

SELECT [Column] FROM [Table] 

It’s easy to get lazy. Just don’t. Always put square brackets round, and you won’t have this problem.

Copying Text to the Clipboard (05/03/2012)

This is quite a nifty little bit of VBScript for adding something to the user’s clipboard.

strCopy = “This text has been copied to the clipboard.”

Set objIE = CreateObject(“InternetExplorer.Application”)
objIE.Navigate(“about:blank”)
objIE.document.parentwindow.clipboardData.SetData “text”, strCopy
objIE.Quit

Have to admit, I’m beginning to go off VB Script. Obviously, it’s incredibly useful, since it works on every Windows machine, and lets you do things like make HTAs, scripts etc.

But, it’s less useful when you move onto the web, because you have to use javascript if you want any hope of it working on people’s computer. But then, with javascript, you can’t access local resources (so you can’t connect to databases) or do things like this cool clipboard copy trick.

Basically, there are some cool things that VBS can do. And you can do them in HTAs or scripts, but as soon as you go online, you need to either have a downloadable HTA, or use a mixture of javascript and a server side language.

Tag Cloud