Excel VBA - DOS CMD print directory to tab

alfordtp

Board Regular
Joined
Oct 3, 2008
Messages
56
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Maybe
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top