Excel VBA copy filenames into Excel Spreadsheet

  GlasgowMary 20:29 21 Sep 05
Locked

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.

Hope you can help. Thanks, Mary

PS The above code might be useful to Onizuka

  VoG II 20:47 21 Sep 05

ChDir should work. You need to specify the full path to the folder in " quotes.

  GlasgowMary 21:10 21 Sep 05

I did specify the full path in quotes, but it still tried to list the files in the default file location or from the location that I last opened an Excel file from. Is there anything else I can try?

Regards, Mary

  VoG II 21:44 21 Sep 05

Have you tried using an Input box to ask for the path to the folder?

  GlasgowMary 22:00 21 Sep 05

No I haven't tried this. The people who will be using this macro are unlikely to know what the path is or even what a path is!

I'll have another look at the ChDir code. Maybe I'm doing something wrong there.

Will let you know how I get on. Thanks for your help with this.

Mary

  Number 7 22:46 21 Sep 05

I've tried your code with the ChDir command and it works fine when the file is opened from a desktop shortcut.

ChDir ("c:\documents and settings\UserName\my documents\") lists the files in the My Documents folder.

ChDir ("c:\documents and settings\UserName\my documents\excel") lists the files in the Excel folder.

  GlasgowMary 09:28 22 Sep 05

Hi, I've just discovered that if the default file location is the C:\ drive then the ChDir command will not work.

I changed the default, in Excel, to a network location, different from the location of the files names I want listed, and the macro ran just fine.

Thanks for the help you gave me. Knowing that ChDir should work made me look again at other factors.

Regards,
Mary

  GlasgowMary 09:29 22 Sep 05

Hi, I've just discovered that if the default file location is the C:\ drive then the ChDir command will not work.

I changed the default, in Excel, to a network location, different from the location of the files names I want listed, and the macro ran just fine.

Thanks for the help you gave me. Knowing that ChDir should work made me look again at other factors.

Regards,
Mary

  VoG II 10:50 22 Sep 05

That's interesting - thanks for the feedback.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…