Excel - Break rows into batches of 1000

I have a list of 50,000 files (full file paths). I want to re-order these into folders, 1,000 files per folder using a batch file.

Folders just need to be named 000, 001, 002, 003 etc. All I need is for Excel to write "000" for the first 1000 files, 001 for the next thousand, 002 for the next thousand until there are no more files.

I've experimented with Do While开发者_如何学运维 but can't get what I want. I can get some of what I want with:

Do While i < 1001
Range("A" & i) = "'000"

But this breaks down as I need to do a new Do While for each additional digit in a number, and it doesn't stop when the list of file runs out.

Many thanks for any help.

Try this

Sub a()
  Dim i, j As Long                         'Integers go only up to 32K
  Dim s As String
  For i = 1 To 50000
    Range("A" & i).NumberFormat = "@"      'Show as text , to show leading zeroes
    j = Int((i - 1) / 1000)                'Calculate the batch number for this Cell
    s = CStr(j)                            '...and To String
    Range("A" & i) = Mid("000", 1, 3 - Len(s)) + s  'Fill with leading zeroes
  Next i
End Sub