Application.Filesearch Replacement For Office 2007

If you have ever used completed a search for files using VBA you probably made use of Application.Filesearch.  However, if you’ve updated to Office 2007 or 2010 you may have noticed that Filesearch has been removed… but the (very) old Dir remains, so you can use this instead, an example of which follows:

Sub FileSearch()
    Dim fso As Object
    Dim FileName As String
    Dim strArr(1 To 65536, 1 To 1) As String
    Dim i As Long

    ' Set the directory / filename you are looking for
    Const strDir As String = "C:\Your\File\Path"
    Const SearchTerm As String = "YourFileNameTerm"

    ' Complete the search
    Let FileName = Dir$(strDir & "\*" & SearchTerm & "*.xls")
    Do While FileName <> vbNullString
        ' For each file found load to the array
        Let i = i + 1
        Let strArr(i, 1) = strDir & "\" & FileName
        Let FileName = Dir$()
    Loop

    ' Search within sub-folders
    Set fso = CreateObject("Scripting.FileSystemObject")
    Call RecurseSubFolders(fso.GetFolder(strDir), strArr(), i, SearchTerm)

    ' Tidy up and copy the results to the active worksheet
    Set fso = Nothing
    If i > 0 Then
        Range("A1").Resize(i).Value = strArr
    End If

End Sub

Private Sub RecurseSubFolders( _
    ByRef Folder As Object, _
    ByRef strArr() As String, _
    ByRef i As Long, _
    ByRef SearchTerm As String)

    Dim SubFolder As Object
    Dim FileName As String

    ' Search sub folders
    For Each SubFolder In Folder.SubFolders
        Let FileName = Dir$(SubFolder.Path & "\*" & SearchTerm & "*.xls")
        Do While FileName <> vbNullString
            Let i = i + 1
            Let strArr(i, 1) = SubFolder.Path & "\" & FileName
            Let FileName = Dir$()
        Loop
        Call RecurseSubFolders(SubFolder, strArr(), i, SearchTerm)
    Next
End Sub

DreamWeaver - while executing onload in _onOpen.htm, the following JavaScript error(s) occurred:

 

To fix the above error navigate to one of the file address below:

Windows XP:

C:\Documents and Settings\Administrator\Ap Data\Adobe\Dreamweaver CS5\en_US\Configuration\

Vista:

C:\Users\[Your Username]\AppData\Roaming\Adobe\Dreamweaver CS5\en_US\Configuration\

Windows 7:

C:\Users\[user]\Ap CS4\en_US\Configuration\

And delete the file called ‘WinFileCache-[random numbers].dat’.

“Code execution has been interrupted”

Every now and then, while executing macro routines within Excel you may get the error message “code execution has been interrupted”.  This error message should only appear if you hit “Ctrl+Break” to stop the execution and view the code. But… sometimes it just happens, normally with no apparent reason.

If you hit the “Continue” button, the code will execute for a while, and may even finish, but you may get the same error message again. Sometimes you may even have to click the “continue” button several times to complete the execution of a macro.

This issue caused intermittent problems for me over a long period of time, that was until I found the solution.

To stop this, often re-occurring, problem all you have to do is add one line of code “Application.EnableCancelKey = xlDisabled” as the first line of your macro.

This should fix the problem and enable you to execute your macro code successfully without getting the dreaded error message “Code execution has been interrupted” again.

The above fix is fine for any projects being rolled out to a group of users. If you’re testing code and using “Ctrl+Break” this might be a hindrance. Another fix is, while In the debug window, press Ctrl+Break, this again fixes the issue.

Of course, the next time you press “Ctrl+Break” in the Excel window the problem may well come back!

I say should because this is not guaranteed :( I have had occasions when this does not work, rebooting the PC is the only other possible solution I’ve found.

Compile Error: "Object Library Invalid..."

If you’ve ever come across the ‘Compile Error’ message you’ll know how much fun they can be to correct. 

Quite often when this message appears you’ll find yourself faced with the VBE telling you that ‘Left’ is not a valid function !  The first thing you should check is what references you are using.  If you’ve ever written some code on one version of Excel and tried to role it out to users with an older version you’ll have seen this many times. 

When you add references in an Excel 97 file and use it in Excel 2003 the references will automatically update.  If you try and do that the other way round the references do not ‘back-date’.  You’ll need to go to the references box and un-check the ‘Missing’ reference.  Then look through the list and find the older version which is available to the Excel version you are using.

The most recent one I found displayed the following message:

Object Library Invalid or Contains References to Object Definitions that could not be found.

I’ll add here that I was working in an office which predominately used Excel 97 with several PCs with Excel 2003.  A little retro there ;)

The error occurred in Excel 2003 using an Add-In (.xla) created in Excel 2003 !  I checked for missing references and found none.  The file opened and the code ran without issue on other machines, both Excel 97 and Excel 2003. 

