|
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. |
Macro won't run from d: drive, but works on c: drive
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. |
Macro won't run from d: drive, but works on c: drive
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. |
Macro won't run from d: drive, but works on c: drive
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. |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
Macro won't run from d: drive, but works on c: drive
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 |
| All times are GMT +1. The time now is 04:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com