Open and Save Files in Directory

[Daniel Jones]

I'd like to be able to select a directory and for each .xls file found
in the directory 1) open the .xls file 2) write the name and date
saved in another workbook (in this case "workbook report.xls") and 3)
save and close the workbook.

I have the shell of the loop but I'm missing the pieces where I've
written comments. A solution for this would be greatly appreciated!
Thanks.


Sub OpenAndSaveFiles()
Dim wkbk As Workbook
Dim StrWkbk As String
' open a form that allows the user to select a directory i.e. C:\temp

Workbooks("Workbook Report.xls").Worksheets("Sheet1").Range
("A1").Select
With Application.FileSearch
.NewSearch
' look in the directory selected in the form above i.e. C:\temp
.LookIn = "C:\Temp"
.SearchSubFolders = False
.Filename = ".xls"
.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
' save the filename and date saved of each found file in workbook
report.xls
wkbk.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[Daniel Jones]

I'd like to be able to select a directory and for each .xls file found
in the directory 1) open the .xls file  2) write the name and date
saved in another workbook (in this case "workbook report.xls") and 3)
save and close the workbook.

I have the shell of the loop but I'm missing the pieces where I've
written comments.  A solution for this would be greatly appreciated!
Thanks.

Sub OpenAndSaveFiles()
Dim wkbk As Workbook
Dim StrWkbk As String
' open a form that allows the user to select a directory i.e. C:\temp

Workbooks("Workbook Report.xls").Worksheets("Sheet1").Range
("A1").Select
With Application.FileSearch
  .NewSearch
' look in the directory selected in the form above i.e. C:\temp
  .LookIn = "C:\Temp"
  .SearchSubFolders = False
  .Filename = ".xls"
  .FileType = msoFileTypeExcelWorkbooks

  If .Execute() > 0 Then
   For i = 1 To .FoundFiles.Count
    ' save the filename and date saved of each found file in workbook
report.xls
     wkbk.Close SaveChanges:=False
    Next i
  Else
        MsgBox "There were no files found."
  End If
End With
End Sub

Sorry for the double-post.
1. Does anyone know how I can have this prompt the user to select a
directory instead of hardcoding w/ .LookIn = "C:\Temp" ?
2. I'm trying to save the name and date saved in "workbook
report.xls". I know to use .name for the name, but whats the property
for the date/time of the file save?

Thanks for your help in advance!

D