Awesome!
You are doing a great job for the learners like us.
We all believe in the process of KT i.e. knowledge Transfer. But, I think
you are the best definition of this term and process.
Thanks a ton, Paul for all your codes and salute to your efforts and
intention to help us always....!!!
Mangesh
On Oct 8, 2015 11:09 PM, "Paul Schreiner" <schreiner_p...@att.net> wrote:
> Thanks for your kind words!
>
> To be honest, it's mostly just experience, and "need".
>
> I started with writing programs (C+) and UNIX scripts in the mid 1980's.
>
> I currently maintain over 170,000 lines of VBA code for applications that
> manage over 1,000,000 manufacturing documents and create reports from the
> data contained in them.
>
> so, when this question came up, I knew I had some code from several years
> ago.
> (it was last updated in February of 2013)
> So, I just grabbed it and modified it a bit to fit the question.
>
> It's a collection of macros I use to maintain archives. When new versions
> of documents are created, the old ones get moved to an "archive" folder.
> Periodically I run this macro that takes the archive folder and breaks up
> the files into set not exceeding 700mb so that I can move them to CD's for
> long-term storage. (I keep a copy on an external backup for easy access)
> Currently, my archive folder has 12,000 files in 1,100 folders requiring
> 2.2 Gb. Running the macro, in less than 2 minutes the files will be neatly
> separated into separate 700Mb folders.
>
> As far as logic goes: I think it helps that I learned programming in an
> era that had limited storage. My first programming class used punch cards.
> We only were allowed to use the "terminals" once our programs compiled
> successfully!
>
> I learned early on that the most TIME CONSUMING part of any program is
> READING, WRITING and updating the display.
>
> So, I try whenever possible to NOT manipulate data within the cells.
> but instead read the entire dataset into memory, do all my manipulations
> and calculations, then write it out and refresh the display at the end.
>
> The other thing that adds to the "experience" is groups like this.
> If my only experience was through my job, how many opportunities would I
> have to run across date arithmetic problems? cost calculations?
>
> Instead, while I'm waiting for some of my longer programs to run (or
> during lunch) I'll look through the user groups for things that look
> "challenging".
> Or something I can throw together quickly.
> sometimes, I just answer posts because I think I can do it better/faster!
> (lol)
>
>
> With these user groups. "Communication is Key".
> When I was in college, I could remember nearly all the formulas in
> Calculus, but what I couldn't do was figure out when to use them!
>
> With the posts in user groups, there's just no convenient way we can know
> all the background behind the NEED for the questions being asked.
> So we have to make assumptions.
> If the person asking the question doesn't recognize when we made an
> assumption, he may not recognize when the assumption isn't correct.
> So I try to explain WHY I selected a specific approach.
>
> Speaking of which... back to work!
>
>
>
> *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:* Mangesh Vimay <mangesh.da...@gmail.com>
> *To:* excel-macros@googlegroups.com
> *Sent:* Thursday, October 8, 2015 12:31 PM
> *Subject:* Re: $$Excel-Macros$$ Select all excel files in computer
>
> 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.
>
>
> --
> 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.
