Hi there!

I've got 300 data files in the following format and they've similar name 
convention, e.g. 020101.txt, 020102.txt...

CODE	STOCK SHORT NAME	(SH)	($)	TYPE	DATE	
1	CHEUNG KONG	518000	37234500	M	2002/6/11	
2	CLP HOLDINGS	94000	3080200	M	2002/6/11	
3	HK & CHINA GAS	240000	2576000	M	2002/6/11	
4	WHARF HOLDINGS	9000	171000	M	2002/6/11	
5	HSBC HOLDINGS	434800	40603800	M	2002/6/11	
8	PCCW	1294000	2471540	M	2002/6/11	
11	HANG SENG BANK	128300	11066525	M	2002/6/11	
12	HENDERSON LAND	15000	511500	M	2002/6/11	

Can any one tell me how I can import/append the files into one single 
table programatically using access vba?

Any help would be greatly appreciated!

Terrence Ho

You need to first create an Import Specification for the files to be
imported. In my example, I created an Import Specification called
"Terence Ho Import Specification".  Help will tell you how to create the
spec.  Once you've done that, then you can use one of the two sets of
code below to import the files.  My example imports all files in the
directory "C:\Documents and Settings\John\My Documents\My Work\Terence
Ho\" at one time.  You can modify the code to import specific files.
This code is courtesy of the MSDN October 2001 Library.

Here is code that uses Microsoft's Scripting Runtime library.


Sub TestGetFiles()
' A reference to the
' Microsoft Scripting Runtime library
' must be set
' Call to test GetFiles function.

   Dim dctDict As Scripting.Dictionary
   Dim varItem As Variant
   Dim strDirPath As String
   
   ' Set the path the files
   strDirPath = "C:\Documents and Settings\John\My Documents\My
Work\Terence Ho\"
   ' Create new dictionary.
   Set dctDict = New Scripting.Dictionary
   ' Call recursively, return files into Dictionary object.
   If GetFiles(strDirPath, dctDict, True) Then
      ' Print items in dictionary.
      For Each varItem In dctDict
        ' Import File
        DoCmd.TransferText acImportDelim, "Terence Ho Import
Specification", _
            "tblImportData", varItem, True, ""
         Debug.Print varItem
      Next
   End If
End Sub

Function GetFiles(strPath As String, _
                dctDict As Scripting.Dictionary, _
                Optional blnRecursive As Boolean) As Boolean
              
' Uses the Microsoft Scripting Runtime library
' This procedure returns all the files in a directory into
' a Dictionary object. If called recursively, it also returns
' all files in subfolders.
   
   Dim fsoSysObj      As Scripting.FileSystemObject
   Dim fdrFolder      As Scripting.Folder
   Dim fdrSubFolder   As Scripting.Folder
   Dim filFile        As Scripting.File
   
   ' Return new FileSystemObject.
   Set fsoSysObj = New Scripting.FileSystemObject
   
   On Error Resume Next
   ' Get folder.
   Set fdrFolder = fsoSysObj.GetFolder(strPath)
   If Err <> 0 Then
      ' Incorrect path.
      GetFiles = False
      GoTo GetFiles_End
   End If
   On Error GoTo 0
   
   ' Loop through Files collection, adding to dictionary.
   For Each filFile In fdrFolder.Files
      dctDict.Add filFile.Path, filFile.Path
   Next filFile

   ' If Recursive flag is true, call recursively.
   If blnRecursive Then
      For Each fdrSubFolder In fdrFolder.SubFolders
         GetFiles fdrSubFolder.Path, dctDict, True
      Next fdrSubFolder
   End If

   ' Return True if no error occurred.
   GetFiles = True
   
GetFiles_End:
   Exit Function
End Function


Here is code where you don't need to set a reference to the Microsoft
Scripting Runtime library.


