Excel \ OpenOffice Calc Dir function does not iterate thru files

Question

My VBA-Code only returns the first file of the the directory while it should return at least 3 files, does anyone have a clue whats going on here?

FolderName = Environ("UserProfile") + "\"
FilePath = FolderName & "Invoice." & Format(Date,"yyyy.mm.dd") & "-" & "*" & ".ods"

count = 1
FileName = Dir(FilePath)

While (FileName <> "" And count < 5)
    MsgBox "FileName = " + FileName
    count = count + 1
Wend

I cant accept answer for another 50 seconds... Rookie policy of StackoverFlow. PS: Do you know how to format a digit to 3-digits? For example 3 to 003 — userX, Jan 30 '14 at 07:52

score 0 · Accepted Answer · answered Jan 30 '14 at 07:43

0

Try this code (note that I added FileName = Dir in the end of the While loop):

FolderName = Environ("UserProfile") + "\"
FilePath = FolderName & "Invoice." & Format(Date,"yyyy.mm.dd") & "-" & "*" & ".ods"

count = 1
FileName = Dir(FilePath)

While (FileName <> "" And count < 5)
    MsgBox "FileName = " + FileName
    count = count + 1
    FileName = Dir
Wend

answered Jan 30 '14 at 07:43

Dmitry Pavliv

35,333
13
79
80

Another quick question, do you know how to format a digit to 3-digits? For example 3 to 003 – userX Jan 30 '14 at 07:51
Yes, I know. Just apply custom formating "000" to the cell, or use excel formula `=Text("A1","000")` – Dmitry Pavliv Jan 30 '14 at 07:54
the same thing with VBA: `MsgBox Format(3, "000")` – Dmitry Pavliv Jan 30 '14 at 07:56
If you got time for one last question how do you add a VBA-string to the excel-sheet – userX Jan 30 '14 at 08:11
what do you mean by `add a VBA-string to the excel-sheet` ? this one: `Range("A1")="something"`? – Dmitry Pavliv Jan 30 '14 at 08:13
Yes, I want to move the string to a Cell B7, but the string is in the macro or VBA – userX Jan 30 '14 at 08:17
write `Range("B7")="something"` or if your string in some varaible: `Range("B7")=myVariable` – Dmitry Pavliv Jan 30 '14 at 08:30
I get "basic runtime error sub procedure or function procedure not defined" – userX Jan 30 '14 at 08:33
Have you use this string inside `Sub` statement? If no, sandwitch `Range("B7")="something"` with `Sub test()` and `End Sub` – Dmitry Pavliv Jan 30 '14 at 08:35

Excel \ OpenOffice Calc Dir function does not iterate thru files

1 Answers1