May 042015
 

So there I was, trying to remember the password to one of the Excel script files I had but nothing was coming to me. So obviously the solution was Google.

http://superuser.com/questions/807926/how-to-bypass-the-vba-project-password-from-excel

http://blog.anidear.com/2013/10/unlock-protected-excel-file.html

  1. If it’s a .XLSM file, you can rename it to a .ZIP,
  2. extract the .\xl\vbaProject.bin,
  3. Rename the .bin to a .txt
  4. Edit in a Text editor (that can handle binary files as text)
  5. Search for DPB=
  6. Replace with DPx=
  7. Save the .txt
  8. Rename back to a .bin
  9. Copy into the .ZIP
  10. Rename the .ZIP to a .XLSM
  11. Open the Excel document in Excel
  12. Bypass the error
  13. Edit the project (Alt-F11)
  14. Right-click the project in the Project Explorer
  15. Choose VBAProject Properties
  16. Select Protection (tab)
  17. Leave Lock project for viewing checked
  18. Enter a new password
  19. Select OK
  20. Exit the VBA for Applications editor
  21. Save the Excel
  22. Re-open
  23. Alt-F11 to enter the VBA Project, use the password entered in Step 18
  24. Your project is now recovered

Other solutions apply for other file extension or for older Excel files.

Note to self: If you require true encryption or better protection for your VBA applications, create a better security strategy.

Nov 032013
 

Recently worked on an AACC (Avaya Aura Contact Center) where the partitioning of the server was determined to be the cause of the problem. While Disk Management (diskmgmt.msc) is easily accessible from START>>RUN, a screenshot is not quite as portable as text. To that end (and as a recommendation for addition to the Nortel Enterprise Audit Tool, or NEAT, used to survey Contact Center servers for Avaya engineering), I put together a script to query WMI (Windows Management Instrumentation) for the necessary information.

WMI Objects:

  • Win32_DiskDrive
  • Win32_DiskDriveToDiskPartition
  • Win32_DiskPartition
  • Win32_LogicalDiskToPartition

