CODEWITHSUNDEEP
excelvba
Mike
Mike

Reputation: 37

Loop to check file existence without DIR function

I need to make a loop that will save the edited files in a folder in numerical order, but if I run the macro again it needs to keep from the last number:

wb.SaveAs "C:\User\Desktop\Data\" & i & ".dat"
i = i + 1

Here the code saves the files in order (1,2,3). And if I run the macro again it needs to save (4,5,6).

I tried to use this loop above to save the function:

Do While Dir("C:\User\Desktop\Data\" & i & ".dat") = i
    i = i + 1
Loop

But apparently, I can't use the DIR function, because I'm already using it at the end of the loop to get the next file, and the loop breaks.

'Get next file name
 MyFile = Dir

So, is there's any way to do this? I'm trying to make a function that would check and return true or false to whether a file with that number already exists, but could not make it work. Thanks for the help.

Upvotes: 2

Views: 1264

Answers (1)

Brendan Gooden
Brendan Gooden

Reputation: 1561

You could get the largest number in the directory, and keep going from there?

This code presumes that all the files are named as such: [numericalfilename].[extension] for example "143.dat". This code will not work if there are alpha characters in the file-name (apart from extension)

Once you have defined Path and Extension variables, you can call the Function NextNumber which will give you [MaxNumber_In_Directory] + 1

From here you can perform your "save loop", by setting i = NextNumber(Path, "*." & Ext)

Sub test()
Dim Path As String
Dim Ext As String
Dim i as Long

' ***** REPLACE VARIABLES ******

    Path = "C:\New\New Folder"
    Ext = "dat"

' ******************************

    i = NextNumber("C:\New\New Folder", "*." & Ext)

' ********************************
' **** PERFORM SAVE CODE HERE ****
' ********************************

   'I am not 100% sure of your save loop, you'll need to modify this

    wb.SaveAs "C:\User\Desktop\Data\" & i & ".dat"
    i = i + 1


End Sub




Public Function NextNumber(strPath As String, strExt As String) As Long
Dim vArray(500)
Dim i As Long
Dim length As Long
ChDir strPath
strExtension = Dir(strExt)

        'Perform Loop
        Do While strExtension <> ""
            length = Len(strExtension) - (Len(strExt) - 2) - 1
            vArray(i) = CLng(Mid(strExtension, 1, length))
             i = i + 1
            strExtension = Dir
        Loop

NextNumber = WorksheetFunction.Max(vArray) + 1

End Function

Upvotes: 1

Related Questions