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) 

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.

Stringing it out (19/02/2012)

So, everyone knows string concatenation is bad. And by everyone, I mean everyone who understands vbscript or ASP and then me.

However, even though I’ve always known it’s bad, I haven’t really seen a way around it, without writing an array and constantly re-diming it. Which is… well, a mess really.

However, I came across the ADO textstream function the other day that allows you to do something like this:

Set Stream = CreateObject(“ADODB.Stream”)
Stream.Type = 2
Stream.Open

’ This is where you put your loop

Stream.WriteText “test”
Stream.WriteText “test2”

’ End your loop here

Stream.Position = 0

Value = Stream.ReadText()

It’s surprisingly concise. Now, maybe, in some circumstances, there is a quicker way than this. But that requires a complex extra function, whereas this is so neat.

Time to go back and rewrite some things, I think.

A Touch of Class (17/02/2012)

I think I suddenly understood classes and objects today.

I get this quite a bit with coding concepts. Or maybe concepts in general. I leave them mulling over in my head, and then suddenly, one day, I get them,

I think no one has ever really explained objects very well, and I think a key part of that is that I never got when I would use one. I think the fact that you don’t really need them makes it even harder.

But classes are basically like multiple functions in one. Here’s a demo one I built to explain them to myself:

Class TVProgram

Public StartTime
Public Test
Public ProgramTitle

Public Property Get ProgramDate
ProgramDate = Day(Test) & ” ” & MonthName(Month(Test)) & ” ” & Year(Test)
End Property

End Class

Set objTVShow = New TVProgram
objTVShow.StartTime = CDate(“17:30”)
objTVShow.Test = DateSerial(1999,9,17)
objTVShow.ProgramTitle = “TV Show”

wscript.echo objTVShow.ProgramDate & ” – ” & objTVShow.Test

So the first few lines:

Public StartTime
Public Test
Public ProgramTitle

Are like the Function variables. You assign values to these.

Public Property Get ProgramDate

Is like the Function output. You can have several of these in one class, and this is the real value to them and the thing I don’t think I got until now: classes are like functions with lots of outputs. This means you can define different outputs with the same data, or different parts of output for the same idea.

It gives me a nice warm feeling when I suddenly understand something like this. And it makes me wonder why I never understood it before. However, I think part of this comes from the fact no one ever really says what classes actually are:

You can use classes to describe complex data structures. For example, if your application tracks customers and orders, you can define two classes for them, each with a unique set of internal data (typically called properties) and functions (typically called methods). You can then manage customers and orders as if they were native VBScript subtypes. More important, because you assign a class its properties and methods (i.e., its programming interface), you have an object-oriented tool to improve VBScript applications.

Does that really help in any way? Or does that leave you even more confused by what they’re actually talking about?

Backup to the Future (09/01/2011)

And so my backup quest continues. What I learnt today was probably the most important thing so far: actually backing up the files on my computer.

There were three important requirements to my plan:

  1. It must be automatic.
  2. It must be free.
  3. The process must not affect my life or computer use.  

I turned today to looking at ways at backing up the important files on my computer.

My plan with these is to upload them to my webserver. This will probably be quite a big job at first, because there are a lot of them, and I’m happy to do this once, as long as I can do automatic incremental backups from then on.

Thankfully, I found WinSCP. Having played with it a bit now, I like WinSCP – perhaps more than Filezilla. For my purposes, what is really useful about WinSCP is that it allows you to run commands from the command line.

It took me a little while to figure this out, but I got there in the end. First you need the .bat file, something like this:

winscp /script=file.txt

This will run winSCP against the commands stored in file.txt

Then we create file.txt:

open ftp://USERNAME:PASSWORD@ftp.SERVERNAME.com
synchronize remote “D:\Files” /www/Files/
exit

This logs onto the FTP server with the given credentials and synchronizes the local file against the remote one. It adds all the missing or updated files.

What’s particularly good is you can add lines and lines of as many folders as you like. It works so well, that I’m thinking of setting all my FTP folders up like this, and ditching FileZilla entirely.

However, I still had a problem. Running the .bat file loads up a command prompt window.

 Black window of fear

Now these windows are harmless enough (although I remember a friend saying to me once “whenever I see one of these I just think ‘something has gone really wrong’.”)

However, remembering back to #3 I don’t want the process to affect me. And a black window popping up does. Allbeit not very much. Look, I just don’t want to see the black window, okay?

Luckily, there’s a way round this. Instead of running a bat file, you run the command in vbscript:

 ’Create scripting Object
Set oShell = CreateObject(“Wscript.Shell”)

 ’Run script in invisible mode
 oShell.Run “winscp /script=UploadList.ini”, 0, false

This runs the command invisibly, so I don’t even know it’s doing it. And because it’s a synchronise, it will just identify all the new or changed files and back them up.

I could put it as a scheduled task, but I turn my computer off and on everyday, so I’ll stick it in the Startup folder, and just let it run when my computer boots up. Backup plan complete, I believe.

What’s also good is that it’s another command line piece of software for me to add to my collection. I’m aiming to use a lot more command line software in 2012 and automate more things if possible.

Tag Cloud