How to call Functions

mubi_masti

New member
Joined
Oct 8, 2011
Messages
14
Reaction score
0
Points
0
I have pasted two codes from following articles of this forum but i am facing problem in executing these functions;

Function to Check if File or Directory (Folder) exists

Function To Get File Name From Specific Directory

Code:
Private Declare Function SetCurrentDirectoryA _
        Lib "kernel32" (ByVal lpPathName As String) As Long

Public Function GetOpenFilenameFrom(Optional sDirDefault As String) As Variant
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: To ask for a file at a specified directory
    Dim sDirCurrent As String
    Dim lError As Long
    'Make note of the current directory
    sDirCurrent = CurDir
    If sDirDefault = vbNullString Then
        'If optional arguement not supplied then
        'assign current directory as default
        sDirDefault = CurDir
    Else
        'If option arguement is supplied, test path to ensure
        'that it exists.  If not, assign current directory
        If Len(Dir(sDirDefault, vbDirectory)) = 0 Then
            sDirDefault = sDirCurrent
        End If
    End If
    'Change the drive and directory
    '*Drive change is unecessary if same, but takes as long to test
    ' as just changing it
    If Not Left(sDirDefault, 2) = "\\" Then
        'Not a network drive, so use ChDir
        ChDrive Left(sDirDefault, 1)
        ChDir (sDirDefault)
    Else
        'Network drive, so use API
        lError = SetCurrentDirectoryA(sDirDefault)
        If lError = 0 Then _
            MsgBox "Sorry, I encountered an error accessing the network file path"
        ChDir (sDirDefault)
    End If
    'Get the file's name & path, setting the filters to only display
    'desired types.  Help on the exact syntax can be found by looking
    'up the GetOpenFilename method in the VBA help files
    GetOpenFilenameFrom = Application.GetOpenFilename _
            ("Excel Files (*.xl*), *.xl*,All Files (*.*),*.*")
    'Change the drive and directory back
    If Not Left(sDirCurrent, 2) = "\\" Then
        'Not a network drive, so use ChDrive
        ChDrive Left(sDirCurrent, 1)
        ChDir (sDirCurrent)
    Else
        'Network drive, so use API
        lError = SetCurrentDirectoryA(sDirCurrent)
        If lError = 0 Then _
            MsgBox "Sorry, I encountered an error resetting the network file path"
        ChDir (sDirCurrent)
    End If
End Function

Code:
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function

First i am facing problem how to call these function because
one can put following code against an object like following

Code:
Private Sub GetMeAFile_Click() 
.......
end sub

but

in the article it is suggested like following

Code:
Private Sub GetMeAFile()'.....................................not possible
.....
end sub

how can i solve this issue.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there,

So what are you trying to do exactly. The sub line you gave above is the click event for an ActiveX control, such as a button. The example from the article is actually calling the routines from within other code.

Are you trying to have a button on the sheet that opens the file when you click it, or something else?
 
Top