VBA End if issue

I need your advice peers!

I keep getting an error message that says "End if  without block if" error message.

Can anyone advise what I am doing wrong.



Sub LoopAllFilesInFolder()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


myPath = "C:\Users\Zack Carter\Desktop\DUNPOS0475 DUNPOS0475 CURTAINS Pencil Pleat Hanging .v1"
myExtension = "*.xlsx*"

myFile = Dir(myPath & myExtension)
Do While myFile <> ""
If Len(myFile) = 0 Then Exit Do
Set wb = Workbooks.Open(fileName:=myPath & myFile)

Columns("P:XFD").entireColumns.Delete

wb.Close SaveChanges:=True
End If

myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Open in new window

zack carterProject AnalystAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
After removing the line#23, you also need to correct the path at line#12 which is missing a backslash in the end of the file path.
It should be like this...

myPath = "C:\Users\Zack Carter\Desktop\DUNPOS0475 DUNPOS0475 CURTAINS Pencil Pleat Hanging .v1\"

Open in new window

0
 
NorieVBA ExpertCommented:
Zack

The only If you have in the code is this,
    If Len(myFile) = 0 Then Exit Do

Open in new window

which is a single line If so doesn't need an End If.

So all you need to do to get the code to compile is remove the End If on line 23.
0
 
zack carterProject AnalystAuthor Commented:
Hi Norie,

I have removed the End IF statement and its  not looping through the fodler and deleting the columns.

Now im stumped.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Zack

What files are you looking for and in which folder?
0
 
zack carterProject AnalystAuthor Commented:
thank you for your feedback and spotting my mistake
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Zack!
0
 
Fabrice LambertFabrice LambertCommented:
Additional notes:
Indentation will help reading and understanding your code (that include us and you).
Keep in mind that source codes are supposed to be working AND read by people, so write it as understandable as possible.

I do not advice using the Dir function, I prefer using the FileSystemObject library wich is more secure.
Since the Dir function store somewhere the last criteria used for loop purpose, what if in your loop you call a function that change the criteria ?
Your code will be screwed (and you'll wonder why).
Consider the following:
    '// myFunction is supposed to print all txt files
    '// Alas, it will print 1 txt file and all xlsx files
Public Sub myfunction()
    Dim fileName as String
    fileName = Dir(CurrentProject.Path & "\*.txt")

    Do While fileName <> vbNullString
        Debug.Print fileName
        myFriendMessyfunction
    Loop
End Sub

    '// function written by someone else
    '// you arn't supposed to modify it
Public Sub myFriendMessyfunction
    Dim fileName as String
    fileName = Dir(CurrentProject.Path & "\*.xlsx")

    Do While fileName <> vbNullString
        '// random code here
    Loop
End Sub

Open in new window

On the other hand, this version will do the job as expected:
Public Sub myfunction()
    Dim FSO As Object		'// Scripting.FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Dim fld As Object		'// Scripting.Folder
    Set fld = FSO.GetFolder(CurrentProject.Path)
    
    Dim file As Object		'// Scripting.File
    For Each file In fld.Files
        If (file.Name Like "*.txt") Then
            Debug.Print file.Name
        End If
        myFriendMessyfunction
    Next
    Set fld = Nothing
    Set FSO = Nothing
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.