tim64,

You/me are passing a Variant to a function that requires a String.
Excel won't let you convert variants to another data type,
unless they are passed ByVal.

Change the function's first line from...
Function latest_folder(ByRef strPath As String) As String
To...
Function latest_folder(ByVal strPath As String) As String
or
Change the data type of temp3 from Variant to String.
'--------
Curious, why this...FileFormat:=xlExcel7

Jim Cone
San Francisco, USA



"tim64" wrote in
message ...

jim, I use excel 2003 so that can't be the problem.
the code you sent to me got an error (see below)

Sub ListFilesInFolder()
Dim teMp, temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8,
temp9, temp10, FILE_PATH As Variant
Dim c As Integer
Dim t1(20) As Variant
Application.DisplayAlerts = False
Dim path As Variant
path = PickFolder("C:\") & "\"
t = Dir(path & "*detail*.htm")
Dim c1 As Integer
While t < ""
t1(c1) = t
t = Dir()
c1 = c1 + 1
Wend
c = 0
For i = 0 To 20
If t1(i) = "" Then
GoTo a:
End If
If c = 0 Then
temp3 = path
temp4 = Split(temp3, "\")
temp5 = temp4(UBound(temp4) - 1)
temp6 = latest_folder(temp3)
<------------------------------------------ ByRef argument type

-snip-

Function LatestFolder(ByRef strPath As String) As String
'Jim Cone - San Francisco, USA - June 29, 2005
'Requires a project reference to the "Microsoft Scripting Runtime" library.
'Displays the latest folder name in the strPath folder,
'if the folder name contains the strPath folder name.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objSubFold As Scripting.Folder
Dim strParentName As String
Dim strLatest As String
Dim varDate As Variant

Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
strParentName = objFolder.Name
strLatest = "No folders"

For Each objSubFold In objFolder.SubFolders
If InStr(1, objSubFold.Name, strParentName, vbTextCompare) 0 Then
If objSubFold.DateLastModified varDate Then
varDate = objSubFold.DateLastModified
strLatest = objSubFold.Name
End If
End If
Next 'objFile

LatestFolder = strLatest & " - " & varDate

Set objFSO = Nothing
Set objFolder = Nothing
Set objSubFold = Nothing
End Function
'------------------------