Split Multiple workbook in a folder into separate worksheet and rename the file name with Date

[DocAElstein]

What does this do?

Sub MyFileListAtMyFolder() ' 
Dim myPathAndFiles As String
 Let myPathAndFiles = "D:\test\*.xls*" ' 
Dim StrDirBack As String
 Let StrDirBack = Dir(myPathAndFiles, vbNormal) ' Initial file search for all Excel files - * is a wildcard, so any file like  MyFile.xlsx  or BN.xls    etc. will meet the search criteria - the file need only have  .xls  in it
    Do While Not StrDirBack = "" ' Dir will return "" if no next file is found
    Dim MyFilesList As String
     Let MyFilesList = MyFilesList & vbCr & vbLf & StrDirBack ' add a line and the next found file name to our file list
     Let StrDirBack = Dir ' If Dir is used with no arguments, then it looks for the next file with the previous serach criteria
    Loop '
 MsgBox Prompt:=MyFilesList, Title:="My Files from  " & myPathAndFiles
End Sub

Does it list all the files you need?

---

I need to know
_1)
either
The tab name of the summary sheet
or
The tab number, ( counting from the left )

_2) what name should the saved file have

[sudharsan.r]

Hi Doc,
Thanks for the quick reply.
The Sheet name in all the workbook is named as "DFC". And the filename of the each file should be "DFC_Workbookname"(workbook name has date in it).
The output files can be stored in "D:\output" .


Thanks,
San R








[QUOTE=DocAElstein;13115]What does this do?

Sub MyFileListAtMyFolder() ' 
Dim myPathAndFiles As String
 Let myPathAndFiles = "D:\test\*.xls*" ' 
Dim StrDirBack As String
 Let StrDirBack = Dir(myPathAndFiles, vbNormal) ' Initial file search for all Excel files - * is a wildcard, so any file like  MyFile.xlsx  or BN.xls    etc. will meet the search criteria - the file need only have  .xls  in it
    Do While Not StrDirBack = "" ' Dir will return "" if no next file is found
    Dim MyFilesList As String
     Let MyFilesList = MyFilesList & vbCr & vbLf & StrDirBack ' add a line and the next found file name to our file list
     Let StrDirBack = Dir ' If Dir is used with no arguments, then it looks for the next file with the previous serach criteria
    Loop '
 MsgBox Prompt:=MyFilesList, Title:="My Files from  " & myPathAndFiles
End Sub

Does it list all the files you need?

---

I need to know
_1)
either
The tab name of the summary sheet
or
The tab number, ( counting from the left )

_2) what name should the saved file have

[DocAElstein]

Hi San R
Thanks for the info

---

Also please, try this macro, and tell me what happens for you:

What does this do?

Sub MyFileListAtMyFolder() ' 
Dim myPathAndFiles As String
 Let myPathAndFiles = "D:\test\*.xls*" ' 
Dim StrDirBack As String
 Let StrDirBack = Dir(myPathAndFiles, vbNormal) ' Initial file search for all Excel files - * is a wildcard, so any file like  MyFile.xlsx  or BN.xls    etc. will meet the search criteria - the file need only have  .xls  in it
    Do While Not StrDirBack = "" ' Dir will return "" if no next file is found
    Dim MyFilesList As String
     Let MyFilesList = MyFilesList & vbCr & vbLf & StrDirBack ' add a line and the next found file name to our file list
     Let StrDirBack = Dir ' If Dir is used with no arguments, then it looks for the next file with the previous serach criteria
    Loop '
 MsgBox Prompt:=MyFilesList, Title:="My Files from  " & myPathAndFiles
End Sub

Does it list all the files you need?

Alan