Importing multiple types of files

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
Is there a way to make this code import all the files automaticaly after the
code renames all the files?
 
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.
 
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.
 
Back
Top