Macro won't run from d: drive, but works on c: drive

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> No, it's a hard drive.
>
> "NickHK" wrote:
>
> > GFN,
> > Is D: a CD ?
> > Also "it doesn't work" means what ?
> >
> > NickHK
> >
> > "GFN" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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...

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 Tools>Options>Require Variable
Declaration.

NickHK

"GFN" <[email protected]> wrote in message
news:[email protected]...
> 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 Tools>Options>Require Variable
> Declaration.
>
> NickHK
>
> "GFN" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 Tools>Options>Require Variable
> > Declaration.
> >
> > NickHK
> >
> > "GFN" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Tools>Options>Require Variable
> > > Declaration.
> > >
> > > NickHK
> > >
> > > "GFN" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Tools>Options>Require
Variable
> > > > Declaration.
> > > >
> > > > NickHK
> > > >
> > > > "GFN" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 Tools>Options>Require
> Variable
> > > > > Declaration.
> > > > >
> > > > > NickHK
> > > > >
> > > > > "GFN" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 Tools>Options>Require
> > Variable
> > > > > > Declaration.
> > > > > >
> > > > > > NickHK
> > > > > >
> > > > > > "GFN" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > > 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 Tools>Options>Require
>> > Variable
>> > > > > > Declaration.
>> > > > > >
>> > > > > > NickHK
>> > > > > >
>> > > > > > "GFN" <[email protected]> wrote in message
>> > > > > > news:[email protected]...
>> > > > > > > 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" <[email protected]> wrote in message
news:OLnBkQ#[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > 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" <[email protected]> wrote in message
> >> > > > news:[email protected]...
> >> > > > > 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
Tools>Options>Require
> >> > Variable
> >> > > > > > Declaration.
> >> > > > > >
> >> > > > > > NickHK
> >> > > > > >
> >> > > > > > "GFN" <[email protected]> wrote in message
> >> > > > > > news:[email protected]...
> >> > > > > > > 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
> >> > > > > > > >
> >> > > > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > > >
> >> > > >
> >> > > >
> >> > > >
> >> >
> >> >
> >> >
> >
> >
>
>