1

I have a macro (Loop through files in a folder using VBA?) that looks inside a folder for zip files, but the problem is that the last zip file has an empty string as a name. How can I avoid that error.

The code for the macro:

Sub LoopThroughFiles()
    ' Define variables
    Dim file As String, folder As String

    ' Define working directory
    folder = "C:\Users\cportocarrero\Desktop\Curvas\"

    ' Define type of file to search for
    file = Dir(folder & "*.zip")

    ' Loop through all the files
    Do While Len(file) > 0
        Debug.Print file
        file = Dir
        MsgBox file
    Loop
End Sub
Community
  • 1
  • 1
capm
  • 1,017
  • 3
  • 18
  • 24
  • 1
    Your problem is that you are Debug.Printing one value of `file` but then you are changing the the `file` variable before sending to MsgBox. KekuSemau's answer shows how your loop should be structured. Your `While` test doesn't need to change as `Len(file) > 0` will have same effect as `file <> ""` – DeanOC May 21 '13 at 22:14

1 Answers1

1

You have to keep the loop like this:
- first call to dir before the loop, then
- the while loop
- call to dir should be the last command inside the loop 

file = Dir(folder & "*.zip")
Do While file <> ""
    Debug.Print file
    MsgBox file
    file = Dir
Loop

When dir returns an empty string, this means that there are no more matches.

KekuSemau
  • 6,830
  • 4
  • 24
  • 34
  • 2
    +1 I'm being picky here, but the call to `Dir` *should* be the last command inside the loop, but doesn't *have* to be. It just needs to be after all usage of the `file` variable. – DeanOC May 21 '13 at 22:11
  • Yes, you are completely right. +1 for being picky, I have edited that. – KekuSemau May 21 '13 at 22:21