ACCESS FEED: Re: [AccessDevelopers] Ascertaining file names

Rabu, 19 Oktober 2011

Re: [AccessDevelopers] Ascertaining file names

Nice alternative, thanks Toby!

Doug

On Wed, Oct 19, 2011 at 9:59 AM, Toby Bierly <toby@stahlbush.com> wrote:

I'm sure Doug's method with the FileSystemObject will work, but I thought I might share one more alternative which is using the Dir function. Dir accepts wildcard characters to match multiple files. Dir will return the first matching file when it is called with an argument. Dir called with no arguments will search for additional matching files using the same argument from the first call.   Dir returns an empty string when there are no more matches. Instead of renaming the extension, you could also move to another folder, such as a subfolder called `Processed`, using FileCopy and Kill. So you could code it like this:

Dim strFoundFile As String

Dim strPath As String

strPath = <your path to folder with PTX files>        ' example "C:\MyFiles\"

strFoundFile = Dir(strPath & "*.PTX")

While strFoundFile <> ""

    ' import strFoundFile here

    ' DoCmd.TransferText ..................

    ' now move file and grab the next matching file
    FileCopy strPath & strFoundFile, strPath & "Processed\" & strFoundFile
    Kill strPath & sFoundFile
    strFoundFile = Dir()
Wend

Hope that helps,

Toby

-----Original Message-----
From: AccessDevelopers@yahoogroups.com [mailto:AccessDevelopers@yahoogroups.com] On Behalf Of Doug Steele
Sent: Wednesday, October 19, 2011 7:24 AM
To: AccessDevelopers@yahoogroups.com
Subject: Re: [AccessDevelopers] Ascertaining file names

Cathy:

Here is some code that should get you to the point where you found each file and have its name. In the code, I am assuming that the full path to your input folder is in the variable 'MyPath'

Dim FSO

Dim FSOFolder

Dim FF As File

Dim MyFileName as string

Dim MyFiles



Set FSO = CreateObject("Scripting.FileSystemObject")

Set FSOFolder = FSO.GetFolder(MyPath)

Set MyFiles = FSOFolder.Files

For Each FF In MyFiles

MyFileName = FF.Name

If Right(MyFileName,4) = ".PTX" then

'process your file

End If

Next

Doug Steele

On Wed, Oct 19, 2011 at 7:05 AM, Cathy Jupp <cathy.jupp@stoneham.plc.uk> wrote:

I have an ongoing need to import text files that were output from another application into an Access table. I will be doing this using VBA code. I know which folder these files will be in, and the file extension. Once each file has been imported I will rename it from something.PTX to something.XTP so that the file is retained but will be ignored by future imports.

The problem I have is that I will never know the file names, and there could be any number of them.

So essentially what I need to do is loop through the following:

open the first file I encounter in the folder with an extension of .PTX
import it
rename it

until no files with a .PTX extension remain.

I have no idea how to open a file for import within Access without knowing its name. Can anyone advise me how to go about this please?

Many thanks in advance.

Cathy