1

I am currently working on a macro that imports files from a number of subfolders within a folder named "simulations" which is in the current working directory. The code I have is working for everything I need except the last step which is importing the file list into excel.

Currently, I have a macro which reads a file I create by hand using the cmd line:

> dir /b > foldernames.txt

I then open this file in another excel window and then copy the range into my current excel workbook. This seems like a slow, roundabout way of doing this and am looking for macro solutions that can circumvent this.

I have tried a number of code combinations using things like:

Dim Fold As Folder
Dim FS As FileSystemObject

I suspect that most of the syntax I've tried is not compatible with 2007 onwards. Are there certain functions that would be helpful for this application? I don't need code written for, just suggestions about applicable functions so I can play with those.

teepee
  • 2,620
  • 2
  • 22
  • 47
  • See cris' answer here: http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory/14246818#14246818 – Tim Williams Mar 31 '14 at 23:30

1 Answers1

3

We run the CMD command via Shell

Sub RunDir()
    x = Shell("cmd.exe /c dir /b > C:\TestFolder\foldernames.txt", 1)
    Close #1
    Open "C:\TestFolder\foldernames.txt" For Input As #1
    j = 1
     Do While Not EOF(1)
            Line Input #1, TextLine
            Cells(j, 1) = TextLine
            j = j + 1
        Loop
    Close #1
End Sub

I use TestFolder as the destination for the Dir..............pick your own

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • + 1 Nice one. However why `Close #1` in the beginning? – Siddharth Rout Apr 01 '14 at 05:16
  • @SiddharthRout Just in case it had been left open by any other sub or process......I like it because it is compact and quick. – Gary's Student Apr 01 '14 at 10:57
  • @Gary'sStudent, it seems that I occasionally get an error when this code is run. Other times it seems to work. I'm not sure why. For example if I delete the textfile and then run this macro in my Workbook_Open function, it will crash when I open the workbook, saying the file cannot be found, but when I look in the directory, it has indeed created the file. When I run it a second time then it works from then on. – teepee Apr 01 '14 at 14:26
  • @teepee......it is possible that the macro is not letting the **Shell** command complete before trying to open the file.....I will look for a solution! – Gary's Student Apr 01 '14 at 15:01
  • Yes, that's exactly the problem I'm having. The file doesn't appear in the folder before the function tries to read it, so I will either get an error if the file doesn't exist, or if it does, it may extract old information contained in that file before it gets updated by the Shell command. – teepee Apr 01 '14 at 20:15