Import files

[ekta]

Hi:

I use the code below to first convert files of type .ACY to .txt and then import them in access tables. But now I want that it should convert files of any types. They can be .LAS, .ORD etc. How should I modify the code to do this. My code is below:

Private Sub btnImport_Click()
Dim mPath As String, mFilename1 As String, mFilename2 As String, i As Integer
Dim mTable As String, mPos As Integer, mNumber As Integer, mLenNumber As Integer
Dim mFormatCode As String, mFilename As String, mStr As String
Dim mMsg As String, mBooDelete As Boolean, mNumFiles As Integer

On Error GoTo ImportFiles_error

If IsNull(Me.ImportDirectory) Then
Me.ImportDirectory.SetFocus
MsgBox "You must specify an Import Directory", , "Choose or Type Import Directory"
Exit Sub
End If

mNumFiles = 0
mMsg = ""

mPath = Me.ImportDirectory & "\"
mFilename1 = Dir(mPath & "*.acy")
Do While mFilename1 <> ""
mLenNumber = InStr(mFilename1, ".") - 1
If mLenNumber > 0 Then
mNumber = CInt(Left(mFilename1, mLenNumber))
mFormatCode = ""
For i = 1 To mLenNumber
mFormatCode = mFormatCode & "0"
Next i

mFilename = mPath & mFilename1
mFilename2 = mPath & Format(mNumber, mFormatCode) & ".txt"
FileCopy mFilename, mFilename2
DoEvents
Kill mFilename
DoEvents

mTable = "tblData_" & Format(mNumber, mFormatCode)
On Error Resume Next
mStr = CurrentDb.TableDefs(mTable).Name
If Err.Number = 0 Then
'table already exists -- close and delete it
DoCmd.Close acTable, mTable
DoCmd.DeleteObject acTable, mTable
CurrentDb.TableDefs.Refresh
mBooDelete = True
mMsg = mMsg & "DELETED: " & mTable
Else
mBooDelete = False
End If
On Error GoTo ImportFiles_error

DoCmd.TransferText acImportDelim, "Import", mTable, mFilename2, False

mNumFiles = mNumFiles + 1
If mBooDelete Then
mMsg = mMsg & " and replaced"
Else
mMsg = mMsg & mTable
End If

End If
mMsg = mMsg & vbCrLf
mFilename1 = Dir
Loop
MsgBox "DIRECTORY: " & mPath & vbCrLf & vbCrLf & mMsg, , "Imported " & mNumFiles & " tables"
Exit Sub
ImportFiles_error:
MsgBox Err.Description, , "ERROR " & Err.Number
Stop
Resume
End Sub

Thanks,

Ekta

 

[ekta]

anyone????

 

[cable]

This line:
mFilename1 = Dir(mPath & "*.acy")
is the one that needs changing each time. You'll probably need to loop through the whole code with each extension you want to import.

 

[ekta]

Thanks for replying cable. The problem is that I don't know the extensions. I get these files that I need to import and everytime extensions are different.

 

[cable]

Thanks for replying cable. The problem is that I don't know the extensions. I get these files that I need to import and everytime extensions are different.

urm then how do you know which files to import?
mFilename1 = Dir(mPath & "file*.*") would make the code process all files starting with file, ie you can use the normal wildcard's in the dir cmd.

also how do you know all the files are going to be the same format?