The answer is "yes", you CAN.How it's DONE is a bit more difficult.
First of all, by "all drives" you must mean all physical drives (hard
drives)since mapped network drives aren't "on" the computer they can be mapped
on the new system and nothing is lost.
So, you only have to deal with "fixed" drives.
Prior to Excel2007, VBA had a filesystem "search" utility.It was dropped in
Excel2007 and I don't believe it has been restored.
consequently, you have to use "brute force" to accomplish it.
Basically, you use the filesystem object and recursively search through
folders.Yes, it's time consuming.My c:\ drive has 450,000 files on it, and I
found that it has 2,277 Excel files.
This will list the Excel files.Next, you have to decide what you wish to DO
with them!(I'd write a function and call it from the section that writes the
filenames to the worksheet)
Option Explicit
'err.number & ": " & err.description
Public fso, ListRow, RepSheet
Sub List_Drives()
Dim d, dc, stat, File_Cnt
Set fso = CreateObject("scripting.filesystemobject")
Set dc = fso.drives
RepSheet = "All_Files"
stat = Clear_Report
For Each d In dc
If (d.drivetype = 2) Then File_Cnt = Get_Folders(d.Path)
Next d
Application.StatusBar = False
MsgBox File_Cnt & " files found"
End Sub
'==================================================================================
Function Get_Folders(FolderName)
Dim File, Files, f, fil
Dim Folders, Folder, Ext
Dim File_Cnt, ArrFolder
ArrFolder = Split(FolderName, "\")
If (UBound(ArrFolder) = 3) Then
Application.StatusBar = "Accessing folder: " & FolderName
Debug.Assert True
End If
If (Right(FolderName, 1) <> "\") Then FolderName = FolderName & "\"
On Error Resume Next
Set Folder = fso.getfolder(FolderName)
Set Files = Folder.Files
For Each File In Files
Set f = fso.getfile(File.Path)
Ext = fso.getextensionname(File.Name)
If (Left(UCase(Ext), 3) = "XLS") Then
File_Cnt = File_Cnt + 1
ListRow = ListRow + 1
If (ListRow Mod 10 = 0) Then Debug.Assert True
Sheets(RepSheet).Cells(ListRow, "A").Value = f.Name
Sheets(RepSheet).Cells(ListRow, "B").Value = f.Path
Sheets(RepSheet).Cells(ListRow, "C").Value = f.Size
Sheets(RepSheet).Cells(ListRow, "D").Value = f.datecreated
Sheets(RepSheet).Cells(ListRow, "E").Value = f.datelastmodified
End If
Next File
'--------------------------------------------------------
Set Folders = Folder.subfolders
For Each Folder In Folders
File_Cnt = File_Cnt + Get_Folders(Folder.Path)
Next Folder
'--------------------------------------------------------
On Error GoTo 0
Get_Folders = File_Cnt
End Function
Function Clear_Report()
Sheets(RepSheet).Range("A2:Z1000000").ClearContents
Sheets(RepSheet).Range("A1").Value = "FileName"
Sheets(RepSheet).Range("B1").Value = "Path"
Sheets(RepSheet).Range("C1").Value = "Size"
Sheets(RepSheet).Range("D1").Value = "Date Created"
Sheets(RepSheet).Range("E1").Value = "Date Modified" ListRow = 1
End Function
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Pankaj Michael <pankaji...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wednesday, October 7, 2015 10:58 PM
Subject: $$Excel-Macros$$ Select all excel files in computer
Hi to ALL,
Can we search ALL excel extension files through vba in a computer(All Drives)
through VBA? Transfer to any other location. Priority is excel
I want to shift all excel files from one computer to another because company is
replacing systems and only ms office files needs to transfer as per policy.
Please help
--
ThanksPankaj Kumar9910075248--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
