By using expertatexcel.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

expertatexcel.com

 


Home
What can Excel do?
Learning Excel Books
Basic concepts Immediate window Macros 101 Syntax Use existing code Hello world Math formulas Functions Modifying Recorded Macros
Selecting Moving data Formatting Clipboard Range names Filters Sorting Fill Combining Hyperlinks Comments Options Vlookup Conditional Formatting Data validation Pivot Tables Charts Charts
Debugging Strings Active This vba statements VBA functions Run stuff
Workbook Sheet Cells Rows Column Range Selection Methods Properties Events Collections Chart
Variables Data types variables/scope Conditionals Loops functions/sub Arrays testing Batch Software dev techniques Backup/Recovery Documentation Wrappers webservice Refactor
#Errors Problems Too Slow/Optimize Error handling Problem Solving Method Someone else's sheet complex formula
Date/times Code Generation Random Shortcuts Forms Special Chars Customize your training Work and learn
Reporting Sample projects Reconcliation Adjusting data Generate Slides Special Characters
Outlook Web Database Files PPT Word API Objects Script host Dos Bloomberg JIRA Input Spreadsheets or files
Stocks Bitcoin Sports Job Productivity Project Management Tracking Health Games Programmer

Examine all Excel files on your computer

A very simple example of the power of Excel is the following task:

Find all Excel files on the computer and be able to easily browse them.

To find all Excel files on your computer the DOS Dir command comes to the rescue.
  • Open a DOS window and run
  • Go to c:
  • dir /b /s *.xls* > C:\temp\Excelout.txt

    This will create a list of all Excel files on the C: Drive. If you use other drives then repeat with each.

    Wait until the DOS command finishes which for many files could take hours.

    Using Excel open the file and use the Import feature

    Shift the data down one row and add header to row 1
  • Full Filne
  • File Name
  • Category
  • Cat2
  • Macros
  • Maybe
  • Research/make sure
  • Comment

    File Name
    To get the file name search google or click here.

    Do the following to the Spreadsheet
  • Add filters
  • Freeze pane for top row
  • Sort by the FileName

    Set up a Macro:
  • Record a Macro to Open a file
  • Change the filename to Activecell
  • Assign the macro to Ctrl+Shift+O or your preferred key combination

    Now you can move down the file list and press Ctrl+Shift+O to open and view the file.

    Set the Category column to mark the row with some indicator.




    •