On Nov 24, 12:47=A0pm, Daniel Jones <95bu...@gmail.com> wrote:
> I'd like to be able to select a directory and for each .xls file found
> in the directory 1) open the .xls file =A02) 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. =A0A solution for this would be greatly appreciated!
> 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
> With Application.FileSearch
> =A0 .NewSearch
> ' look in the directory selected in the form above i.e. C:\temp
> =A0 .LookIn =3D "C:\Temp"
> =A0 .SearchSubFolders =3D False
> =A0 .Filename =3D ".xls"
> =A0 .FileType =3D msoFileTypeExcelWorkbooks
> =A0 If .Execute() > 0 Then
> =A0 =A0For i =3D 1 To .FoundFiles.Count
> =A0 =A0 ' save the filename and date saved of each found file in workbook
> =A0 =A0 =A0wkbk.Close SaveChanges:=3DFalse
> =A0 =A0 Next i
> =A0 Else
> =A0 =A0 =A0 =A0 MsgBox "There were no files found."
> =A0 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 =3D "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!