Nice alternative, thanks Toby!
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 StringDim strPath As StringstrPath = <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()
WendHope 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 namesCathy:
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 FSODim FSOFolderDim FF As FileDim MyFileName as stringDim MyFilesSet FSO = CreateObject("Scripting.FileSystemObject")Set FSOFolder = FSO.GetFolder(MyPath)Set MyFiles = FSOFolder.FilesFor Each FF In MyFilesMyFileName = FF.NameIf Right(MyFileName,4) = ".PTX" then'process your fileEnd IfNextDoug 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
Please zip all files prior to uploading to Files section.
Tidak ada komentar:
Posting Komentar