get file name

[tcgaines]

how do you get a filename?

 

[namliam]

What do you mean? How do you mean?
What is your purpose?

Seasons greetings from Amsterdam

The Mailman

 

[ghudson]

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

Browse [Find a directory or file]

 

[tcgaines]

how do you get a filename?

I am importing several workbooks into their own tables. Using the code below, If there are 7 .xls in the given folder, the tables are named consecutively "1"-"7." I would like to name each of the tables the name of the workbook that is being imported.

If .FoundFiles.Count > 0 Then
For fileLoop = 1 To .FoundFiles.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheet TypeExcel8, fileLoop, 'this names each of the tables
.Foundfiles(fileLoop)
Next fileLoop

Thanks for suggestions.

 

[namliam]

What is your ".FoundFiles" ? Is that a custom function/property?

What does it do?

Does it maybe have a "name" property?

Seasons greetings from Amsterdam

The Mailman

 

[tcgaines]

What is your ".FoundFiles" ? Is that a custom function/property?

What does it do?

Does it maybe have a "name" property?

Seasons greetings from Amsterdam

The Mailman

No, this is not a custom function, but I think you may have answered my question.

MSDN:"Using the FoundFiles Object
Use the FoundFiles property to return the FoundFiles object. This example steps through the list of files that are found and displays the path and file name of each file. Use FoundFiles(index), where index is the index number, to return the path and file name of a specific file found during the search.

With Application.FileSearch
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next I
End With"

I'm going to play around with this code and see if I can make something work.

Thank you again for your help.

 

[namliam]

Very intresting.... I did not know this... Something new learned today...

Anyway... Appearently just doing .FoundFiles(i)
returns the filename + path...

So Dir(.FoundFiles(i)) would return filename only...

What Reference do you need to use for this? Very intresting possibilities here, I usually do something with Dir and stuff. This is very intresting indeed....

Reference? Please?

Greets

The Mailman

 

[tcgaines]

Right on. Learning is always a plus :]

This is the context im using this code in. Is that what you mean by reference?

This searches a directory, finds the xls files, imports them into seperate tables, and names them respective to the workbook names. FileNameNoPathNoExtension is the only custome function.

With Application.FileSearch
.LookIn = MyFolder 'directory path
.FileName = "*.xls" 'I only want .xls files
.Execute

If .FoundFiles.Count > 0 Then
For i = 1 To .FoundFiles.Count
fName = FileNameNoPathNoExtension(.FoundFiles(i)) 'Custom function returning file name without path/extension
DoCmd.TransferSpreadsheet acImport, acSpreadsheet TypeExcel8, fName, .Foundfiles(i), True
Next i