Excel Hints

Results 1 to 4 of 4

Thread: Solved: Call a function from personal.xlsb

  1. #1

    Question Solved: Call a function from personal.xlsb

    Hi,
    From a workbook named Test.xlsm I am trying to call a function defined in Personal.xlsb but there is a Run-time error '424': Object required.
    Please point me out why it's wrong?

    In Test.xlsm:
    VB:
    Sub the_Main() 
        Dim arr() As String 
        arr = PERSONAL.XLSB!List_xlsx_Files() 
    End Sub 
    
    
    Formatting tags added by mark007
    In Personal.xlsb:
    VB:
    Public Function List_xlsx_Files() As String() 
        Dim Directory As String 
        Dim filename As String 
        Dim IndexSheet As Worksheet 
        Dim rw As Long 
         
         'Change the directory below as needed
        Directory = "C\ABC\" 
         'Directory = ActiveWorkbook.Path
         'Directory = FolderPath
        If Right(Directory, 1) <> "\" Then 
            Directory = Directory & "\" 
        End If 
         
        rw = 1 
         
         'Set IndexSheet = ThisWorkbook.ActiveSheet
         
        Dim temp() As String 
         
        filename = Dir(Directory & "*.xlsx") 
        Do While filename <> "" 
            rw = rw + 1 
            filename = Dir 
        Loop 
        Redim temp(rw - 2) 
         
        rw = 1 
         
        filename = Dir(Directory & "*.xlsx") 
        Do While filename <> "" 
             'IndexSheet.Cells(rw, 1).Value = filename
            temp(rw - 1) = Directory + filename 
             'Run DeleteBlankSheets(filename)
            rw = rw + 1 
            filename = Dir 
        Loop 
         
        List_xlsx_Files = temp 
         
        Set IndexSheet = Nothing 
    End Function 
    
    
    Formatting tags added by mark007
    Last edited by xld; 09-08-2009 at 12:10 AM.

  2. #2
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    VB:
     
    arr = Application.Run("Personal.xlsb!List_xlsx_Files") 
    
    
    Formatting tags added by mark007

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,065
    Location
    VB:
     
    Sub the_Main() 
        Dim arr As Variant 
        arr = Application.Run("PERSONAL.XLSB!List_xlsx_Files") 
    End Sub 
    
    
    Formatting tags added by mark007
    VB:
     
    Public Function List_xlsx_Files() As Variant 
        Dim Directory As String 
        Dim filename As String 
        Dim IndexSheet As Worksheet 
        Dim rw As Long 
        Dim temp As Variant 
         
         'Change the directory below as needed
        Directory = "C:\personal\bob\_development\Excel\Ribbon\" '"C:\ABC\"
        If Right(Directory, 1) <> "\" Then 
            Directory = Directory & "\" 
        End If 
         
        Redim temp(1 To 1000) 
        rw = 0 
        filename = Dir(Directory & "*.xlsm") 
        Do While filename <> "" 
             
            rw = rw + 1 
            temp(rw) = Directory + filename 
            filename = Dir 
        Loop 
        Redim Preserve temp(1 To rw) 
         
        List_xlsx_Files = temp 
         
        Set IndexSheet = Nothing 
    End Function 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4

Posting Permissions

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