Open and Save Files in Directory

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
0
Daniel
11/24/2009 5:47:56 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
773 Views

Similar Articles

[PageSpeed] 20

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!
> 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
> =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
> report.xls
> =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!

D
0
Daniel
11/25/2009 8:20:10 PM
Reply:

Similar Artilces: