Importing multiple types of files

[Guest]

OK, in simple terms I need to import *.sdf files (lots of them) from a folder
into an access table.
If I change the extension of the file to txt I can import it just fine, the
problem is that I have like 1000 files a day to import.
Is there a way to point access to the correct folder and have him import the
all files into one table. Keep in mind that they don't have a known extension
to MSAccess.
Any help will be welcome.
Thanks in advance.

 

[Guest]

You can use the following code to grab all of the sdf files in a certain
folder and rename them to end in txt. Then you can import them normally.

Public FileNames() As String

Sub GetFileNames(FilePath As String)
Dim i As Integer
Dim strFileName As String

If Right(FilePath, 1) <> "\" Then
FilePath = FilePath & "\"
End If

strFileName = Dir(FilePath)
i = -1
Do While Len(strFileName) > 0
If Right(strFileName, 4) = ".sdf" Then
i = i + 1
ReDim Preserve FileNames(i)
FileNames(i) = strFileName
End If
strFileName = Dir()
Loop

End Sub

Sub RenameFiles(FilePath As String)
Dim i As Integer

For i = LBound(FileNames) To UBound(FileNames)
Name FilePath & FileNames(i) As FilePath & Left(FileNames(i),
Len(FileNames(i)) - 3) & "txt"
Next
End Sub


Please let me know if I can provide more assistance.

 

[John Nurick]

Hi Diogo,

See http://support.microsoft.com/?id=304206

 

[Guest]

Is there a way to make this code import all the files automaticaly after the
code renames all the files?

 

[Guest]

Yes, within the loop right after renaming the file, you can use the code

docmd.transfertext

to get the file to import. I did not lay it out for you, but the help is
pretty good.

Please let me know if I can provide more assistance.

 

[Guest]

You will have to excuse me but I'm new to using code, could you be more
specific?
I have the files in this folder "C:\MyDocuments\Sdf", where in the code
should I point to the folder in question? And I want to import the files
automaticaly, should I specify any import specifications in the
"docmd.transfertext". Could you help me? thanks.