Paul,
I have been reading your codes since 2011. How do you manage to write these
codes in so simple manner? Is there any logic? Please guide.
Mangesh
On Oct 8, 2015 6:45 PM, "Paul Schreiner" <schreiner_p...@att.net> wrote:
> 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
>
>
>
> --
>
> Thanks
> Pankaj Kumar
> 9910075248
> --
> 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.
>
--
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.
