CODEWITHSUNDEEP
excelvbadirectory
murugan_kotheesan
murugan_kotheesan

Reputation: 43

DIR function in vba works diffrent when passing a value directly and by a variable

Hi i have some files *in a particular folder in my system when i use *the below** code it gives all the files in that folder (passing the path directly)

filenm = Dir("C:\Documents and Settings\murugan.k\Desktop\Daily report automation\Eve report\trial\")

Do Until filenm = ""
ActiveSheet.Cells(ctr, 12).Value = filenm
ctr = ctr + 1
filenm = Dir()
Loop

but when i store the same path in a variable and pass the varible to the dir function it gives me only two files (AUTOEXEC.BAT & bar.emf)

filenm = Dir(pth)
Do Until filenm = ""
ActiveSheet.Cells(ctr, 12).Value = filenm
ctr = ctr + 1
filenm = Dir()
Loop

could some one please help me in resolving this problem because i can't hard code the path in macro which has to be dynamic (changes as per user)

Upvotes: 0

Views: 10839

Answers (3)

jogrohs
jogrohs

Reputation: 89

well, for me your answer did not work and after some looking around i found a little mistake. your code is fine in general, but the "=" sign will not work because

When no more file names match, Dir returns a zero-length string ("").

at least i think this is the reason it showed nothing for my output. so i would say it works like:

Sub LoopThruDirectory2()

Dim strPath As String
Dim strFile As String
Dim x As Integer
strPath = ThisWorkbook.path
strFile = Dir(strPath & "\")
Do While strFile <> ""
    x = x + 1
    Debug.Print strFile
    strFile = Dir    ' Get next entry.
Loop

End Sub

feel free to set "thisworkbook.path" as your path and handle strFile as you want. (eg. see above) . for my project the user wanted to pick a chosen folder so i had to use "msoFileDialogFolderPicker". just in case someone has the same problem

Upvotes: 0

murugan_kotheesan
murugan_kotheesan

Reputation: 43

i some how got his correct

b4 i have the path without"\" at the end so i concatenate the path with "\" and put that in anothe variable and i pass that variable to DIR function (which din't work) but now i have added "\" at the end of folder path in the input itself and i pass that varible to DIR it works now

'            checking the available files
filenm = Dir(File_pth)
Do Until filenm = ""
ActiveSheet.Cells(ctr, 12).Value = filenm
ctr = ctr + 1
filenm = Dir()
Loop

Upvotes: 0

user2140173
user2140173

Reputation:

Try different attributes for the Dir() function.

path = "C:\Documents and Settings\murugan.k\Desktop\" & _ 
        "Daily report automation\Eve report\trial\"

filenm = Dir(path, vbNormal)

Do Until filenm = ""
    ActiveSheet.Cells(ctr, 12).Value = filenm
    ctr = ctr + 1
    filenm = Dir()
Loop

Upvotes: 1

Related Questions