After turning to Google I found this thread at ozgrid.com.  The answer; deleting the all the EXD files left by previous iterations of the project. EXD files cache ActiveX component information. If the component changes without removing the EXD file (which is created when the component is first used), then the system will be out of sync. 

To delete the EXD files go to command prompt and type the following DOS commands:

• > CD \Document and Settings
• > DEL /S /A:H /A:-H *.EXD

To explain what that does I’ll quote from the thread:

Essentially the command recursively deletes all your hidden and exposed exds. That will make all your ActiveX components load a bit slower the first time you use them again, but it should also clear out the problematic ones.

So, I’ll keep my fingers crossed that the above works for you too.

Using VBA to Convert a Column Number To A Letter

Sometimes while manipulating data in Excel you’ll have a integer which represents the column number, but need the column letter.

The function below can translate that number to the column letter required.  This function has been tested and works to 702, column ‘ZZ’.

Function ConvertToLetter(iCol As Integer) As String
'-----------------------------------------------------------------------------
' Procedure : ConvertToLetter
' Author    : Niall - Zypher.co.uk
' Purpose   : Convert a number to a column letter
'             Tested to 256 columns, assumed to work until 702 (ZZ)
'-----------------------------------------------------------------------------
'
    If iCol <= 26 Then
        ' Columns A-Z
        ConvertToLetter = Chr(iCol + 64)
    Else
        ConvertToLetter = Chr(Int((iCol - 1) / 26) + 64) & _
                          Chr(((iCol - 1) Mod 26) + 65)
    End If
End Function

As always, any questions or enquiries let us know via the comments section.

Add worksheets to Excel using VBA

Adding worksheets to Excel is quite simple. For example, to add a Worksheet before the active sheet (default unless stated otherwise), name it "MyWorksheet" and have it become the active sheet, you would use code similar to below;

Sub Add_New_Worksheet()
    ' Add a new worksheet in front of the active sheet
    Worksheets.Add().Name = "MyWorksheet"
End Sub

If we want to add a new Worksheet as the last Worksheet in the active workbook and name it "MyWorksheet" we would use;

Sub Add_As_Last_Worksheet()
    ' A a named worksheet to the end
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MyWorksheet"
End Sub

The Add Method (with regards to the Worksheet Object) has an After Variant as well as an Before Variant. You can only use one of the options, either the Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet before the current active Sheet.

the example below shows how to add more then one Worksheet, the code below adds 2 new worksheets;

Sub Add_n_Worksheets()
    ' Insert 2 worksheets after the last current worksheet
    Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=2
End Sub

The last Variant available is the Type Variant. The Type specifies the sheet type. The choices available are listed below;
XlSheetType constants:

• xlWorksheet
• xlChart
• xlExcel4MacroSheet
• xlExcel4IntlMacroSheet

If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet.

Create a Microsoft Word Document with VBA

The below code is a basic example on how to create a Microsoft Word document.  This is sometimes useful if want to create a report showing your data outside of Excel.

Private Sub CreateWordDoc()
'-----------------------------------------------------------------------------
' Procedure : CreateWordDoc
' Author    : Matthew - Zypher.co.uk
' Date      : 12/04/2007
' Purpose   : Create a MS Word document using earling binding
'             Requires a reference to 'Microsoft Word ??.? Object Library'
'-----------------------------------------------------------------------------
'
    Dim objWord As Word.Application
    Dim doc As Word.Document

    'Create Word doc object
    Set objWord = CreateObject("Word.Application")

    With objWord
        ' Ensure the MS Word object is visible
        .Visible = True

        ' Add a new word document and save the file prior to adding text
        Set doc = .Documents.Add
        doc.SaveAs "C:\Your\File\Directory\Filename.doc(x)"

        ' Or open an existing document
        'Set doc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
    End With

    'Construct document
    With objWord.Selection
        ' Set the font type
        .Font.Name = "Trebuchet MS"
        ' Set the font size
        .Font.Size = 16

        ' Set the format, depending on the value of i
        For i = 1 To 50
            Select Case i
                Case Is < 10
                    ' Set the font size
                    .Font.Size = 12
                    ' Set font to bold
                    .Font.Bold = True
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphRight

                Case Is < 20
                    ' Set the font size
                    .Font.Size = 8
                    ' Turn off bold
                    .Font.Bold = False
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphLeft

                Case Is < 30
                    ' Set the font size
                    .Font.Size = 10
                    ' Turn on bold
                    .Font.Bold = True
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphRight

                Case Is < 40
                    ' Set the font size
                    .Font.Size = 6
                    ' Turn off bold
                    .Font.Bold = False
                    ' Align the text to the center
                    .ParagraphFormat.Alignment = wdAlignParagraphCenter

                Case Else
                    ' do nothing
            End Select

            ' Add text
            .TypeText "Here is an example test line, #" & i _
                & " - Font size is " & .Font.Size
            ' Move to the next line
            .TypeParagraph

        Next i

    End With

    ' Save the file
    doc.Save
    ' Bring the MS Word window to the front
    doc.Activate

End Sub

As always, if you have any questions do let us know.