Problem with IF Statement

[robcarr]

Dear All,

I am having a problem trying to get the data to find the files with in the coding below, it always jumps to the else and exits the macro. Can someone please help, I need to get the coding to find a file and if it finds just 1.xls file in the folder it will open all excel files, the coding for this works fine, I just need the coding to work to find a file in the folder and check to see if the folder exists.

Sub OpenAndCopy()
Dim i As Integer
Sheets("Macro Sheet&quot.Select
userval = Application.InputBox("Type in a unique Surname or Team Name:", "Select Team Details", 2)
If userval <> False Then [E20] = userval
findname = [E20].Value
pathname = Range(&quot;E18&quot.Value
With Application.FileSearch
Sheets(&quot;Macro Sheet&quot.Select
.LookIn = pathname
'* represents wildcard characters

If .Execute > 0 Then 'Workbook exists
Application.ScreenUpdating = False
Dim oFso, oFold, f1, oFiles
Dim wbkMe, wbk2 As Workbook
'ADDED
Dim lCpdRows As Long
Dim ws As String
' set up the files
Set oFso = CreateObject(&quot;Scripting.FileSystemObject&quot
Sheets(&quot;Macro Sheet&quot.Select
pathname = [E18].Value
Set oFold = oFso.GetFolder(pathname) ' folder with source data
Sheets(&quot;Proficiency Vantive Cases&quot.Select
Set oFiles = oFold.Files
Set wbkMe = ThisWorkbook
For Each f1 In oFiles
' only interested in xl files
If f1.Name Like &quot;*.xls&quot; Then
Set wbk2 = Workbooks.Open(Filename:=f1)
'ADDED
ws = &quot;Proficiency Vantive Cases&quot;
With wbk2.Worksheets(&quot;Complete Listing&quot.Range(&quot;A4&quot.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

lCpdRows = Selection.Rows.Count
End With
If wbkMe.Worksheets(&quot;Proficiency Vantive Cases&quot.Range(&quot;A65536&quot.End(xlUp).Row _
+ lCpdRows > 65536 Then ws = &quot;Sheet2&quot;
With wbkMe.Worksheets(ws) 'amended
.Activate
.Range(&quot;A1&quot.Select
' check for existing data
If IsEmpty(.Range(&quot;A1&quot) And _
.Range(&quot;A1&quot.End(xlDown).Row = 65536 Then _
ActiveSheet.Paste
' last row
If Not IsEmpty(.Range(&quot;A1&quot) Then
.Range(&quot;A1&quot.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End If
End With
Application.CutCopyMode = False
Application.DisplayAlerts = False
wbk2.Close
Application.DisplayAlerts = True
Set wbk2 = Nothing
End If
Next
Sheets(&quot;Proficiency Vantive Cases&quot.Select
Rows(&quot;1:1&quot.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=&quot;=Agent Names*&quot;, Operator:=xlAnd
Range(&quot;A1&quot.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Rows(&quot;1:1&quot.Select
Selection.AutoFilter
Range(&quot;A1&quot.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(&quot;A1&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(&quot;A1&quot.Select
Columns(&quot;G:G&quot.Select
Selection.Style = &quot;Percent&quot;
Selection.NumberFormat = &quot;0.00%&quot;
Range(&quot;A1&quot.Select
' then close the active workbook and returns to 101 template
Application.ScreenUpdating = True
Else 'There is NOt a Workbook
MsgBox &quot;Vantive Data Was Not Found!&quot;, vbInformation
End If
End With
Sheets(&quot;Macro Sheet&quot.Select


End Sub


when the coding is run the .lookin = pathname does not look for the value in pathname, it looks for my desktop profile location and ignores pathname, pathname does display the correct place to look for files.

I have had this working before on 1 files in a folder, but i need it to look for any .xls files in the specified folder, and it it finds a .xls file it will open all files.

Thanks in advance for any help with this.

Rob.

 

[segmentationfault]

It worked fine for me, but I may misunderstand which if statement is causing problems.

Make sure that your pathname ends in a /.

 

[segmentationfault]

It worked fine for me, but I may misunderstand which if statement is causing problems.

Make sure that your pathname ends in a \.

Hm, hope that's not a double post...

 

[robcarr]

When I run the coding it excepts the pathname and if it finds the location it should open all files in that location,which works, but if the location doesnt exist it should exit and say doesnt exist, but all it does is continue down the coding and when it gets to the
&quot;get oFold = oFso.GetFolder(pathname) ' folder with source data&quot; place it says path name not found, which is correct, but is shouldn;t get this far, any help would be much appreciated.

regards