Results 1 to 2 of 2

Thread: How to call Functions

  1. #1

    How to call Functions

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Private Declare Function SetCurrentDirectoryA _
            Lib "kernel32" (ByVal lpPathName As String) As Long
    Public Function GetOpenFilenameFrom(Optional sDirDefault As String) As Variant
    'Author       : Ken Puls (
    '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
            '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)
            '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)
            '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
    Public Function FileFolderExists(strFullPath As String) As Boolean
    'Author       : Ken Puls (
    'Macro Purpose: Check if a file or folder exists
        On Error GoTo EarlyExit
        If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
        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

    Private Sub GetMeAFile_Click() 
    end sub

    in the article it is suggested like following

    Private Sub GetMeAFile()'.....................................not possible
    end sub
    how can i solve this issue.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts