I have a macro that looks for pdf files from within the directory where the
active excel file is. It works when the excel file is on the desktop, but it
doesn't work when moved to the d: drive.

I've tried it on another computer and it works where ever I put the excel
file.

Is there a setting that I need to fix in order for it to run.

GFN.

GFN,
Is D: a CD ?
Also "it doesn't work" means what ?

NickHK

"GFN" wrote in message
...
I have a macro that looks for pdf files from within the directory where
the
active excel file is. It works when the excel file is on the desktop, but
it
doesn't work when moved to the d: drive.

I've tried it on another computer and it works where ever I put the excel
file.

Is there a setting that I need to fix in order for it to run.

GFN.

No, it's a hard drive.

"NickHK" wrote:

GFN,
Is D: a CD ?
Also "it doesn't work" means what ?

NickHK

"GFN" wrote in message
...
I have a macro that looks for pdf files from within the directory where
the
active excel file is. It works when the excel file is on the desktop, but
it
doesn't work when moved to the d: drive.

I've tried it on another computer and it works where ever I put the excel
file.

Is there a setting that I need to fix in order for it to run.

GFN.

GFN,
OK, but what does "it doesn't work" mean ?
A few more details would help.

NickHK

"GFN" wrote in message
...
No, it's a hard drive.

"NickHK" wrote:

GFN,
Is D: a CD ?
Also "it doesn't work" means what ?

NickHK

"GFN" wrote in message
...
I have a macro that looks for pdf files from within the directory
where
the
active excel file is. It works when the excel file is on the desktop,
but
it
doesn't work when moved to the d: drive.

I've tried it on another computer and it works where ever I put the
excel
file.

Is there a setting that I need to fix in order for it to run.

GFN.

Post your code please..

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=39114

The code is below. I am having problems when it gets to the .filesearch
part. It is now writing the headings and the last part that tells the number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of " &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1 Step -1
If Mid(CurrentEntry, j, 1) = Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0, vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=391143

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the .filesearch
part. It is now writing the headings and the last part that tells the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of " &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1 Step -1
If Mid(CurrentEntry, j, 1) = Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0, vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak
------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

It works fine when the excel file is located on the c: drive. But it doesn't
list the files when the file is located on my d: drive (second hard drive).
Is there something going on with the macro security setting in some other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the .filesearch
part. It is now writing the headings and the last part that tells the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of " &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1 Step -1
If Mid(CurrentEntry, j, 1) = Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0, vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak
------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive. But it
doesn't
list the files when the file is located on my d: drive (second hard
drive).
Is there something going on with the macro security setting in some other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the
..filesearch
part. It is now writing the headings and the last part that tells the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of " &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1 Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak

------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

I guess .foundfiles.count is returning a zero.

With windows explorer the files are displayed okay.

There are no errors.



"NickHK" wrote:

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive. But it
doesn't
list the files when the file is located on my d: drive (second hard
drive).
Is there something going on with the macro security setting in some other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the
..filesearch
part. It is now writing the headings and the last part that tells the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of " &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1 Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak

------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

GFN,
Just because your workbook is opened from the D: drive, it does not mean the
CurDir="D:".
What is the value of CurDir ?

NickHk

"GFN" wrote in message
...
I guess .foundfiles.count is returning a zero.

With windows explorer the files are displayed okay.

There are no errors.



"NickHK" wrote:

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive. But it
doesn't
list the files when the file is located on my d: drive (second hard
drive).
Is there something going on with the macro security setting in some
other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire
Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the
..filesearch
part. It is now writing the headings and the last part that tells
the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of
" &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into
row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1
Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak


------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

the last output of the code shows the correct directory.

d:

"NickHK" wrote:

GFN,
Just because your workbook is opened from the D: drive, it does not mean the
CurDir="D:".
What is the value of CurDir ?

NickHk

"GFN" wrote in message
...
I guess .foundfiles.count is returning a zero.

With windows explorer the files are displayed okay.

There are no errors.



"NickHK" wrote:

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive. But it
doesn't
list the files when the file is located on my d: drive (second hard
drive).
Is there something going on with the macro security setting in some
other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire
Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the
..filesearch
part. It is now writing the headings and the last part that tells
the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) & " of
" &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path into
row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1
Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j + 1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess, 0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak


------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

GFN,
What about CurDir("D") ?
What if you use the Dir(D:\MyFolder\*.pdf) instead of the FileSearch?
What about .LookIn="D:"

I can't see any reason other than you are looking in the wrong place or
there are no .pdf files.

NickHK

"GFN" wrote in message
...
the last output of the code shows the correct directory.

d:

"NickHK" wrote:

GFN,
Just because your workbook is opened from the D: drive, it does not mean
the
CurDir="D:".
What is the value of CurDir ?

NickHk

"GFN" wrote in message
...
I guess .foundfiles.count is returning a zero.

With windows explorer the files are displayed okay.

There are no errors.



"NickHK" wrote:

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive. But
it
doesn't
list the files when the file is located on my d: drive (second
hard
drive).
Is there something going on with the macro security setting in
some
other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire
Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the
..filesearch
part. It is now writing the headings and the last part that
tells
the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) &
" of
" &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path
into
row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To 1
Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j +
1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess,
0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak



------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

Hi Nick,

I can't see any reason other than you are looking in the wrong place or
there are no .pdf files.


There have been many reports of problems with filesearch.

See, for example:

http://tinyurl.com/azx42


---
Regards,
Norman



"NickHK" wrote in message
...
GFN,
What about CurDir("D") ?
What if you use the Dir(D:\MyFolder\*.pdf) instead of the FileSearch?
What about .LookIn="D:"

I can't see any reason other than you are looking in the wrong place or
there are no .pdf files.

NickHK

"GFN" wrote in message
...
the last output of the code shows the correct directory.

d:

"NickHK" wrote:

GFN,
Just because your workbook is opened from the D: drive, it does not
mean
the
CurDir="D:".
What is the value of CurDir ?

NickHk

"GFN" wrote in message
...
I guess .foundfiles.count is returning a zero.

With windows explorer the files are displayed okay.

There are no errors.



"NickHK" wrote:

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive.
But
it
doesn't
list the files when the file is located on my d: drive (second
hard
drive).
Is there something going on with the macro security setting in
some
other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check ToolsOptionsRequire
Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to the
..filesearch
part. It is now writing the headings and the last part that
tells
the
number
of files found, but it's not doing the .filesearch function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i) &
" of
" &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and path
into
row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry) To
1
Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j +
1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " & FormatNumber(FilesToProcess,
0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak



------------------------------------------------------------------------
Kaak's Profile:
http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143

Norman,
I can't say that I use the .FileSearch in Office. If I need such
capabilities, I use a recursive VB/Dir routine that never has these
problems.

NickHK

"Norman Jones" wrote in message
...
Hi Nick,

I can't see any reason other than you are looking in the wrong place or
there are no .pdf files.


There have been many reports of problems with filesearch.

See, for example:

http://tinyurl.com/azx42


---
Regards,
Norman



"NickHK" wrote in message
...
GFN,
What about CurDir("D") ?
What if you use the Dir(D:\MyFolder\*.pdf) instead of the FileSearch?
What about .LookIn="D:"

I can't see any reason other than you are looking in the wrong place or
there are no .pdf files.

NickHK

"GFN" wrote in message
...
the last output of the code shows the correct directory.

d:

"NickHK" wrote:

GFN,
Just because your workbook is opened from the D: drive, it does not
mean
the
CurDir="D:".
What is the value of CurDir ?

NickHk

"GFN" wrote in message
...
I guess .foundfiles.count is returning a zero.

With windows explorer the files are displayed okay.

There are no errors.



"NickHK" wrote:

GFN,
What do you get, an error or .FoundFiles.Count=0 ?
Can you see the .pdf in Explorer ?

NickHK

"GFN" wrote in message
...
It works fine when the excel file is located on the c: drive.
But
it
doesn't
list the files when the file is located on my d: drive (second
hard
drive).
Is there something going on with the macro security setting in
some
other
place than excel?



"NickHK" wrote:

GFN,
Code works for me.
It would help if you declared your variables.
Or better force it Option Explicit. Check
ToolsOptionsRequire
Variable
Declaration.

NickHK

"GFN" wrote in message
...
The code is below. I am having problems when it gets to
the
..filesearch
part. It is now writing the headings and the last part
that
tells
the
number
of files found, but it's not doing the .filesearch
function.

Sub ListPDFs()
'This Macro lists the pdf files in the current directory
'including the subdirectories

'Clear out all cell in the sheet
Cells.Clear

'Add headings
Worksheets("Directory").Activate
Cells.Clear
With ActiveSheet.Range("A1:d1")
.Value = Array("Filename", "Size", "Date/Time", "Path")
.Font.Bold = True
End With
test = CurDir
'Search for pdf files in current directory
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = True
.Filename = "*.pdf"
.Execute

NumberOfFiles = .FoundFiles.Count
Row = 1
Application.ScreenUpdating = True
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Working on " & (i)
&
" of
" &
NumberOfFiles
Row = Row + 1
'Puts file names, sizes, and dates, and
path
into
row
CurrentEntry = .FoundFiles(i)
For j = Len(CurrentEntry)
To
1
Step -1
If Mid(CurrentEntry, j, 1) =
Application.PathSeparator
Then
Cells(Row, 1) = Mid(CurrentEntry, j
+
1)
Exit For
End If
Next j
Cells(Row, 2) = FileLen(CurrentEntry)
Cells(Row, 3) = FileDateTime(CurrentEntry)
Cells(Row, 4) = CurrentEntry
On Error Resume Next
Next i

End With
ActiveSheet.UsedRange.Name = "FileList"
With Cells(Row + 2, 1)
.Value = "There are " &
FormatNumber(FilesToProcess,
0,
vbFalse,
vbFalse, vbTrue) & " PDF files in the current directory."
.Font.Bold = True
End With
Cells(Row + 3, 1) = test

Application.StatusBar = False
End Sub



"Kaak" wrote:


Post your code please...


--
Kaak




------------------------------------------------------------------------
Kaak's Profile:

http://www.excelforum.com/member.php...fo&userid=7513
View this thread:
http://www.excelforum.com/showthread...hreadid=391143