VBA for financial professionals : API

VbaFin.com

Visual Basic for Financial Professionals

Home

API Example2

Sometimes you might need to change the directory without knowing the name of the drive it is mapped to. In this case ChDrive and ChDir could not be used. To achieve the objective you would have to use SetCurrentDirectory API which in itself is quite simple. Here's an example:

Declare Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long

Sub RunMyProgram()

SetCurrentDirectory "\\YOUR-2009FC1\My Documents\VbaFin.com"

'Continue code below

End Sub

Another useful API function - CoRegisterMessageFilter can be employed to suppress any messages coming from an application we have passed the control to. For example let's say we want to open an Excel file and run a macro that typically takes some time and there's a risk that Excel will display the message "Excel is waiting for another application to complete an OLE action" and cause your process to hang and not finish. Here's the example:

Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" _

(ByVal lFilterIn As Long, ByRef lPreviousFilter) As Long

Sub RunMyProgram()

Dim xlApp As Excel.Application, lMsgFilter As Long

Const xlPath = "C:\Processes\"

'Remove the message filter before passing the control to the Excel Application below

CoRegisterMessageFilter 0&, lMsgFilter
'Continue code below

Set xlApp = New Excel.Application
With xlApp

        .DisplayAlerts = False
        .Workbooks.Open xlPath "xlFile.xls", 0, True
        .Run "xlFile.xls!xlProcess"

While .Workbooks.Count

.ActiveWorkbook.Close False

Wend

.Quit

    End With
    'Restore the message filter
    CoRegisterMessageFilter lMsgFilter, lMsgFilter

End Sub