Using WMI queries against these objects you can derive:

  • Win32_DiskDrive => Physical Device ID (.\\.\PHYSICALDRIVE0\)
  • Win32_DiskPartition => Partition Device ID (Disk #0, Partition #1) and a derived type (e.g., Simple Volume? Primary Partition? Extended Partition/Logical Drive?)
  • Win32_LogicalDiskToPartition => Logical Drive Device ID (D:)

For quick “automated” checks of a system to verify compliance with engineering guidelines, this is a must.

Sample output:

\\.\PHYSICALDRIVE0,Disk #0, Partition #2,Basic,True,C:,Primary Partition
\\.\PHYSICALDRIVE0,Disk #0, Partition #3,Basic,False,D:,Extended Partition/Logical Drives
\\.\PHYSICALDRIVE0,Disk #0, Partition #3,Basic,False,F:,Extended Partition/Logical Drives
\\.\PHYSICALDRIVE0,Disk #0, Partition #3,Basic,False,G:,Extended Partition/Logical Drives
\\.\PHYSICALDRIVE0,Disk #0, Partition #3,Basic,False,T:,Extended Partition/Logical Drives

and

\\.\PHYSICALDRIVE0,Disk #0, Partition #2,Dynamic,True,C:,Simple Volume?
 \\.\PHYSICALDRIVE0,Disk #0, Partition #3,Dynamic,True,D:,Simple Volume?
 \\.\PHYSICALDRIVE0,Disk #0, Partition #3,Dynamic,True,F:,Simple Volume?
 \\.\PHYSICALDRIVE0,Disk #0, Partition #3,Dynamic,True,G:,Simple Volume?

The cool thing is that the script is applicable for all systems going back to Windows 2000 (Symposium 4 if I recall correctly) when the WMI query objects were instantiated in the OS by Microsoft.

Apr 142013
 

 

Apr 132013
 

Frequently I find a need to do something with Windows, and VBScript is a flexible and fairly powerful language capable of doing all sorts of useful things. Granted there is JavaScript and PowerShell; and I should learn JavaScript more since there are several utilities that Avaya (formerly Nortel) has developed to collect data on systems and being able to write my own utilities to make myself more effective is a desirable skill.

I was writing a not-work-related utility this weekend and used several web sources to refresh my memory on how various VBScript functions, statements and operators worked. Here’s a list of some of those links:

Dim myArray(10)

myArray(0) = “Nothing to see, move along.”

Erase myArray

Mid( myString, 3, len(myString)-2 )

Replace( myString, “Find me”, “Replace me”, 1, -1)

Dim re

Set re = New RegExp

re.Pattern = “^We hold these truths.*”

re.IgnoreCase = true

re.Test( myString )

Dim re

Set re = New RegExp

re.Pattern = “These are the droids we”

re.IgnoreCase = true

myJediMindTrick = re.Replace(“These are the droids we are looking for”, “These are not the droids you”)

Dim myString

Dim myObject

Dim myInteger

myString = Empty

myObject = Nothing

myInteger = Null

‘ Although you could also use Empty on the integer variable.

UBound(myArray) returns the total number array elements (regardless of element values). isEmpty(myArray) returns false on arrays even if all array elements are empty.

Quick tip:

Const vbQuote = “”””

myString = vbQuote & “Quickly” & vbQuote & ” add quotation marks to any string.”

If Not booleanDroidsWeAreLookingFor Then

Call continueLooking

End if

On Error Resume Next

On Error Goto 0

I looked this up and discovered this really doesn’t do anything that I really want to happen. It would be nice if VBScript had a little bit more powerful error throwing/catching… sadly, it does not.

Jan 172013
 

As a continuation of the previous Tarot card simulation, I’ve been playing around with re-writing the Excel tool as a VBScript that I can execute from a (Windows 7) CMD line.

Function getrnd( low, high )
	getrnd = int((high-low+1)*rnd+low)
End Function

Function reverseCard( reverseState )
	if reverseState = 0 then
		reverseCard = 1
	else
		reverseCard = 0
	end if
End Function

Sub shuffleDeck()
	fdCurCard = 1
	lmax = getrnd(33,45) ' 39 +- 6
	rmax = 78 - lmax

	reverse = getrnd(0,1)

	for i = 1 to lmax
		daCards(i) = fdCards(i)
		daReversed(i) = reverseCard(fdReversed(i))
	next

	for i = 1 to rmax
		dbCards(i) = fdCards(i+lmax)
		dbReversed(i) = fdReversed(i+lmax)
	next

	lcnt = 1
	rcnt = 1
	fcnt = 1

	s = getrnd(0,1)

	do while fcnt<=78
 		r = getrnd(1,4)
 		if (s=0) then
 			do until r=0 or lcnt>lmax
				fdCards(fcnt) = daCards(lcnt)
				fdReversed(fcnt) = daReversed(lcnt)
				lcnt = lcnt+1
				fcnt = fcnt+1
				r = r-1
			loop
		else
			do until r=0 or rcnt>rmax
				fdCards(fcnt) = dbCards(rcnt)
				fdReversed(fcnt) = dbReversed(rcnt)
				rcnt = rcnt+1
				fcnt = fcnt+1
				r = r-1
			loop
		end if

		if (s=0) then
			s=1
		else
			s=0
		end if
	loop

End Sub

This assumes that you’ve already got a pre-populated deck. The challenge with this approach is (I’ve found) that the cards aren’t truly randomized at all. If you start with a deck that is not randomized at all (completely ordered, as if it just came out of the box), it takes a while for the randomization to start to occur. For instance, if the Fool is the top card, statistically speaking, that card should end up in the first 8-12 cards you pull… until you really randomize the deck.

The simplest approach to radomizing the deck that I could think of was to do a random card swap. Do a for-next loop through the entire deck and swap each card randomly with some other card in the deck. By the time you complete a pass through the deck (78 total card swaps) you have a fairly random deck setup and the shuffle algorithm simulates shuffling much better.

sub randomizeDeck()
	fdCurCard = 1

	for i = 1 to 78
		tcardnum = i
		do until tcardnumi
			tcardnum = getrnd(1,78)
		loop

		tcard = fdCards(i)
		trev = fdReversed(i)

		fdCards(i) = fdCards(tcardnum)
		fdReversed(i) = fdReversed(tcardnum)

		fdCards(tcardnum) = tcard
		fdReversed(tcardnum) = getrnd(0,1)
	next
end sub

Anyway… This is a work in progress.

Jun 222012
 

This time with comments:

Sub NoteTable()
    ' new subroutine, named NoteTable
    ' declare variable oOuterTable
    Dim oOuterTable As Word.Table
    Dim oInnerTable As Word.Table
    Dim oColumn1 As Word.Range
    ' Create a new table, 2 columns and 1 row, at the current cursor location, and assign the memory reference to the oOuterTable variable
    Set oOuterTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    ' set the context to oOuterTable
    With oOuterTable
        ' Set the width on the first column of the table to 1.1 inches
        .Columns(1).SetWidth ColumnWidth:=InchesToPoints(1.1), RulerStyle:=wdAdjustNone
        ' Set the width of the second column of the table to 5.5 inches
        .Columns(2).SetWidth ColumnWidth:=InchesToPoints(5.5), RulerStyle:=wdAdjustNone
        ' Center the contents of the first column (two steps)
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        ' Remove paragraph spacing before/after the text in the second column and set the vertical alignment to center
        ' (We assume left justification on the paragraph)
        .Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
        .Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
        .Cell(1, 2).VerticalAlignment = wdCellAlignVerticalCenter
        ' Set oColumn1 variable to the memory reference for cell 1,1
        Set oColumn1 = .Cell(1, 1).Range
        ' Add a nested table within cell 1,1 with one column and one row, and assign the memory reference to the oInnerTable variable
        Set oInnerTable = ActiveDocument.Tables.Add(Range:=oColumn1, NumRows:=1, NumColumns:=1)
    End With
    '  set the context to oInnerTable
    With oInnerTable
        ' Center the text horizontally and vertically (two step)
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        ' Set the font color to white
        .Cell(1, 1).Range.Font.ColorIndex = wdWhite
        ' Set the cell shading to a dark blue (Dark Blue, Text 2, Lighter 40%)
        .Cell(1, 1).Shading.BackgroundPatternColor = -553582695
        ' Set paragraph spacing before and after the text to ensure uniform borders around the text
        .Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 6
        .Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 6
        ' Fill cell 1,1 (the inner table) with the text "NOTE"
        .Cell(1, 1).Range.Text = "NOTE"
    End With
    ' Select the second column in the outer table (a time saver so I don't have to select this to start typing the notes,
    ' I can just fire the macro and keep typing.)
    oOuterTable.Cell(1, 2).Select
    ' Clear the undo history so that all the steps taken by the macro are removed from the undo history
    ' NOTE: This will clear the entire undo history. There does not appear to be a method for pausing the undo history
    ' then resuming once the macro completes its execution.
    ActiveDocument.UndoClear
End Sub

The next step will be to update the macro so that I can highlight some text, then cut that text, build the table and paste the text into the second column of the outer table. That way, I can go back through old documentation and clean up my notes quickly.

Jun 202012
 

I worked at a law firm a few years back and they had some absolutely fabulous formatting for internal documentation. There was just a ton of stuff I learned about how to nicely format documentation, and I picked up a number of styles that I’ve carried forward since. An example is:

NOTE
Example notation. Basically this is a outer table (two columns) and an inner table (in the left column cell). The inner table is shaded and contains the NOTE/WARNING text, and the right column of the outer table contains all the notes or warnings (this text.)

I think it looks attractive and improves documentation readability. But, I’ve had to custom make this notation text from one document to the next because I didn’t have any kind of standardization, or I had to open an old document and copy the formatted table from one document to the next. I’ve been wondering how to automate that… So I did some poking around with Word 2010 today, recording a sample VBA script and then doing lots of Google-fu to research how to customize a short script to do exactly what I want.

Now, I have two scripts that do exactly that, and I’ve added them to my Word 2010 ribbon to quickly build the NOTE or WARNING tables so they look exactly and consistantly the way I want. Wheee!

Sub NoteTable()
    Dim oOuterTable As Word.Table
    Dim oInnerTable As Word.Table
    Dim oColumn1 As Word.Range
    Set oOuterTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    With oOuterTable
        .Columns(1).SetWidth ColumnWidth:=InchesToPoints(1.1), RulerStyle:=wdAdjustNone
        .Columns(2).SetWidth ColumnWidth:=InchesToPoints(5.5), RulerStyle:=wdAdjustNone
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
        .Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
        .Cell(1, 2).VerticalAlignment = wdCellAlignVerticalCenter
        Set oColumn1 = .Cell(1, 1).Range
        Set oInnerTable = ActiveDocument.Tables.Add(Range:=oColumn1, NumRows:=1, NumColumns:=1)
    End With
    With oInnerTable
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 1).Range.Font.ColorIndex = wdWhite
        .Cell(1, 1).Shading.BackgroundPatternColor = -553582695
        .Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 6
        .Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 6
        .Cell(1, 1).Range.Text = "NOTE"
    End With
    oOuterTable.Cell(1, 2).Select
    ActiveDocument.UndoClear
End Sub
Sub WarningTable()
    Dim oOuterTable As Word.Table
    Dim oInnerTable As Word.Table
    Dim oColumn1 As Word.Range
    Set oOuterTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    With oOuterTable
        .Columns(1).SetWidth ColumnWidth:=InchesToPoints(1.1), RulerStyle:=wdAdjustNone
        .Columns(2).SetWidth ColumnWidth:=InchesToPoints(5.5), RulerStyle:=wdAdjustNone
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
        .Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
        .Cell(1, 2).VerticalAlignment = wdCellAlignVerticalCenter
        .et oColumn1 = .Cell(1, 1).Range
        .et oInnerTable = ActiveDocument.Tables.Add(Range:=oColumn1, NumRows:=1, NumColumns:=1)
    End With
    With oInnerTable
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 1).Range.Font.ColorIndex = wdWhite
        .Cell(1, 1).Shading.BackgroundPatternColor = -721371137
        .Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 6
        .Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 6
        .Cell(1, 1).Range.Text = "WARNING"
    End With
    oOuterTable.Cell(1, 2).Select
    ActiveDocument.UndoClear
End Sub