VBA Application.Filesearch workaround

[eeder1]

I REALLY need a fix for the application.filesearch function that is currently in some old code which works in Excel 2003 and before but not in Excel 2007. I have heard suggestions on fixing this by using the Dir function but with my limited knowledge and no time I need expertise...PLEASE HELP!!!!



Call GetBrowse 'goes to a sub that allows you to pick a file with multiple excel files...after running through it jumps back to the Application.Filesearch below and gets stuck due to it being removed from 2007
With Application.FileSearch
.NewSearch
.LookIn = strPath
.Filename = "*.xls"
If .Execute() > 0 Then
Workbooks.Add
WrWrkBk = ActiveWorkbook.Name
x = Workbooks(WrWrkBk).Worksheets.Count

sort = 8
For i = 1 To counter - 1
Application.ScreenUpdating = False
strfile = .FoundFiles(i)


Find_Last_Slash (strfile)
strfile = Mid(strfile, 1, position)
a = a + 1
strDiv = Workbooks(RdWrkBk1).Sheets(1).Cells(a, sort).Value
strfile = strfile & strDiv & ".xls"

Workbooks.Open strfile 'open workbook

Find_Last_Slash (strfile)
RdWrkBk = Trim(Mid(strfile, position + 1, 50))

 

[eeder1]

Wow that was a fast response!....

Notes below as this is quite a bit more compact than the old code

Sub test()
Dim myFolder As String, fn As String, wb As Workbook
Dim mySheet As String, myRange As String, r As Range
Dim myRows As Long, myCols As Long, n As Long
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
myFolder = .SelectedItems(1) 'before I was selecting a folder with all the files in it seems to work like the old code as I choose from drop down list...ok so far then
Else
Exit Sub
End If
End With
myFolder = myFolder & "\"
mySheet = "Unit Waterfalls" '? Alter here (common sheet name)
myRange = "H8:H50" '? Alter here (range to be extracted)'This range is the file names without the .xls
With Range(myRange)
myRows = .Rows.Count
myCols = .Columns.Count
End With
Set wb = Workbooks.Add
For Each r In Sheets(1).Range("a1", Sheets(1).Range("a" & Rows.Count).End(xlUp))
fn = Dir(myDir & r.Value & ".xls", vbNormal) 'saying MYDIR is empty and r.value is empty....since it is empty I get the "NO such file named and it gives me a popup and it is over?? I think you are close
If fn = "" Then
MsgBox "No such file named " & r.Value & ".xls"
Else
n = n + 1
If n > wb.Sheets.Count Then wb.Sheets.Add after:=wb.Sheets(n - 1)
With wb.Sheets.Cells(1).Resize(myRows, myCols)
.Formula = "='" & myFolder & "[" & fn & "]" & mySheet & "'!" & Split(myRange, ":")(0)
.Value = .Value
End With
wb.Sheets.Name = fn
End If
Next
End Sub

 

[jindon]

Where is the List of files then ?

'before I was selecting a folder with all the files in it seems to work like the old code as I choose from drop down list...ok so far then

What do yoou mean ?

'saying MYDIR is empty and r.value is empty....since it is empty I get the "NO such file named and it gives me a popup and it is over?? I think you are close

that line should read

fn = Dir(myFolder & r.Value & ".xls", vbNormal)

 

[eeder1]

Not sure as the browsefolder that popped up with the old code did the same as yours...I would put all files into one directory and point toward that path. The old code I know would refer to the list or range of file nanes to retrieve the files

 

[jindon]

1) Within the activeworkbook walks through the list (this is the list all of the file names desired and is used to name the worksheets)

Do you have list of files somewhere on the sheet ?

 

[eeder1]

Do you have list of files somewhere on the sheet ? The list of names are on a sheet within the activework in the range "H8:H50"
Your original was shown as below but you restated as the second one
fn = Dir(myDir & r.value & ".xls", vbNormal) 'I was hovering over mydir adn r.value earlier that's where I was seeing it say empty
fn = Dir(myFolder & r.value & ".xls", vbNormal) 'now it is pointing toward the correct path but is still not picking up the fill and grabbing the worksheet

 

[eeder1]

r.value is empty when you move the pointer over it...

 

[jindon]

I'm confusing....
You stated

1) Within the activeworkbook walks through the list (this is the list all of the file names desired and is used to name the worksheets)

So I thought there would be a list of file names that you want to extract from.

Am I missing something ?

 

[eeder1]

That is correct. The path selected has let's say 50 files in it and currently in the range list I have three file names in column "H" the file name is

File Name List Name (Column H) with activework sheet (1)
Texas.xls Texas
Florida.xls Florida
New York.xls New York

Each of file names have one worksheet and 37 rows of data and in all the files the worksheet is named "Unit Waterfalls" without walking through the code not sure how else to explain it looks at the list in column "H" finds the file name and then opens the file with the corresponding name and copies the worksheet data, and the names the worksheet "Texas - Unit Waterfalls" then moves onto the next file name on the list until it has finished the list

 

[eeder1]

The old code works great in 2003 Excel it is just the 2007 version that is killing it...thanks for continuing to look as I can tell you know how to code and I am a newbie. Very frustrating on my end as well as not sure why Excel would delete the Application.Filesearch function

 

[jindon]

Not clear.

File Name List Name (Column H) with activework sheet (1)
Texas.xls Texas
Florida.xls Florida
New York.xls New York

Are they in a same cell in colH ????