PDA

View Full Version : Copy Filenames from Folder



PSL
04-15-2009, 01:12 AM
http://img7.imageshack.us/img7/470/62090697.jpg

Been trying to work on a macro. Seems quite self explainatory from the pic. Have partially got a part of it to work. Here's what I intend to do:-

1. Copy all sheet names from folder path mentioned in A1
2. Open all the Workbooks in the source folder in Hidden mode.

Thanks in advance

GTO
04-15-2009, 01:15 AM
Greetings PSL,

Could you tack in the code you have thus far? Use the little green/white VBA button at the top of the message window and paste the code between the tags.

Mark

JONvdHeyden
04-15-2009, 01:32 AM
Hello PSL

You could try this:


Sub Test()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim fso As Object, fld As Object, fil As Object, fldPath As String, i As Long
fldPath = Range("A1")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
i = 1
On Error GoTo ErrHandler
For Each fil In fld.Files
Cells(i, 2) = fil.Name
i = i + 1
Workbooks.Open fil
ActiveWindow.Visible = False
Next fil
ErrHandler:
Application.EnableEvents = True
End Sub

PSL
04-15-2009, 01:37 AM
This is what im using to open the files

Sub Allfiles()

Dim i As Integer, wb As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\New Folder"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Next i
End With

End Sub

JONvdHeyden
04-15-2009, 03:02 AM
Looks like you just need to add:

ActiveWindow.Visible = False

...within your for loop

PSL
04-15-2009, 07:37 AM
Looks like you just need to add:

ActiveWindow.Visible = False

...within your for loop

Thanks!.. I was able to open the files, but was having trouble getting their names on the sheet!..

Your code works perfectly.

Thanks again!