How do I copy multiple file names?

Copy file names from a directory into a workbook

• My question sound simple, and migh be more or less difficult. I need to wirte some VBA code which copies the names of all files with the extension .h that are located in the same directory as theto one of it's worksheets. If for example I have a directory with the following files: test.h good.h done.h main.xls nothing.txt The main.xls of course being our application with the VBA code. So when the main.xls is opened, it will copy the following file name entries to workbook1: test.h good.h done.h I am using2003 I

• Answer:

  I apologize for my delay in responding to your question. I forgot to check my inbox when I got back from vacation last week and last night found 8 pending questions. Yikes! Here is some event code that will list the files: Private Sub Workbook_Open() 'Make list on sheet1 of .h files in working folder Dim Filename As String Dim iRow As Long ChDir ThisWorkbook.Path Filename = Dir("*.h") 'Pattern match for .h files iRow = 2 Columns("A").ClearContents Do While Filename <> "" 'write starting at row 2 in column A Cells(iRow, "A") = Filename iRow = iRow + 1 'get next filename Filename = Dir() Loop End Sub You must place this code in the workbook's ThisWorkbook code module. To do this right-click on theicon at the left end of the Worksheet Menu bar, select View Code, and paste this code into the Code pane, then Save the workbook--being sure to save it in the folder containing the .h files. This code will now run when the workbook is opened. If you want to test the code without having to close and open the workbook, simply place your cursor somewhere in the body of the code and click the Run Macro button on the Standard toolbar. As you can see this writes the file names to column A of the active worksheet, but I think you can see how to adjust the code to write it where you want.