I have some VBA code that copies all the filenames in a Windows folder and pastes them into an Excel spreadsheet. Here it is: Dim myRow As Integer Dim myFile As String Dim mySheet As Worksheet
For Each mySheet In Worksheets mySheet.Select mySheet.Unprotect Next mySheet Sheets("Files").Select Range("a1").Select myRow = 1 myFile = dir("*.xls") Do Until myFile = "" Cells(myRow, 1) = myFile myRow = myRow + 1 myFile = dir Loop
This works really well except I have to open the file that runs the code from within Excel, otherwise I get the file names from the default file location. I'd like to open the file from a desktop shortcut and have the code go to the directory of my choice. I've tried ChDir and opening a dummy file at the beginning of the macro, but neither of these works.