Excel VBA - DOS CMD print directory to tab

alfordtp

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0

Forum statistics

Threads
1,224,598
Messages
6,179,820
Members
452,946
Latest member
JoseDavid

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
Back
Top