cuccu
09-07-2009, 09:32 PM
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:
Sub the_Main()
Dim arr() As String
arr = PERSONAL.XLSB!List_xlsx_Files()
End Sub
In Personal.xlsb:
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
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:
Sub the_Main()
Dim arr() As String
arr = PERSONAL.XLSB!List_xlsx_Files()
End Sub
In Personal.xlsb:
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