Excel VBA copy filenames into Excel Spreadsheet

  GlasgowMary 20:29 21 Sep 2005
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 2005

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

  GlasgowMary 21:10 21 Sep 2005

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 2005

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

  GlasgowMary 22:00 21 Sep 2005

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 2005

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 2005

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 2005

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 2005

That's interesting - thanks for the feedback.

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

Elsewhere on IDG sites

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)