Re: $$Excel-Macros$$ Select all excel files in computer

[Pankaj]

Thanks Paul for your help

On Friday, October 9, 2015 at 12:43:31 AM UTC+5:30, Paul Schreiner wrote:
>
> the problem with that approach is that it would not maintain the original 
> folders.
> It would move them to a SINGLE folder.
>
> I strongly suspect that is not what is desired. 
> *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:* Swapnil Palande <palande@gmail.com >
> *To:* excel-...@googlegroups.com  
> *Sent:* Thursday, October 8, 2015 2:07 PM
> *Subject:* Re: $$Excel-Macros$$ Select all excel files in computer
>
> Hi,
> Instead of macro do it manually, it will be faster.  use search 
> functionality of windows... Search for *.xls and *.xlsx and then move those 
> files to destination folder. 
> Regards, 
> Swapnil Suresh Palande 
>
>
> On 08-Oct-2015 8:28 am, "Pankaj Michael" <panka...@gmail.com > 
> wrote:
>
>
> 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...@googlegroups.com .
> To post to this group, send email to excel-...@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...@googlegroups.com .
> To post to this group, send email to excel-...@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 b

Re: $$Excel-Macros$$ Select all excel files in computer

[Paul Schreiner]

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:Z100").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

Re: $$Excel-Macros$$ Select all excel files in computer

[Mangesh Vimay]

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:Z100").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
>

Re: $$Excel-Macros$$ Select all excel files in computer

[Swapnil Palande]

Hi,

Instead of macro do it manually, it will be faster.  use search
functionality of windows... Search for *.xls and *.xlsx and then move those
files to destination folder.

Regards,
Swapnil Suresh Palande
On 08-Oct-2015 8:28 am, "Pankaj Michael"  wrote:

>
> 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.

Re: $$Excel-Macros$$ Select all excel files in computer

[Paul Schreiner]

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
'===

Re: $$Excel-Macros$$ Select all excel files in computer

[Paul Schreiner]

the problem with that approach is that it would not maintain the original 
folders.It would move them to a SINGLE folder.
I strongly suspect that is not what is desired. 
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: Swapnil Palande <palande.swapni...@gmail.com>
 To: excel-macros@googlegroups.com 
 Sent: Thursday, October 8, 2015 2:07 PM
 Subject: Re: $$Excel-Macros$$ Select all excel files in computer
   
Hi,Instead of macro do it manually, it will be faster.  use search 
functionality of windows... Search for *.xls and *.xlsx and then move those 
files to destination folder. Regards, 
Swapnil Suresh Palande 

On 08-Oct-2015 8:28 am, "Pankaj Michael" <pankaji...@gmail.com> wrote:


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.


   

-- 
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.c

Re: $$Excel-Macros$$ Select all excel files in computer

[Mangesh Vimay]

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.
>
> Basica

$$Excel-Macros$$ Select all excel files in computer

[Pankaj Michael]

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.