PDA

View Full Version : Append File Names to an Existing List of File Names



icodehelp
05-22-2008, 08:46 PM
I have a macro that searches a given directory for files that contain "36.xls" in the name and copiesthe directory location of the file to a sheet called "Summary" of the macro workbook.

This is that part I need help with:
When new files containing "36.xls" are added to that directory, I want to be able to rerun the macro and have only new filenames add to the summary sheet of the workbook at the end of the existing list. This is to save time so it doesn't have to write each file name over again.

I also want alphabetically sort that list after all the new files have been added. I have attached the code in this thread.

Any ideas? :help

Bob Phillips
05-23-2008, 09:26 AM
I woul just append all the names irregardless, sort them, then delete duplicates.

icodehelp
05-23-2008, 09:49 AM
I really don't want to add back all the names because it takes a long time. There are many files and the server is slow. I think just rerunning the wholemacro would be faster than adding all of the names again and deleting the duplicates.

Anyone else have suggestions?

malik641
05-23-2008, 10:41 AM
Just an FYI, you don't need to sort the files with Bubble Sort. The .Execute() method has a SortBy and SortOrder option. Look it up in the help files. That should speed up your code some.

malik641
05-23-2008, 11:14 AM
Compare this code with your own. I don't know what you mean by it runs slow except for the fact that the user ALWAYS has to choose a SearchString and folder, besides that it's pretty fast (I tested with 76 files that had "36.xls" in it. Just find the files and paste it in there, overwriting

Note: mine uses a sheet called "Data" to paste the values into.
Sub DataMiner()
Dim fLdr As String, SearchString As String
Dim fs As FileSearch
Dim i As Long
Dim Filenames() As String

ReDim Filenames(0)

'command to get the directory
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
fLdr = .SelectedItems(1)
End With
' nothing selected
If fLdr = vbNullString Then Exit Sub

' TESTING ONLY
SearchString = "*36.xls"

Application.StatusBar = "Searching for " & SearchString & " in " & fLdr
Set fs = Application.FileSearch
With fs
.LookIn = fLdr
.SearchSubFolders = False
.Filename = SearchString
.FileType = msoFileTypeExcelWorkbooks
If .Execute(msoSortByFileName, msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
ReDim Preserve Filenames(i - 1)
Filenames(i - 1) = .FoundFiles(i)
Next i
End If
End With

With Worksheets("Data")
.Range(.Cells(1, 1), .Cells(UBound(Filenames) + 1, 1)) = WorksheetFunction.Transpose(Filenames)
End With

Application.StatusBar = vbNullString
End Sub


....Now that I look at it, this code is probably killing you because it would be so slow...
' For i = 1 To NumFiles
Application.StatusBar = "File " & i & " of " & NumFiles
Sheets(1).Activate
Application.ScreenUpdating = True
Range("A1").Select
Application.ScreenUpdating = False

'Search to see if this file has already been downloaded
CurrentFileFound = False
j = 1
Do
If ActiveCell.Offset(j, 0) = Filenames(i) Then
CurrentFileFound = True
ActiveCell = ActiveCell - 1
NumFilesFound = NumFilesFound + 1
End If
j = j + 1
Loop Until ActiveCell.Offset(j, 0) = "" Or CurrentFileFound = True

If CurrentFileFound = False Then
ActiveCell.Offset(i, 0) = Filenames(i)
Workbooks.Open (Filenames(i))
CurrentWorkbook = ActiveWorkbook.Name
Workbooks(CurrentWorkbook).Activate
If i + StartNumFiles = 1 Then

Bob Phillips
05-23-2008, 04:08 PM
I really don't want to add back all the names because it takes a long time. There are many files and the server is slow. I think just rerunning the wholemacro would be faster than adding all of the names again and deleting the duplicates.

Anyone else have suggestions?

Surely it is the identifying the files that takes the time, and that is unavoidable whether they are already there or not. Adding them to the list should be instantaneous.