Excel VBA - DOS CMD print directory to tab

[alfordtp]

I dont think this is complicated, but I dont have the syntax:

I have a directory that contains 5 files (C:/Test). In DOS, I can print a list of the files to a text file in that directory (print.txt).

I want to run a macro that will run when an Excel workbook opens that will run that DOS cmd, print a list of the files in that directory, and save it to the "Sheet1" tab of the workbook that I originally opened and overwrite whats in there (so when I add new files to the directory, it will pick up all new files too).

In the end, I am looking for the syntax for printing list of files in a folder to the first tab of a workbook.

Any help is good. If the DOS cmd is not the easiest way to go, let me know other suggestions.

Thanks!

 

[Fazza]

Maybe

Private Sub Workbook_Open()
  '
  'note I've used C:\temp not C:\test
  Const str_FILE_PATH As String = "C:\temp\*.*"
  '
  Dim i As Long
  Dim strFileName As String
  '
  Application.ScreenUpdating = False
  '
  'and I'm using the sheet's CODE NAME
  With Sheet1
    .Columns(1).ClearContents
    '
    strFileName = Dir(str_FILE_PATH)
    '
    'as the example is just for 5 files
    'just loop through & load directly to worksheet
    'if there are lots, change to faster approach
    'such as load array then single load to worksheet
    Do While Len(strFileName)
      i = i + 1
      .Cells(i, 1).Value = strFileName
      strFileName = Dir()
    Loop
    '
  End With
  '
End Sub