Importing Question...

[ChiTownDiva]

Murnin' All...Happy Hump Day...

Last week I posed the question:

"I wonder if Access can:

1. In a windows folder, change the file extension from DAT to TXT (or CSV).
2. Use an import spec and import the file into the database.
3. Append the file to an existing table.
4. Drop the imported file from the database.

I need to figure out a way to do this via some code because I have about 100 tables to import into a database this way."

I got a pretty good answer:


Function Import()
With Application.FileSearch
.NewSearch
.LookIn = "S:\SALESUP\Yasu\test"
.SearchSubFolders = True
.FileName = "*.TXT"
.MatchTextExactly = True
End With

With Application.FileSearch
If .Execute() > 0 Then

For I = 1 To .FoundFiles.Count
DoCmd.TransferText acImportDelim, "VALUE Import Specs", "TempTable", .FoundFiles(I), True

DoCmd.OpenQuery "testappendquery"



Next I
End If
End With
End Function

Well, now we want to be able to download all of the *.dat files without going in manually changing each one to a *.txt file first. Also we don't want to append each import to a table. We just want to import each download as a table.

I know I can change the extension via MS DOS, but I don't have those privledges. The alternative is to accomplish this via Access. I know there is a GetFile() function in Access. Is there a way to incorporate this function within the above code so we don't have to change all of our *.dat files to *.txt files first and then simply import each download into the database?

Thanks in advance for any assistance with this.

ChiTownDiva

 

[PHV]

but I don't have those privledges
If you can't do it in a command window I don't think you can do it in VBA.
Anyway take a look at the Name ... As instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ChiTownDiva]

Thanks PHV...

I went to

http://support.microsoft.com/?kbid=306144

and found this code:


Sub DataImport(strPath As String, strTableName As String)
'Variables used to create and modify the file extension
Dim objFileSystem
Dim objFile
Dim strFileCopy As String
Dim intExtPosition As Integer

'Create an instance of the FileSystemObject to access
'the local file system
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

'Use the GetFile method to return a File object corresponding to the
'file in a specified path.
Set objFile = objFileSystem.GetFile(strPath)
intExtPosition = InStr(objFile.Name, ".")
If intExtPosition > 0 Then
strFileCopy = Left(objFile.Name, intExtPosition - 1) & ".txt"
Else
strFileCopy = objFile.Name & ".txt"
End If

'Create a copy of the file with a .txt extension
objFile.Copy strFileCopy, True
DoCmd.TransferText acImportDelim, , strTableName, strFileCopy, True
End Sub

The part that is italicized is the part that I would like to incorporate into the function.

ChiTownDiva