excel

Removing Excel macro passwords

04/052010

At work, I recently needed to edit a database connection string in an Excel spreadsheet. Unfortunately the macros were password protected, the person who wrote the spreadsheet left the company some years ago, and we had no record of the password.

Luckily, thanks to a post on David Bugden’s blog, I discovered that it’s simple to circumvent VBA macro password protection without having to purchase a commercial utility such as Passware’s VBA Key.

The hex editor method worked for me in Excel 2003, although I’m not sure whether Microsoft have beefed up the security in 2007 and later versions.

If you want to remove the password used to protect/unprotect worksheets and workbooks, there is some information available here.

Banish quotation marks from text files exported from Excel

26/082006

When exporting Excel worksheets to CSV (comma-separated values) or tab-delimited text, it will add quotation marks (“..”) around any fields containing commas or quotes.

While such files can be re-imported into Excel without problems, it can cause other applications to choke. It is possible to open the files in a text editor to remove the problematic characters, or create a Word macro to do the same job automatically, but the simplest solution is to bypass Excel’s own file export filters and use a macro such as the following to perform the export:

Public Sub TextNoModification()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub

The delimiter and output filename can be changed by editing the code. The macro above is taken from this very useful page from McGimpsey & Associates which contains a whole host of Excel tips.