Public Sub TestGetAllFiles()
    Dim varFileArray As Variant
    Dim lngI As Long
    Dim strDirName As String
    
    Const NO_FILES_IN_DIR As Long = 9
    Const INVALID_DIR As Long = 13
    
    On Error GoTo Test_Err
    
    strDirName = "C:\Documents and Settings\John\My Documents\My
Work\Terence Ho\"
    varFileArray = GetAllFilesInDir(strDirName)
    For lngI = 0 To UBound(varFileArray)
        ' Import File
        DoCmd.TransferText acImportDelim, "Terence Ho Import
Specification", _
            "tblImportData", strDirName & varFileArray(lngI), True, ""
'        Debug.Print varFileArray(lngI)
    Next lngI

    Exit Sub
    
Test_Err:

    Select Case Err.Number
        Case NO_FILES_IN_DIR
            MsgBox "The directory named '" & strDirName _
                & "' contains no files."
        Case INVALID_DIR
            MsgBox "'" & strDirName & "' is not a valid directory."
        Case 0
        Case Else
            MsgBox "Error #" & Err.Number & " - " & Err.Description
    End Select
    
End Sub

Function GetAllFilesInDir(ByVal strDirPath As String) As Variant
' Loop through the directory specified in strDirPath and save each
' file name in an array, then return that array to the calling
' procedure.
' Return False if strDirPath is not a valid directory.
    Dim strTempName As String
    Dim varFiles() As Variant
    Dim lngFileCount As Long
    
    On Error GoTo GetAllFiles_Err
    
    ' Make sure that strDirPath ends with a "\" character.
    If Right$(strDirPath, 1) <> "\" Then
        strDirPath = strDirPath & "\"
    End If
    
    ' Make sure strDirPath is a directory.
    If GetAttr(strDirPath) = vbDirectory Then
        strTempName = Dir(strDirPath, vbDirectory)
        Do Until Len(strTempName) = 0
            ' Exclude ".", "..".
            If (strTempName <> ".") And (strTempName <> "..") Then
                ' Make sure we do not have a sub-directory name.
                If (GetAttr(strDirPath & strTempName) _
                    And vbDirectory) <> vbDirectory Then
                    ' Increase the size of the array
                    ' to accommodate the found filename
                    ' and add the filename to the array.
                    ReDim Preserve varFiles(lngFileCount)
                    varFiles(lngFileCount) = strTempName
                    lngFileCount = lngFileCount + 1
                End If
            End If
            ' Use the Dir function to find the next filename.
            strTempName = Dir()
        Loop
        ' Return the array of found files.
        GetAllFilesInDir = varFiles
    End If
GetAllFiles_End:
    Exit Function
GetAllFiles_Err:
    GetAllFilesInDir = False
    Resume GetAllFiles_End
End Function

John Ruff - The Eternal Optimist :-)
Always Looking for a Contract Opportunity

xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498 



-----Original Message-----
From: bleem [mailto:hosiutung@h...] 
Sent: Friday, June 14, 2002 4:48 PM
To: Access
Subject: [access] importing text through vba


Hi there!

I've got 300 data files in the following format and they've similar name

convention, e.g. 020101.txt, 020102.txt...

CODE	STOCK SHORT NAME	(SH)	($)	TYPE	DATE	
1	CHEUNG KONG	518000	37234500	M	2002/6/11	
2	CLP HOLDINGS	94000	3080200	M	2002/6/11	
3	HK & CHINA GAS	240000	2576000	M	2002/6/11	
4	WHARF HOLDINGS	9000	171000	M	2002/6/11	
5	HSBC HOLDINGS	434800	40603800	M	2002/6/11	
8	PCCW	1294000	2471540	M	2002/6/11	
11	HANG SENG BANK	128300	11066525	M	2002/6/11	
12	HENDERSON LAND	15000	511500	M	2002/6/11	

Can any one tell me how I can import/append the files into one single 
table programatically using access vba?

Any help would be greatly appreciated!

Terrence Ho

Hi John!

The code works fine. Thanks a lot!

Regards,
Terrence Ho