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

    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 (www.excelguru.ca)
    '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 (www.excelguru.ca)
    '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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
  •