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:
    [vba]
    Sub the_Main()
    Dim arr() As String
    arr = PERSONAL.XLSB!List_xlsx_Files()
    End Sub
    [/vba]

    In Personal.xlsb:
    [vba]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
    [/vba]
    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
    [VBA]
    arr = Application.Run("Personal.xlsb!List_xlsx_Files")
    [/VBA]

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,094
    Location
    [vba]

    Sub the_Main()
    Dim arr As Variant
    arr = Application.Run("PERSONAL.XLSB!List_xlsx_Files")
    End Sub
    [/vba]

    [vba]

    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
    [/vba]
    ____________________________________________
    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
  •