Making an array in Excel with the file names of a particular folder

Hello, could anyone help me, I already have a macro in Excel that uses an array of filenames to process data, but so far I have to enter the filenames manually.

Basically, I want the macro to simply create an array in Excel with the files names and their extension (which will always be the same) located in a particular folder, sucha as:

Filename1.ext

Filename2.ext

...

Filename13.ext

There will always be the same amount of files in the particular folder (13 in total) , but this process would really make processing of the data quicker than having to enter the filenames by hand each time.

Any help would be grealty appreciated.

Tank you!

These forums are for VB.NET questions.

If your using the VB within Excel then this is VBA (Visual Basic for Applications) is a very different product from VB.NET and there are some other locations where you will get a quicker and better response to your VBA questions.

To create an array of strings and use them

Dim Filename(13) as string

For i = 1 to 13

'To reference a specific element Filename(i)

Next i

However I would use the VBA Forum for further questions with regard to VBA.

You may find more assistance in following which specifically deals with MS Office development.

Office Automation: office.developer.automation newsgroup

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.office.developer.automation&lang=en&cr=US

Or the Office Newgroups

http://www.microsoft.com/office/community/en-us/default.mspx d=1

Or the VBA Forum

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

Hope that helps....

I apologize for the mixup.

Thank you for your input.

Hi

I would use a section of one of your worksheets to store the names, and read this into the array when the macro is run.Alternatively, you can hardcode the names into your code if they never change.

Hi

There are a couple of different ways of appoaching this depending upon your need.

1. Getting the file list from the user using the standard Excel VBA GetOpenFilename

Sub Test1

Dim arrNames as variant

dim indx as long

arrNames=GetOpenFilename(Filefilter:="Workbooks (*.xls),*.xls", Multiselect:=True)

if not isarray(arrNames) then exit sub ' user cancelled

for indx= lbound(arrNames) to ubound(arrNames)

cells(indx+1,1)=arrNames(indx)

next indx

End Sub

2. Using the Scripting objects, which require set up a reference to Microsoft Scripting Runtime (VBA/Tools/References)

Sub Test2

Dim fso as Scripting.FileSystemObject

Dim fldr as Scripting.Folder

dim fil as Scripting.File

const strFolder as string = "C:\Temp\" ' or however you want to set this

dim rw as long

set fso = New Scripting.FileSystemObject

set fldr = fso.GetFolder(strFolder)

rw=1

for each fil in fldr.files

cells(rw,1)=fil.path

rw=rw+1

next fil

End Sub

Regards

Peter Mo.