VBA - List all Files in Folder - Dir() stops part-way through

[i_excel]

Hey

I've got emails in a desktop folder and I'm trying to list their file names in a worksheet. I've written the below code for that purpose but I'm facing a couple of problems:
1. The filenames are truncated: 20080607-MySpace pa#BFD0E4.html appears rather than 20080607-MySpace password request-36629. I understand this may have something to do with working on a mac, so I don't care so much about resolving this issue.
2. The listing stops part-way through so that i'm only getting about 33,000 instead of 38,000 emails listed. This is a problem.

Any help with the above would be greatly appreciated. Again, I note I'm using a mac.

i_excel

Sub ListEmails()    
    Dim i As Integer: i = 1
    Dim StrFile As String


    StrFile = Dir("Macintosh HD:Users:Name:Desktop:Gmail Data:test:messages:")  '& filenameCriteria)
    StrFile = Dir()
    
    Do While StrFile <> ""
        Cells(i + 1, 1) = i
        Cells(i + 1, 2).Value = StrFile
        StrFile = Dir()
        
    i = i + 1
    Loop


End Sub

 

[John_w]

I can't help with no. 1. For 2, change the i variable to Long. Integers have a maximum value of 32767.

 

[Fazza]

I also can't help with Mac specific code. Am writing though to propose a minor variation to the approach that is good on non-Mac machines.
Instead of writing each result to the worksheet as it is generated, store them in an array and have a single write (to worksheet).
This should be much faster to execute. Code below gives the idea, untested,

Sub ListEmails()    
    Dim i As Long
    Dim StrFile As String
    Dim ar As Variant


    ReDim ar(1 To 60000, 1 To 2)


    StrFile = Dir("Macintosh HD:Users:Name:Desktop:Gmail Data:test:messages:")  '& filenameCriteria)
    StrFile = Dir()
    
    Do While Len(StrFile)
        i = i + 1
        ar(i, 1) = i
        ar(i, 2) = StrFile
        StrFile = Dir()
    Loop


    If i > i Then Range("A2").Resize(i, 2).Value = ar


End Sub

 

[Dryver14]

@Fazza

Just a learning question for me.

were I doing the below i would have put dim ar() as variant, to indicate an array, is it not necessary?

dim ar as variant followed by redim as(1 to 60000, 1 to 2)

 

[Fazza]

dim ar as variant

it is not necessary to dim ar as variant
can be just dim ar

btw, I always use 'option explicit'

personal preference of mine is to be explicit. Then I don't miss any - that is, don't leave off the 'as whatever' part and default to variant when I don't want variant
such as
dim i
dim rInputCells
dim sFileName
dim wbkReport

would be
dim i as long
dim rInputCells as excel.range
dim sFileName as string
dim wbkReport as excel.workbook

 

[i_excel]

Thank you John_w and Fazza.

The answer, John_w, was about as basic as I thought it might be - thanks for that. Fazza - that's a very good idea, I hadn't anticipated an efficiency gain using arrays so I'll have to try it out.

i_excel

 

[Dryver14]

Thanks for the explanation Fazza, I always use option explicit too.

 

[Fazza]

you're very welcome. if you google for VBA best practice there might be some good info

and when you want to take your code to the next level, look at https://www.amazon.com/Professional-Excel-Development-Definitive-Applications/dp/0321508793

 

[daverunt]

Book looks interesting Fazza.

For those in the UK, Amazon is £53
Go to Wordery where it's £40

 

[Fazza]

I haven't looked at the second edition - which has been out some years now. The book is first class.
BTW, you'd want to have done some coding before getting into it; it is not directed at new coders.
I'm sure a bit of googling will find some sample chapters. If you're serous about coding it is 'must have'.