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]

I was having a problem after recording a macro that would capture all the sheets in the file so I was hoping to add to yours.....thanks for your patie nce as always for helping as I am trying to run before walk on vba.

 

[jindon]

You should be able to RECORD a MACRO for yourself, no excuse!

Sub example()
Dim ws As Worksheet, myPrintArea As String
For Each ws In Sheets
    Select Case True
        Case ws.Name Like "*5 Year Forecast"
            myPrintArea = "$A$1:$X$77"
        Case ws.Name Like "*FY 2009 Snapshot"
            myPrintArea = "$A$1:$Y$72"
    End Select
    If myPrintArea <> "" Then
        With ws.PageSetup
            .PrintArea = myPrintArea
            .PaperSize = xlPaperLegal
            .LeftMargin = Application.CentimetersToPoints(0.25)
            .RightMargin = Application.CentimetersToPoints(0.25)
        End With
        ws.Columns("A:Z").AutoFilt
        ' or
        'ws.Columns("A:Z").ColumnWidth = 11
    End If
    myPrintArea = ""
Next
End Sub

 

[eeder1]

Agreed and recording a macro is not an issue at all (i wish it was just that simple for me) Trying to incorporate the recorded macro into existing code is where I was struggling. Thank you so much for your expertise.