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